Remove duplicate records

[Jery J.]

I have two tables provided to me. i need to merge the two tables being that
one has the clients name and the other has the clients address. Table 1 with
the clients name is fine but table 2 with the address is duplicated... for
example:
NBR NAME
123 John
123 John
124 Jim
124 Jim
125 Jerry
125 Jerry

And so on and so forth for 376k records, please don't ask me why it is part
of a larger problem found in our case management system. This is how the data
is given to me. It just for some reason creates the same record twice. Every
time i build a query to merge the clients name and address it displays the
correct information but it displays it twice like in table 2. I have tried to
enforce referential integrity and or cascade delete related records like
someone suggested but the system gives me an error "no unique index found. I
can not create a primary key for the second table because there is
duplications.

I have also tried to create a delete query but do not know the criteria
expressions needed to delete every other record or remove duplicate records.
How can i create a query that automatically cleans this up for this screwed
up duplicated table?

Thank you in advance

 

[Phil Smith]

I have two tables provided to me. i need to merge the two tables being that
one has the clients name and the other has the clients address. Table 1 with
the clients name is fine but table 2 with the address is duplicated... for
example:
NBR NAME
123 John
123 John
124 Jim
124 Jim
125 Jerry
125 Jerry

And so on and so forth for 376k records, please don't ask me why it is part
of a larger problem found in our case management system. This is how the data
is given to me. It just for some reason creates the same record twice. Every
time i build a query to merge the clients name and address it displays the
correct information but it displays it twice like in table 2. I have tried to
enforce referential integrity and or cascade delete related records like
someone suggested but the system gives me an error "no unique index found. I
can not create a primary key for the second table because there is
duplications.

I have also tried to create a delete query but do not know the criteria
expressions needed to delete every other record or remove duplicate records.
How can i create a query that automatically cleans this up for this screwed
up duplicated table?

Thank you in advance

You can create a query that creates another table, using "unique". This
would filter out any # of duplicates. For a regular maketable query,
just go into properties and choose "Unique Values.

So you build a make table query, dump all of the fields as is, with
"unique value" set. When you are done, you will have the new table
identical to the original but without any duplicates.

This assumes these are true duplicates, not things like "123 Something
St." vs "123 Something Street". Then, you are in for a world of pain.