Eliminate Duplicate results form query - NOT COLLAPSED

  • Thread starter Thread starter buckpeace
  • Start date Start date
B

buckpeace

I am trying to get rid of results coming back that are the same: Explanaition:
I have a member that has 5 records. I want to get the 1st 2 records (they
are numbered 1-5) as long as they are NOT the same. (dups happen due to
address field containing the same address, but diff doctor) - I just need top
get the 1st 2 that are different.
 
in query design view; with nothing highlighted....up in the menu bar is
'Properties'....one of the options you will find
Unique Values

it is default to no....change to yes

this assumes the entire record is exactly the same for all fields....
 
Step One
Build a query that returns unique results using a Totals query and
grouping on all fields EXCEPT the sequence (record no) field.

Something like
SELECT First(SequenceNumber), ClientName, Address, City, State, Zip
FROM YourTable
GROUP BY ClientName, Address, City, State, Zip

Now using that query as a source, you can build a query like the following.
SELECT YourTable.*
FROM YourTable
WHERE SequenceNumber IN
(SELECT Top 2 SequenceNumber
FROM qOne
WHERE qOne.ClientName = YourTable.ClientName
And qOne.Zip = YourTable.Zip
ORDER BY SequenceNumber)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top