Selecting unique records based on one field

  • Thread starter Thread starter Walt
  • Start date Start date
W

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?
 
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)
 
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
 
Thank you Sylvian and John. You folks that provide this service to us in the
amateur world are great!
--
Thank You,
Walt


John Spencer MVP said:
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?
Click to expand...
Click to expand...
 
Back
Top