Querying for matched first and last name combinations from two different tables

[ltcarlisle]

I'm trying to make a query that finds first and last name combinations from two different tables, and displays the record associated with each of those combinations in one of the tables. For example, the two tables both have columns for FIRST and LAST names, and one of the tables has an ID associated with each person. I'd like to be able to get the ID for each person that comes up on both tables.

In playing around with this, I've tried just joining FIRST names from the two tables and querying. Even though there are certainly matches in the FIRST name category, the query returned a blank list.



Any thoughts? Thanks!

[June7]

Did you try to join on both name fields? Does each table have first/last name combinations the other does not have? Is there a concern for spelling errors?

What is purpose of this? Does each table have different data?

[ltcarlisle]

Yes I also tried to join on both name fields and it again resulted in a blank list. Each table does have first/last name combinations that the other does not have, but each do have some in common -- those are the ones that I'm interested in. The purpose of this is, again, to find the first/last name combinations on the table with the unique ID that match first/last name combinations on another table. I want to use the unique ID associated with a matched first/last name combination and query that ID with other tables

Also there is no concern for spelling errors, neither for punctuation as all of the names are in ALL CAPS

[June7]

I don't understand. If it's the ID you want then just use the table with the ID. What is accomplished by joining to table that does not have ID?

Access is by default not case sensitive so the ALL CAPS is likely irrelevant.

[ltcarlisle]

But I only want the ID of the record with the first/last name combination that matches the first/last name combination of the other table.

[June7]

Then an INNER JOIN on both fields should do that. That should return only records with the name in both tables. No filter criteria required.

[ltcarlisle]

Great, could you tell me more about how INNER JOIN works?

[June7]

INNER JOIN requires related records in both tables.

Query in design view, click on the line linking tables, set join type.

[ltcarlisle]

Thanks for your help!

[orange]

See this for details on syntax and usage.

Did you try Google "Inner Join"? You'd be surprised how much google/bing can find and quickly.

[ltcarlisle]

Apparently my join was an INNER JOIN by default -- still when I use the FIRST names as my destination field, I get blank lists

[June7]

That doesn't make sense. Post query or provide db for analysis.

[ltcarlisle]

Nevermind -- the resulting query table is so big that it it took a long time to run. After some time, the results were displayed.

The primary reason for which the query table was so large was because there were many BLANKS that matched. Any advice on how to ignore BLANKS in querying for matches?

[June7]

Maybe don't include any records where any of the 4 fields is Null? So the criteria would be Is Null under each field but on separate rows so the OR operator is invoked.