Please help with a query!

[A]

Hi,

I'm trying to match up data between two tables -

Table 1: Last name, First name, Address, City, State, Zip, Date of Birth
(approx 45,000 individuals)
Table 2: Last name, First name, (Updated) Address, City, State, Zip (approx
41,000 individuals)

Most of the individuals in both tables overlap, but Table 2 doesn't have as
many people and it also includes updated addresses for those in Table 1.
Basically I want to find all the people in Table 2 who were originally in
Table 1, so I can match them up with their date of birth.

Any ideas for running a query like this would be great. I've made several
attempts but I keep having problems for people with the same first and last
name. The query will list each name multiple times along with every address
associated with that name, so there are a lot of duplicate entries. It would
help if I had a unique identifier to match up, like ID numbers, but
unfortunately we don't have those.

Again, any ideas would be VERY MUCH appreciated.

 

[Steve]

The best you can do is create a query that includes both tables.
1. Join first Name in Table 1 to First Name in Table 2.
2. Join Last Name in Table 1 to Last Name in Table 2.
3. Join City in Table 1 to City in Table 2.
4. Join State in Table 1 to State in Table 2.

The query will return all individuals with the same first and last name who
live in the same city and state. There is no way to distinguish a Joe Smith
who lives in Topeka, Kansas in Table 1 from a different Joe Smith who lives
in Topeka, Kansas in Table 2. That's just the best you can do.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John W. Vinson]

Hi,

I'm trying to match up data between two tables -

Table 1: Last name, First name, Address, City, State, Zip, Date of Birth
(approx 45,000 individuals)
Table 2: Last name, First name, (Updated) Address, City, State, Zip (approx
41,000 individuals)

Most of the individuals in both tables overlap, but Table 2 doesn't have as
many people and it also includes updated addresses for those in Table 1.
Basically I want to find all the people in Table 2 who were originally in
Table 1, so I can match them up with their date of birth.

Any ideas for running a query like this would be great. I've made several
attempts but I keep having problems for people with the same first and last
name. The query will list each name multiple times along with every address
associated with that name, so there are a lot of duplicate entries. It would
help if I had a unique identifier to match up, like ID numbers, but
unfortunately we don't have those.

Again, any ideas would be VERY MUCH appreciated.

This will require a LOT of luck. Is David Jones in Table1 the same person as
Dave Jones in Table2, at the same address? or is Dave his son Dave Jr.? Or is
he the same person as David Jones at 3218 Beale St., or the David Jones at 115
Maple, or the David Jones at 8845 Bancroft Way? You say the addresses are
different - so how can you possibly tell, without mailing each of them a
postcard - "Are you the David Jones who used to live at 4408 Ashcroft"?

You *DO NOT HAVE* enough information to do what you ask, at least not
reliably. You can perhaps run a query to catch those records where the name is
unique in both tables, and (if you're willing to go out on a rather fragile
limb) those records where the name and address match; but if all you have is
multiple people with the same name in one table, and multiple people with that
same name in the other table, there is no imaginable query that would tell you
which is which.

John W. Vinson [MVP]

 

[LauriS]

But since Table 2 has updated addresses you will likely miss quite a few
people.

Wonder what percentage of people moving stay within the same city?

Lauri S.

 

[Steve]

The OP's example showed (Updated) Address but not (Updated) City nor
(Updated) State nor (Updated) Zip and his post only stated Addresses so I
assumed individuals moved within the same city. Irregardless the same
solution is suggested. Like I said, that's the best you can do.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)