Limiting results in a Query

[NHogan]

I have a query that has 4 fields, a Part Number, Year, Model and Code. I only want to show the Part Number if it does not have multiple years, models and codes.

For example, from the data below, I only want to show the Part Number '456' in the output for the query. I am trying to show unique parts in the query which means it can only have one entry and not multiple years, models, or codes.

Part # Year Model Code
123 2000 A1 SSS
123 2000 B1 XXX
456 2001 A1 SSS
789 2000 C1 XXX
789 2001 C1 ZZZ

Is there a way that I can count the duplicate Part Numbers and not show them if the count is greater than 1? Please help.

 

[aaahhhrrggghhhh]

Hi.

Try :

SELECT Count(*) AS cntone, PartNo
FROM tablename
GROUP BY PartNo
HAVING Count(*)<2;

Regards,
Mr Big Dont be small. be BIG

 

[sbohman]

You can return the single parts along with the other fields by using a subquery to select the single parts.

Select c.Part#, p.Year, p.Model, p.Code from
SELECT Count(Part#) AS CountPart#, Part#
FROM PartTable
GROUP BY Part#
HAVING Count(Part#)=1) as c, PartTable as p
where c.Part# = c.Part#;


SBohman