Find Duplicates in 10 tables

[macbrando]

Hello!

I have about 10 tables with address information for Christmas Cards. I
want to see if there are any duplicate addresses in two or more
tables. I found a few resources for just comparing two tables but I
want to compare all of these tables. I attempted to make a query to
contain all the records from all the databases and I was gonna run the
duplicate query wizard on that query but I can't seem to get all the
data to show up together. Can anyone help? I'm fairly new to Access so
you might have to dumb it down for me. ^_^

Thanks for any help anyone can offer.

 

[Dennis]

Why would you have TEN TABLES with Christmas card addresses???

Let's start with that.

 

[Lance]

Is your goal to find duplicates or to eliminate them? subtle difference, but
important.

 

[macbrando]

Why would you have TEN TABLES with Christmas card addresses???

Let's start with that.

Dennis: Because each attorney has their own list (or table) so they
can be easily distinguished.

Lance: I want to be able to view the duplicates to find out which
tables they are in so i can go to the attorneys who have that address
listed.

 

[Douglas J. Steele]

Dennis: Because each attorney has their own list (or table) so they
can be easily distinguished.

Lance: I want to be able to view the duplicates to find out which
tables they are in so i can go to the attorneys who have that address
listed.

There's really no need for ten tables. Add an additional field indicating
whose list it is, and write queries that return the addresses for each
individual attorney. Use those queries where you would otherwise have used a
specific table.

That having been said, you can simulate that by creating a UNION query.
Unfortunately, you cannot create UNION queries through the graphical query
builder: you'll have to work explicitly with the SQL that Access creates.

