Access Query matching first and last names

[Guest]

I have a table with 41,000 individuals (their first and last names) and a
separate table with 45,000 individuals (with first and last names and dates
of birth). Most of the individuals in the tables overlap.

I'm somewhat new to Access and am trying to match up the tables so I can
find the dates of birth for the individuals in the first table. But the only
common identifiers I have are first and last names, and Access won't seem to
let me run a join query using both of those identifiers. Can anyone please
help me figure out how to do this? THANK YOU!!!!

 

[Guest]

Sure it does. In query design view drop both tables onto the QBE grid. Then
drag and drop the First Name field from one table to the other's First Name
field. Next D&D the Last Name fields between the two tables.

Put in the fields that you want returned down in the columns. When you run
the query it will show only those records that match exactly. For example
Jerry Whittle doesn't match up to Jerome Whittle due to Jerry <> Jerome.

 

[Guest]

The real challenge is that with 45,000 names, you are likely to have a lot of
duplicate first/last name combinations.

Good luck.

 

[Guest]

Thanks for the response but for some reason that doesn't seem to work. When
I run the query, it produces no results at all (just the field headings). If
I try changing the join properties, the data for only one of the tables shows
up. Any ideas?

 

[John Spencer]

As a guess the fields are not identical even though they may appear to be.

What is the source of the two tables? Are both tables from Access mdb?

If the source of the tables is external to Access you may have trailing
spaces in one table and not in the other. OR if you are linking to the
tables you could be running into case sensitive data - depends on the ISAM
in use.

Do you get records returned if you join on only last name?
Do you get records returned if you join on only first name?

If so, disregard the above.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks John, you were right - the tables were created in Excel and there were
extra leading spaces for the first names in one table but not the other.