Access queries

Question

I have two tables in MS-Access, U and R. I have joined them together and want to compare two fields. However, because there are X Tags in both to compare I am getting X lines. As you can see on ID 4 and 2 there are two tags in each table and they are both the same however because of my query I get four lines back because it compares all combinations. I do have a Unique Values set to yes. How can I make this work only getting back two lines for the below examples.

ID  U Tag1   R Tag2  Comp
1   100XX7   100XX7  SAME
2   11646L   11644   DIFFERENT
2   11646L   1X05X   DIFFERENT
2   15650    11644   DIFFERENT
2   15650    1X05X   DIFFERENT
3   5981X    598X0   DIFFERENT
4   19103    19103   SAME
4   19103    19X95   DIFFERENT
4   19X95    19103   DIFFERENT
4   19X95    19X95   SAME

Answer 1

I'm not sure what you're trying to do.

Are you trying to only see the ones that are the same between the two tables? Are you trying to list all unique values between the two tables? Are you trying to see every single line from U?

Answer 2

add a

GROUP BY id, U.Tag1, R.Tag2, (Comp? perhaps if you need that in your output)

clause to whatever query you're using now :)

Answer 3

Are you looking for something on the lines of:

SELECT u.ID, u.Tag, r.ID, r.Tag
FROM u INNER JOIN r ON (u.ID = r.ID) AND (u.Tag = r.Tag)

UNION ALL

SELECT u.ID, u.Tag, r.ID, r.Tag
FROM u LEFT JOIN r ON (u.ID = r.ID) AND (u.Tag = r.Tag)
WHERE r.Tag Is Null

The first part shows matched tags, the second part (after Union All) shows tags in u that were not found in r.