Finding Duplicates within multiple tables

[Guest]

Access has appeared so easy and has had so many wizards that i assumed i
could easily locate duplicates within various tables. Is there an EASY way to
do this type of search? It seems there should be a way to search my tables at
once without having to do a query (union). Is that the most simple way?????

thanks again

 

[Ken Snell [MVP]]

You don't give us much information, so we can't give much advice. Most
likely you'll need to write a query that joins two tables and finds records
that are duplicates (whatever that means to those tables) of each other. Run
a query for each two-table combination.

 

[Guest]

Hello,
I tried to keep it simple thats why I didn't include much information in
first post.
My 16 tables(non-relational) each contain mailing lists with feilds such as
last name, address, city.
I simply want to do a one time search and find what records (ie. which
person) appear on more then one table, basically find duplicates of
addresses.
Must I do a union query for something so simple?
thanks in advance.

 

[Craig Alexander Morrison]

Already answered "Can I find duplicate..."

Maybe the answer was not what was hoped for.

 

[Craig Alexander Morrison]

You have already asked this question, did you not like the answers?

"Can I find duplicate records...."

You should not have data in 10 different tables that contain the same type
of thing. Dump the 10 tables, even if just temporaily for the purpose of
checking for duplicates, I think you might be better off with Excel as you
may need to visually check the records anyway and it is a lot simpler..

One table is the simplest way, the Access way.

 

[Craig Alexander Morrison]

It was 10 tables yesterday.

You need to clearly and completely describe a problem to get the best
answer, without writing a book about it. (g)

 

[Guest]

Yes, I understood the answer yesterday.
Im sorry my repitition bothered you.
I thought I'd ask again in case I missed anything.

We are using Access, not Excel, because some tables will have over 2,000
records and we like how powerful and useful Access is. Also, the tables may
become relational in the future. I wish I could combine the tables but this
is the way my supervivor wants it- so im stuck right now. I guess I can
combine the tables for my own use and find duplicates and use that large
table for the mailmerge.

 

[Guest]

yes it was 10 tables yesterday. today I had to create more.
Is there a problem? Im just here for help like everyone else.

 

[Ken Snell [MVP]]

A union query to find duplicates? depends upon what you are trying to do.

I can envision using a union query that returns all the records from all the
tables, with an identifying field that tells me the table name, and then
running a totals query to find the records that appear more than once
(independent of the identifying field that tells me the table name), and
then running a query that uses that totals query to select all the records
from the union query that match the records in the totals query.

Is that what you're asking?

Sounds as if you got an answer about this previously...did you try what was
suggested? What have you tried to do in this case?

--

Ken Snell
<MS ACCESS MVP>

 

[Brendan Reynolds]

On the database design ...

It is very rare for a database design to 'become relational in the future' -
more often, if you start with a flawed design, you're stuck with it forever.
It's kinda like laying the foundation for a house - if you don't get it
right before you build the walls and roof on top of it, you're not going to
get it right afterward.

The ten-table design is, in the long term, unworkable. If your supervisor
can't see that, well, we can sympathise, but there's nothing we can do about
that. That's a people problem, and we can only help with technical problems
here.

On easy solutions ...

'Easy' is, of course, a relative term. I think writing a UNION query is
pretty easy. While you can't use the graphical query designer to create a
UNION query, what you can do is to use the graphical query designer to
create a query that selects from one table, then switch to SQL view, copy
and paste the SQL created by the designer, then you just have to modify it
slightly for each of your UNION clauses. For example ...

SELECT Table1.TestID, Table1.TestOne, Table1.TestTwo
FROM Table1
UNION SELECT Table2.TestID, Table2.TestOne, Table2.TestTwo
FROM Table2;

The first two lines above ("SELECT ... FROM Table1") were created by the
graphical query designer. I then switched to SQL view, copied the first two
lines, and pasted them back between the end of the original two lines and
the closing semi-colon. I typed in the word 'UNION', and I changed each
reference to 'Table1' in the second two lines to 'Table2'.