Combine tables based on matching first name and last name

[RandySea]

I have two tables that come from external sources where they are updated. I have tried to join them in what I thought would be a simple query, but I guess I am even more of a beginner than I thought.

Table 1:


FirstName, LastName, email
Table 2:
FirstName, LastName, street, city, st, zip, etc.

Query result (or a third table that can be periodically updated) that I want:
FirstName, LastName, email, street, city, st, zip, etc.

I tried a simple query that joined firstname and lastname but got an error that only one join is allowed. Then I tried with just lastname joined. The problem is that if there are multiple people with the same last name, I get multiple permutations in my result. Ex with John Smith in Chicago and Mary Smith in NY. I get John Smith in Chicago, John Smith in NY, Mary Smith in Chicago, and Mary Smith in NY.

Is there a simple way to only join where both FirstName and LastName are a match? I guess a less simple way might be to do a calculated field that was FirstName + LastName = FullName if a calculated field can be used as a join. [I'll try this after I finish posting.]

Also, I don't want to lose anyone who is only in one table. That is, I may have some people in my Table 1 who are not in Table 2, and vice versa. I don't want to lose them in the final result.

I'd appreciate any help, including pointing me to somewhere I can find my own answer.

[RandySea]

I have made some progress. I did a calculated field that was FirstName + LastName = FullName. My query works but only for people in both tables.

I think that what I want is a "full outer join using a union query." I found this on another site. Now I guess I have to figure out how to set up a Union Query. More later.

[pbaldy]

Unfortunately Access doesn't support the FULL OUTER JOIN. The workaround you've read about would look like:

SELECT...
FROM...LEFT JOIN...
UNION ALL
SELECT...
FROM...RIGHT JOIN...
UNION ALL
SELECT...
FROM...INNER JOIN...