Table cleanup

[Mr. Ed]

I have a table (tblClients) that I imported from an Excel
spreadsheet. It is related to another table (tblCases) on one-to-one
basis. At this point there is exactly one record for each. The
problem is that tblClients has many records that have duplicates.
What I want to do is either do a find duplicates query using first
and
last name and have the duplicates removed and have the tblCases
updated to reflect the ClientID of the unique tblClient record that
is
remaining. Some of these have only one duplicate, many have none,
and
others have three or four depending upon how many cases they had. At
5000 records this spreadsheet was becoming unmanageable. Is there an
easy way to automate this?

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "RemoveDuplicates.mdb" which illustrates how to do this. You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=285

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Mr. Ed]

And will it update the tblCases record to reflect the ClientID of the
remaining unique record so that the consolidation leaves a one-to-many
relationship and the tblCases record is not orphaned.

 

[Peter Hibbs]

The Excel to Access Converter Utility program at :-
http://www.rogersaccesslibrary.com/...?TID=183&SID=e81a164e2314187z3e8587231e28cb7d
may be able to do what you want.

Peter Hibbs.