Unexpected results on Inner Join

[Access_Novice]

In table 1 I have "Name 1" and "08/17/2014" on row 3. This pairing doesn't occur anywhere else in the table, just once on row 3.



I have the same pairing in Table 2, but this pairing appears twice (rows 4 and 5).

I have a query on both tables with a double join between both name fields and date fields. Because the default join is an inner join, I should only see matches both ways. Because of this, I expected to see "Name 1 and 08/17/2014" on one row in the query results, because the 2nd occurrence in table 2 isn't paired to anything in table one. Only the first occurrence in each table is a match.

But instead, the query results have "Name 1 and 08/17/2014" occurring twice (rows 1 and 2). Why am I getting two rows with these values in the query result, instead of only one?

[burrina]

Hi, FYI Name is a reserved word in Access and spaces are bad. Can you post a screenshot of your qry? Along with a explanation of what should happen?

[June7]

You misunderstand INNER JOIN. An INNER JOIN will show only the records from both tables where there are matches. Both records in table 2 should pair with the record in table 1 because they match. How would the query know to match only the first record? The data matches so it joins.

On the other hand, LEFT or RIGHT JOIN will show all records from one table along with only records from another table that have a match. So going one way, if table 1 did not have the indicated pair then neither record would be retrieved. Going the other way, both records will be displayed and the fields from table 1 will be blank on those 2 rows.