That works. I knew I was missing something simple. It was the GROUP BY and the HAVING together. Thank you very much.
>>> Michael Stassen <[EMAIL PROTECTED]> 2/25/2004 9:05:34 AM >>> Jacque Scott wrote: > My program, NCR (Non-Conformity Report), keeps track of problems with > items that are received from vendors. I am creating a report where the > user can retrieve a list of vendors that have had a NCR written against > them a certain number of times. For example, if the user wants to see > what vendors have had more than 2 NCRs written against them. > > I need something like this, but it doesn't work. It gives me an error > saying invalid use of group function. So I put Count(Subs.Vendor) > 2 > into a HAVING clause and it return only one vendor. I know there are > more. > > SELECT Subs.Vendor > FROM tblNCRLog INNER JOIN Subs ON tblNCRLog.SubID = Subs.SubNo > WHERE ((tblNCRLog.CurrentDate > '2002/10/1') AND Count(Subs.Vendor) > > 2); > > > Does someone have any suggestions? > > Jacque You say you use HAVING, but it's not in your example query. Perhaps that's an oversight. But you're also missing a GROUP BY. Your join gets you one row per NCR per vendor. You need to group by vendor, then check the count with HAVING. I think you need something like: SELECT Subs.Vendor, COUNT(*) AS NCRs FROM Subs, tblNCRLog WHERE Subs.SubNo = tblNCRLog.SubID AND tblNCRLog.CurrentDate > '2002/10/1' GROUP BY Subs.Vendor HAVING NCRs > 2 That would give the vendors and the counts. If you really don't want to see the counts, so long as they're greater than 2, something like this should do: SELECT Subs.Vendor FROM Subs, tblNCRLog WHERE Subs.SubNo = tblNCRLog.SubID AND tblNCRLog.CurrentDate > '2002/10/1' GROUP BY Subs.Vendor HAVING COUNT(*) > 2 Hope that helps. Michael
