Selecting unique records based on one field

[Walt]

I would like to create a query containing 4 fields (CustID, Date, PartNo,
Qty) and have it return only those records where the CustID is unique (Only
placed one order). Can anyone suggest how this can be done?

 

[Sylvain Lafontaine]

Yes: first, you have to select the CustID who have place a single order by
making a Group By on the Order table and testing for Having CustId = 1.
After that, you can use the operator IN to query for the other three fields:

Select CustID, [Date], PartNo, Qty
From Orders
Where CustID IN (Select CustID From Orders Group By CustID Having Count(*) =
1)

This is for SQL-Server but it should work well with Access JET. There are
also other ways to express this; for example, you could use the EXISTS
statement or a subquery:

Select CustID, [Date], PartNo, Qty
From Orders
Where (Select Count(*) From Orders as O2 where Orders.CustID = O2.CustID) =
1

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[John Spencer MVP]

SELECT CustID, [Date], PartNo, Qty
FROM SomeTable
WHERE CustID in
(SELECT CustID
FROM SomeTable
GROUP BY CustID
HAVING COUNT(*)=1)


This is basically the same as a find duplicates query (the Wizard can build
that) with the Count(*)>1 changed to Count(*) = 1

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Walt]

Thank you Sylvian and John. You folks that provide this service to us in the
amateur world are great!
--
Thank You,
Walt

SELECT CustID, [Date], PartNo, Qty
FROM SomeTable
WHERE CustID in
(SELECT CustID
FROM SomeTable
GROUP BY CustID
HAVING COUNT(*)=1)


This is basically the same as a find duplicates query (the Wizard can build
that) with the Count(*)>1 changed to Count(*) = 1

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I would like to create a query containing 4 fields (CustID, Date, PartNo,
Qty) and have it return only those records where the CustID is unique (Only
placed one order). Can anyone suggest how this can be done?