Assuming your tables are named Attorney1, Attorney2 and so on up to
Attorney10, and each has fields PersonName, PersonAddress, PersonCity,
PersonState and PersonZip, create a new query, select only one table
(Attorney1) and drag all of the fields into the grid. Then, switch to the
SQL view (it's under the View menu). You should see something like:

SELECT Attorney1.PersonName, Attorney1.PersonAddress, Attorney1.PersonCity,
Attorney1.PersonState, Attorney1.PersonZip FROM Attorney1

Simply that a litte by removing the Attorney1. in front of each of the field
names, and add a computed field to indicate which attorney's list it is:

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1

Okay, now select that entire query and copy it to the clipboard (Ctrl-C, or
from the Edit menu)

Underneath what's there, type UNION, then paste what you had in the
clipboard (Ctrl-V, or from the Edit menu). You should now have:

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney2" AS WhoseList
FROM Attorney2

Continue doing that until you've included all ten tables

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney2" AS WhoseList
FROM Attorney2
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney3" AS WhoseList
FROM Attorney3
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney4" AS WhoseList
FROM Attorney4
....
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney10" AS WhoseList
FROM Attorney10

Save that query, calling it something like "qryAllLists"

You can now write a query based on that query. Create a new query and select
qryAllLists from the list. Drag all of the fields into the grid, then
convert your query to a Totals query (there's a icon with a sigma on it on
the button bar, or you can select Totals under the View menu). That'll add a
new row to the grid labelled Total:, set to Group By under each field.
Change that Group By to Count under the WhoseList field, put >1 as a
criteria under that field and run the query. You should now see only those
entries that exist in more than one list.

Now, it won't catch where there are differences between the entries. For
example, if it's 123 Main Street for one list, but 123 Main St. on another,
that won't show up as a duplicate.

 

[Dennis]

Yup. Poor database design at it's finest. Start with a good design, and the
rest always falls into place. It never ceases to amaze me how much extra work
people make for themselves because they don't understand database design and
data relationships. Of course, I'm an ex-mainframer, and "back in the day"
neophytes were never allowed to touch the computer except on a terminal, and
using programs written BY US.

*sigh* Those days are gone forever, and we're left with, well, this kind of
thing. (insert gagging emoticon here)

Dennis

 

[macbrando]

I don't understand how putting all of your data in one HUGE table and
using queries to separate it out if needed isn't poor database design.
In my opinion, putting all of your data together is poor. It think it
should be separated out into what each is. In my case, a separate
table for each attorney so I can keep their address lists separate,
Not together. Not to mention it's more to think about when adding more
addresses. Now, I have to type the person's initials in my main table
to add them to that attorney's list where I could've just opened the
table for the attorney and just added it there and not had to worry
about the initials.

That's just me.

Yup. Poor database design at it's finest. Start with a good design, and the
rest always falls into place. It never ceases to amaze me how much extra work
people make for themselves because they don't understand database design and
data relationships. Of course, I'm an ex-mainframer, and "back in the day"
neophytes were never allowed to touch the computer except on a terminal, and
using programs written BY US.

*sigh* Those days are gone forever, and we're left with, well, this kind of
thing. (insert gagging emoticon here)

Dennis

There's really no need for ten tables. Add an additional field indicating
whose list it is, and write queries that return the addresses for each
individual attorney. Use those queries where you would otherwise have used a
specific table.

That having been said, you can simulate that by creating a UNION query.
Unfortunately, you cannot create UNION queries through the graphical query
builder: you'll have to work explicitly with the SQL that Access creates.

Assuming your tables are named Attorney1, Attorney2 and so on up to
Attorney10, and each has fields PersonName, PersonAddress, PersonCity,
PersonState and PersonZip, create a new query, select only one table
(Attorney1) and drag all of the fields into the grid. Then, switch to the
SQL view (it's under the View menu). You should see something like:

SELECT Attorney1.PersonName, Attorney1.PersonAddress, Attorney1.PersonCity,
Attorney1.PersonState, Attorney1.PersonZip FROM Attorney1

Simply that a litte by removing the Attorney1. in front of each of the field
names, and add a computed field to indicate which attorney's list it is:

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1

Okay, now select that entire query and copy it to the clipboard (Ctrl-C, or
from the Edit menu)

Underneath what's there, type UNION, then paste what you had in the
clipboard (Ctrl-V, or from the Edit menu). You should now have:

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney2" AS WhoseList
FROM Attorney2

Continue doing that until you've included all ten tables

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney2" AS WhoseList
FROM Attorney2
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney3" AS WhoseList
FROM Attorney3
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney4" AS WhoseList
FROM Attorney4
....
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney10" AS WhoseList
FROM Attorney10

Save that query, calling it something like "qryAllLists"

You can now write a query based on that query. Create a new query and select
qryAllLists from the list. Drag all of the fields into the grid, then
convert your query to a Totals query (there's a icon with a sigma on it on
the button bar, or you can select Totals under the View menu). That'll add a
new row to the grid labelled Total:, set to Group By under each field.
Change that Group By to Count under the WhoseList field, put >1 as a
criteria under that field and run the query. You should now see only those
entries that exist in more than one list.

Now, it won't catch where there are differences between the entries. For
example, if it's 123 Main Street for one list, but 123 Main St. on another,
that won't show up as a duplicate.

 

[Dennis]

You're right. You DON'T understand. Which is why I've been paid to do
database design for over 30 years, and you haven't. I/we aren't here to teach
you how to design databases. My associates and I are merely pointing out your
DESIGN FLAWS. You can either accept the information at face value and attempt
to learn from it, take courses in database design and learn the "why" for
yourself, or reject the information out-of-hand.

But NO ONE HERE will assist you in poor design methodology.

Your call....

I don't understand how putting all of your data in one HUGE table and
using queries to separate it out if needed isn't poor database design.
In my opinion, putting all of your data together is poor. It think it
should be separated out into what each is. In my case, a separate
table for each attorney so I can keep their address lists separate,
Not together. Not to mention it's more to think about when adding more
addresses. Now, I have to type the person's initials in my main table
to add them to that attorney's list where I could've just opened the
table for the attorney and just added it there and not had to worry
about the initials.

That's just me.

Yup. Poor database design at it's finest. Start with a good design, and the
rest always falls into place. It never ceases to amaze me how much extra work
people make for themselves because they don't understand database design and
data relationships. Of course, I'm an ex-mainframer, and "back in the day"
neophytes were never allowed to touch the computer except on a terminal, and
using programs written BY US.

*sigh* Those days are gone forever, and we're left with, well, this kind of
thing. (insert gagging emoticon here)

Dennis

I have about 10 tables with address information for Christmas Cards. I
want to see if there are any duplicate addresses in two or more
tables. I found a few resources for just comparing two tables but I
want to compare all of these tables. I attempted to make a query to
contain all the records from all the databases and I was gonna run the
duplicate query wizard on that query but I can't seem to get all the
data to show up together. Can anyone help? I'm fairly new to Access so
you might have to dumb it down for me. ^_^

Thanks for any help anyone can offer.

Dennis: Because each attorney has their own list (or table) so they
can be easily distinguished.

Lance: I want to be able to view the duplicates to find out which
tables they are in so i can go to the attorneys who have that address
listed.

There's really no need for ten tables. Add an additional field indicating
whose list it is, and write queries that return the addresses for each
individual attorney. Use those queries where you would otherwise have used a
specific table.

That having been said, you can simulate that by creating a UNION query.
Unfortunately, you cannot create UNION queries through the graphical query
builder: you'll have to work explicitly with the SQL that Access creates.

Assuming your tables are named Attorney1, Attorney2 and so on up to
Attorney10, and each has fields PersonName, PersonAddress, PersonCity,
PersonState and PersonZip, create a new query, select only one table
(Attorney1) and drag all of the fields into the grid. Then, switch to the
SQL view (it's under the View menu). You should see something like:

SELECT Attorney1.PersonName, Attorney1.PersonAddress, Attorney1.PersonCity,
Attorney1.PersonState, Attorney1.PersonZip FROM Attorney1

Simply that a litte by removing the Attorney1. in front of each of the field
names, and add a computed field to indicate which attorney's list it is:

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1

Okay, now select that entire query and copy it to the clipboard (Ctrl-C, or
from the Edit menu)

Underneath what's there, type UNION, then paste what you had in the
clipboard (Ctrl-V, or from the Edit menu). You should now have:

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney2" AS WhoseList
FROM Attorney2

Continue doing that until you've included all ten tables

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney2" AS WhoseList
FROM Attorney2
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney3" AS WhoseList
FROM Attorney3
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney4" AS WhoseList
FROM Attorney4
....
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney10" AS WhoseList
FROM Attorney10

Save that query, calling it something like "qryAllLists"

You can now write a query based on that query. Create a new query and select
qryAllLists from the list. Drag all of the fields into the grid, then
convert your query to a Totals query (there's a icon with a sigma on it on
the button bar, or you can select Totals under the View menu). That'll add a
new row to the grid labelled Total:, set to Group By under each field.
Change that Group By to Count under the WhoseList field, put >1 as a
criteria under that field and run the query. You should now see only those
entries that exist in more than one list.

Now, it won't catch where there are differences between the entries. For
example, if it's 123 Main Street for one list, but 123 Main St. on another,
that won't show up as a duplicate.

 

[MacBrando]

I definitely am willing to learn. I have used FileMaker for years and
finally decided to switch over to Access to try it out and it's almost
like a completely different world. Much more SQL based than FileMaker
which makes it easier for me since I've done some light SQL work in my
past. With that being said and Having this query made up to combine my
lists, is it possible to "convert" the query results into a table so
everything is in one table and I can get rid of the separate tables?

Thanks for your help and understanding.

 

[Douglas J. Steele]

Just use the query I gave you as the basis for a MakeTable query.

Go to create a new query. Select the Union query when prompted for tables or
queries to use in the query you're creating. Drag all of the fields into the
grid. Change the query to a MakeTable query (look under the Query menu). Run
the query. No need to save it once you've run it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I definitely am willing to learn. I have used FileMaker for years and
finally decided to switch over to Access to try it out and it's almost
like a completely different world. Much more SQL based than FileMaker
which makes it easier for me since I've done some light SQL work in my
past. With that being said and Having this query made up to combine my
lists, is it possible to "convert" the query results into a table so
everything is in one table and I can get rid of the separate tables?

Thanks for your help and understanding.

You're right. You DON'T understand. Which is why I've been paid to do
database design for over 30 years, and you haven't. I/we aren't here to
teach
you how to design databases. My associates and I are merely pointing out
your
DESIGN FLAWS. You can either accept the information at face value and
attempt
to learn from it, take courses in database design and learn the "why" for
yourself, or reject the information out-of-hand.

But NO ONE HERE will assist you in poor design methodology.

Your call....

I don't understand how putting all of your data in one HUGE table and
using queries to separate it out if needed isn't poor database design.
In my opinion, putting all of your data together is poor. It think it
should be separated out into what each is. In my case, a separate
table for each attorney so I can keep their address lists separate,
Not together. Not to mention it's more to think about when adding more
addresses. Now, I have to type the person's initials in my main table
to add them to that attorney's list where I could've just opened the
table for the attorney and just added it there and not had to worry
about the initials.

That's just me.

Yup. Poor database design at it's finest. Start with a good design,
and the
rest always falls into place. It never ceases to amaze me how much
extra work
people make for themselves because they don't understand database
design and
data relationships. Of course, I'm an ex-mainframer, and "back in the
day"
neophytes were never allowed to touch the computer except on a
terminal, and
using programs written BY US.

*sigh* Those days are gone forever, and we're left with, well, this
kind of
thing. (insert gagging emoticon here)

:
I have about 10 tables with address information for Christmas
Cards. I
want to see if there are any duplicate addresses in two or
more
tables. I found a few resources for just comparing two tables
but I
want to compare all of these tables. I attempted to make a
query to
contain all the records from all the databases and I was gonna
run the
duplicate query wizard on that query but I can't seem to get
all the
data to show up together. Can anyone help? I'm fairly new to
Access so
you might have to dumb it down for me. ^_^

Thanks for any help anyone can offer.

Dennis: Because each attorney has their own list (or table) so
they
can be easily distinguished.

Lance: I want to be able to view the duplicates to find out which
tables they are in so i can go to the attorneys who have that
address
listed.

There's really no need for ten tables. Add an additional field
indicating
whose list it is, and write queries that return the addresses for
each
individual attorney. Use those queries where you would otherwise
have used a
specific table.

That having been said, you can simulate that by creating a UNION
query.
Unfortunately, you cannot create UNION queries through the
graphical query
builder: you'll have to work explicitly with the SQL that Access
creates.

Assuming your tables are named Attorney1, Attorney2 and so on up to
Attorney10, and each has fields PersonName, PersonAddress,
PersonCity,
PersonState and PersonZip, create a new query, select only one
table
(Attorney1) and drag all of the fields into the grid. Then, switch
to the
SQL view (it's under the View menu). You should see something like:

SELECT Attorney1.PersonName, Attorney1.PersonAddress,
Attorney1.PersonCity,
Attorney1.PersonState, Attorney1.PersonZip FROM Attorney1

Simply that a litte by removing the Attorney1. in front of each of
the field
names, and add a computed field to indicate which attorney's list
it is:

SELECT PersonName, PersonAddress, PersonCity, PersonState,
PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1

Okay, now select that entire query and copy it to the clipboard
(Ctrl-C, or
from the Edit menu)

Underneath what's there, type UNION, then paste what you had in the
clipboard (Ctrl-V, or from the Edit menu). You should now have:

SELECT PersonName, PersonAddress, PersonCity, PersonState,
PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState,
PersonZip,
"Attorney2" AS WhoseList
FROM Attorney2

Continue doing that until you've included all ten tables

SELECT PersonName, PersonAddress, PersonCity, PersonState,
PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState,
PersonZip,
"Attorney2" AS WhoseList
FROM Attorney2
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState,
PersonZip,
"Attorney3" AS WhoseList
FROM Attorney3
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState,
PersonZip,
"Attorney4" AS WhoseList
FROM Attorney4
....
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState,
PersonZip,
"Attorney10" AS WhoseList
FROM Attorney10

Save that query, calling it something like "qryAllLists"

You can now write a query based on that query. Create a new query
and select
qryAllLists from the list. Drag all of the fields into the grid,
then
convert your query to a Totals query (there's a icon with a sigma
on it on
the button bar, or you can select Totals under the View menu).
That'll add a
new row to the grid labelled Total:, set to Group By under each
field.
Change that Group By to Count under the WhoseList field, put >1 as
a
criteria under that field and run the query. You should now see
only those
entries that exist in more than one list.

Now, it won't catch where there are differences between the
entries. For
example, if it's 123 Main Street for one list, but 123 Main St. on
another,
that won't show up as a duplicate.

 

[Dennis]

ONE TABLE:

Each of the lawyers logs on to a network, right?

- Capture the logon ID (trivial to do in Access)
- When a user goes to enter a new address record, their logon ID (NOT
password mind you) is added to the record automatically. It never appears on
the form, and no one ever has to type in something to ID the person.
- All queries and form lookups are designed to ONLY provide records that
match that user's ID, so they never see other peoples'. You (of course) can
see everyone's
- In the BEFORE UPDATE event for each new record, check to see if that
address is already on file for another user. Take whatever action you want if
it is.

Using this methodology, you get a single table that automatically IDs the
user who "owns" each address. You can do what you want with that design.

Alternatively, (since each address will have a unique ID number (Primary
Key/autonumber)), you can have a second table of ALL users who "own" a given
address. You could then run a query that checks to see if an address is
"owned" by more than one user.

These two methods will allow you to maintain the functionality you seek
while practising proper database design. In the end, everyone wins.

I definitely am willing to learn. I have used FileMaker for years and
finally decided to switch over to Access to try it out and it's almost
like a completely different world. Much more SQL based than FileMaker
which makes it easier for me since I've done some light SQL work in my
past. With that being said and Having this query made up to combine my
lists, is it possible to "convert" the query results into a table so
everything is in one table and I can get rid of the separate tables?

Thanks for your help and understanding.

You're right. You DON'T understand. Which is why I've been paid to do
database design for over 30 years, and you haven't. I/we aren't here to teach
you how to design databases. My associates and I are merely pointing out your
DESIGN FLAWS. You can either accept the information at face value and attempt
to learn from it, take courses in database design and learn the "why" for
yourself, or reject the information out-of-hand.

But NO ONE HERE will assist you in poor design methodology.

Your call....

I don't understand how putting all of your data in one HUGE table and
using queries to separate it out if needed isn't poor database design.
In my opinion, putting all of your data together is poor. It think it
should be separated out into what each is. In my case, a separate
table for each attorney so I can keep their address lists separate,
Not together. Not to mention it's more to think about when adding more
addresses. Now, I have to type the person's initials in my main table
to add them to that attorney's list where I could've just opened the
table for the attorney and just added it there and not had to worry
about the initials.

That's just me.

Yup. Poor database design at it's finest. Start with a good design, and the
rest always falls into place. It never ceases to amaze me how much extra work
people make for themselves because they don't understand database design and
data relationships. Of course, I'm an ex-mainframer, and "back in the day"
neophytes were never allowed to touch the computer except on a terminal, and
using programs written BY US.

*sigh* Those days are gone forever, and we're left with, well, this kind of
thing. (insert gagging emoticon here)

:
I have about 10 tables with address information for Christmas Cards. I
want to see if there are any duplicate addresses in two or more
tables. I found a few resources for just comparing two tables but I
want to compare all of these tables. I attempted to make a query to
contain all the records from all the databases and I was gonna run the
duplicate query wizard on that query but I can't seem to get all the
data to show up together. Can anyone help? I'm fairly new to Access so
you might have to dumb it down for me. ^_^

Thanks for any help anyone can offer.

Dennis: Because each attorney has their own list (or table) so they
can be easily distinguished.

Lance: I want to be able to view the duplicates to find out which
tables they are in so i can go to the attorneys who have that address
listed.

There's really no need for ten tables. Add an additional field indicating
whose list it is, and write queries that return the addresses for each
individual attorney. Use those queries where you would otherwise have used a
specific table.

That having been said, you can simulate that by creating a UNION query.
Unfortunately, you cannot create UNION queries through the graphical query
builder: you'll have to work explicitly with the SQL that Access creates.

Assuming your tables are named Attorney1, Attorney2 and so on up to
Attorney10, and each has fields PersonName, PersonAddress, PersonCity,
PersonState and PersonZip, create a new query, select only one table
(Attorney1) and drag all of the fields into the grid. Then, switch to the
SQL view (it's under the View menu). You should see something like:

SELECT Attorney1.PersonName, Attorney1.PersonAddress, Attorney1.PersonCity,
Attorney1.PersonState, Attorney1.PersonZip FROM Attorney1

Simply that a litte by removing the Attorney1. in front of each of the field
names, and add a computed field to indicate which attorney's list it is:

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1

Okay, now select that entire query and copy it to the clipboard (Ctrl-C, or
from the Edit menu)

Underneath what's there, type UNION, then paste what you had in the
clipboard (Ctrl-V, or from the Edit menu). You should now have:

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney2" AS WhoseList
FROM Attorney2

Continue doing that until you've included all ten tables

SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney1" AS WhoseList
FROM Attorney1
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney2" AS WhoseList
FROM Attorney2
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney3" AS WhoseList
FROM Attorney3
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney4" AS WhoseList
FROM Attorney4
....
UNION
SELECT PersonName, PersonAddress, PersonCity, PersonState, PersonZip,
"Attorney10" AS WhoseList
FROM Attorney10

Save that query, calling it something like "qryAllLists"

You can now write a query based on that query. Create a new query and select
qryAllLists from the list. Drag all of the fields into the grid, then
convert your query to a Totals query (there's a icon with a sigma on it on
the button bar, or you can select Totals under the View menu). That'll add a
new row to the grid labelled Total:, set to Group By under each field.
Change that Group By to Count under the WhoseList field, put >1 as a
criteria under that field and run the query. You should now see only those
entries that exist in more than one list.

Now, it won't catch where there are differences between the entries. For
example, if it's 123 Main Street for one list, but 123 Main St. on another,
that won't show up as a duplicate.