how to avoid duplicate records

[Tinkster]

This is a tough one. I have over 1000 names of children involving about 25
fields: Address, city, emergency contacts.... Some of the info is
duplicated. Foster kids with same guardian, .... But my question is: How
can we enter new kids without duplicating a record that may already be ther
based on 3 fields, "Last name, First Name, and Birthdate". Sometimes a name
is the same but birthdate different and vice versus. Any ideas? Too many
kids to keep looking up each name before we enter it. I want it to kick it
out if it is a duplicate or jump to that record.
Thanks

 

[vbasean]

to do what I'm going to suggest, you would have to ensure there are no
duplicates in your table now. SEE BELOW FOR MORE ON DE DUPEING.

open the table in design
click on each field you want as the identifier while holding the control
button

they will all be highlighted

click on the 'Primary Key' button, looks like a key

this creates a three field unique key which will bump anytime all three are
duplicated anywhere in the database.

YOU CAN get rid of all duplicates this way but BACK UP BEFORE YOU DO.

now that the warning is out of the way.

copy the table in question

paste it as definitions only

change the fields of this NEW table as I suggested above

create an append query to take your OLD table and place the items in the NEW
table

run it, it will have an error, something like "all records could not be
appended" but you want this because you want to eliminate the duplicates.

delete the OLD table and rename your NEW table with the OLD's name.

recreate any relationships that existed before with the NEW table.

 

[vbasean]

And here's another one:

you only have 1000 + records, correct?

create a "Find Duplicates" query by using the "Find Duplicates" query wizard
(in 2003 located on the query window after clicking "new")

add the three fields in question: last, first, bdate

run it and it will show you all your duplicates.