SQL? Pulling only the FIRST OCCURRENCE of a set of duplicate reco

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there. I'm stumped. I have an Access database that has duplicate
records. (working in Access 2002) The first field..say "Dr. Number" - is
always the same for each of these duplicate records. But the rest of the
fields are different: EXAMPLE:
Rec# Dr # DrLOC Name
1 0009932 001 ABC Clinic
2 0009932 002 XYZ Clinic
3 0009932 005 123 Clinic
4 9993301 007 County Hosp
5 9993301 008 West Va.

In the above scenario, I need to delete record nbrs 2,3 and 5. I only want
to see the FIRST OCCURENCE of each individual Dr. #.
Not sure how to write the sql to do this .

Anyone have any ideas?
THANKS MUCH IN ADVANCE!
 
Please ignore this post. I think I figured it out. I created a separate
table of the Dr #'s with a "minimum" Dr Loc value and then compared those 2
tables - only pulling the record with the minimum loc value.
 
Glad you solved your problem.

One bit of advice, your post says you want to "delete records ..." That
usually means that you want to PERMANENTLY remove the records from the
database. What I think you really meant was that you don't want to show the
records in the query result.
 
Back
Top