Inherited problem joining tables on more than one field.

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I really hope someone can help me with this.
Database = A2K.
Table 1: Customer_Data: CustomerID, SSN, DOB, First Name, Last Name,
Telephone, Address, City, State, Zip.
Table 2 - (This is the table I inherited): DOB, First Name, Last Name
I need to be able to sort through the thousands of customer records in
table two and pull out only those customer records that are also in
table 1.
I know the design of table 2 is horrible. There isn't any single
field that I can link the two tables on. On the query screen, can I
have one join going between the DOB and another going between the Last
Name? Seems like that would be a lot of lines going between the
tables. Can anyone suggest a way to find and extract those records in
Table 1 that match the records in Table 2?
Any help would be greatly appreciated.
Thanks for your time.
 
There isn't any single
field that I can link the two tables on. On the query screen, can I
have one join going between the DOB and another going between the Last
Name? Seems like that would be a lot of lines going between the
tables.
Click to expand...

You can join by up to TEN fields. It's fine - join DOB to DOB, First
to First, Last to Last (and hope that you don't happen to have any
coincidental duplicates).
 
Before trying to decide anything conclusive about what you have here,
you may be well served to test the data in these tables for uniqueness
on the columns on which you may wish to join.

Next, you may want to consider that First Name is frequently entered
in different ways. James may be equivalent to Jim. I have sometimes
chosen to match names on Last Name and First Initial. For your data,
it may work better on DOB and Last Name, and ignoring first name.

The bottom line is, without uniqueness, you cannot expect perfection
in trying to create this join. Just how close it may be depends on
the data you encounter, and only experimentation will tell what works
well and what doesn't.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top