Duplicates in multiple tables in ACCESS

K

Kate P

How do I remove duplicates from multiple tables in a single Access database?
The help features only show how to dedupe within a single table.

Thanks.
 
M

Mike Labosh

How do I remove duplicates from multiple tables in a single Access
database?
The help features only show how to dedupe within a single table.
Click to expand...

1. If you have tables that contain similar columns, this query gets records
in table 1 that also exist in table 2

SELECT *
FROM Table1
WHERE SomeColumn IN (
SELECT DISTINCT SomeColumn
FROM Table2
)

2. If the two tables are related, you could run the Find Duplicates query
wizard on one of the tables, then open it up in design view, add the second
table and join them.
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
 
J

John Vinson

On Tue, 21 Dec 2004 10:31:01 -0800, Kate P <Kate
How do I remove duplicates from multiple tables in a single Access database?
The help features only show how to dedupe within a single table.

Thanks.
Click to expand...

If you have multiple tables all of which might contain the same data,
you may want to reconsider your table design! There should be only one
table for a particular type of information.

What are these tables? Why are they set up so that you can have dups
across tables? Is there any way to run Append queries (into a properly
indexed master table) to create one table, getting rid of the dups in
the process?

John W. Vinson[MVP]
 
K

Kate P

These tables are fundraising prospects, so unfortunately they cannot be
merged into one table because we need to route certain tables to different
places.

We received lists from different people which contain many of the same
names, so that is the root cause of this particular confusion.

Any suggestions on this?
 
J

John Vinson

These tables are fundraising prospects, so unfortunately they cannot be
merged into one table because we need to route certain tables to different
places.
Click to expand...

You may be making the very common assumption that you must have data
in a Table in order to "route it" to a different place. That
assumption is *incorrect*. It is just as easy, even easier, to export
from a Query as it is from a Table.
We received lists from different people which contain many of the same
names, so that is the root cause of this particular confusion.

Any suggestions on this?
Click to expand...

Just that deduplicating mailing lists is a tricky, time-consuming, and
difficult process at the best. Consider the following examples:

Fred Brown
xxxx 4th St.
Parma Idaho 83660

Fred Brown ... Fred Junior, that is, a different person
xxxx 4th St.
Parma Idaho 83660

Joe Schmoe
123 Any Street
Sometown, ND

Joseph Schmoe
123 Any St.
Sometown, ND

Joe G. Schmoe
123 Any Ave.
Sometown, North Dakota ... you see they're dups, Access won't

All these problems to the side, I'd suggest that - if only for the
purpose of deduplicating - that you create a master table with one
additional field, the listname. Run Append queries, or create a UNION
ALL query of your multiple lists; append the data into the single
table; remove the duplicates from it, and (if you really feel that you
must) append it back out to the individual tables. There is no *easy*
way to do deduplication across multiple tables.

John W. Vinson[MVP]
 
Top