Find part number used more than 3

  • Thread starter Thread starter juan
  • Start date Start date
J

juan

Hello,
Need to find out info for part number that has been quoted
3 or more times. I have the following:
Part number Cost1 QTPRICE
SZETEST 0.2
SZETEST 0.22
SZETEST 0.25
SZETEST 0.265
If I do a totals query:
Part field Part field cost1 QTPRICE
Count group by group by group by
it will give me a count for each one so if I say >=3, I
will not get this. So is there a way I can do this? I'm
not sure since the same part can have different quote
price.
Please advise any info.
thank you,

Juan
 
SELECT [Part Number], QtPrice
FROM YourTable
WHERE [Part Number] in
(SELECT [Part Number]
FROM YourTable
WHERE QtPrice is Not Null
GROUP BY [Part Number]
HAVING COUNT(*) > 2)

That will give you all partnumber records that have three (or more) quotes.

This is essentially the same query as the Duplicates query but the Having is
changed to >2 vice >1
 
Try using a subquery:

SELECT [Part number], [Part field], [cost1], [QTPRICE]
FROM TableName
WHERE [Part number] IN
(SELECT [Part number] FROM TableName AS T
GROUP BY [Part number]
HAVING Count([Part number]) >= 3);
 
Back
Top