Macro To Delete FIRST OCCURENCE of duplicate records?

  • 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 is this is a macro or not. I know of a Macro that would work for
this if I pulled the data in Excel - However, I have over 65K records to sort
through, so Excel is not an option.
Is anyone up to the challenge?
THANKS MUCH IN ADVANCE!
 
Does your table contain a field that is the unique identifier for the
row?

If it doesn't create one. Then you can create a query that returns the
key value only for the first of each group...something like:
SELECT First(KeyID) AS FirstKeyID
FROM tblName
GROUP BY [DR];

Verify that this gets you a unique list of all locations.

Then you can use it as not in criteria to the table

SELECT tblName.KeyID, tblName.DR
FROM tblName
WHERE (((tblName.KeyID) Not In (SELECT First(tblName.KeyID) AS
FirstOfKeyID
FROM tblName
GROUP BY tblName.DR;
)));


What I did was in the criteria field for the key, I typed Not in (first
query). This should give you all the records you want to delete.

Jack
 
Carol said:
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 is this is a macro or not. I know of a Macro that would work for
this if I pulled the data in Excel - However, I have over 65K records to sort
through, so Excel is not an option.
Is anyone up to the challenge?
THANKS MUCH IN ADVANCE!
Click to expand...
 
Back
Top