Hi All. I'm back at my PC.
What I have isn't very sophisticated but it's a start.
In my sample, the data table is our typical student table any one piece
of which could be duplicated but for this exercise, we're just working
with our common identifiers: names, addresses and birthdates.
The data table is linked or imported.
A make-table query is run against the data table that matches on the
common identifiers so we end up with a new table (tblMatching) that
contains all the data we want in our end result plus new columns for
each of the ID scenarios: LastFirstCityState, LastFirstStateDOB,
LastFirstStreet1, LastFirstZip using the below combinations:
the first 5 characters of the last name
the first 3 characters of the first name
the city
the state
the first 5 characters of the zip code
the date of birth
What works for us is having separate worksheets for each ID scenario so
we run additional make-table queries for each. These just as easily
could be queries instead of make-tables and they could be exported to
Excel if desired.
We created an Excel workbook of tabs linked to the appropriate table for
each scenario.
Here's the step-by-step
Open the Access duplicates DB
Run the mcrRefresh macro
Close the Access duplicates DB
Open the Excel workbook
Refresh the links
The process could probably be more automated but I don't know how to
make it so.
As soon as I can figure out how to post it, I'll provide the samples.
/glenda
/glenda
_____
From: Pearce, Norman [mailto:Norman.Pearce@xxxxxxxxxx]
Sent: Thursday, June 23, 2005 09:45 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Access - Finding duplicates
Glenda
Could you paste the SQL statement to the list, I'm sure others
would be interested, including myself
Norman Pearce
Building the foundations for your success
Ealing Hospital
UK
-----Original Message-----
From: Glenda Wells [mailto:gwells@xxxxxxxxxxx]
Sent: 23 June 2005 14:33
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access - Finding duplicates
I've got something to send on this Suzanne. As soon as I
can get to my own computer. /glenda
_____
From: DiMarco, Suzanne
[mailto:SDiMarco@xxxxxxxxxxxxxxxxxx]
Sent: Thursday, June 23, 2005 08:16 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Access - Finding duplicates
I have a table in an Access database that lists
patient names. The table (consisting of Last Name, First Name fields)
contains duplicates. I need to determine how to find the dup's in a
situation where the last name is the same; however, the first name is
slightly different. For example, there may be a Smith, Mary and
Smith, Mary A. Can someone help me with an Access query or maybe
dumping the names into Excel would be better. Thank you.
Suzanne DiMarco
302-327-7517
Desktop Solutions