CODEWITHSUNDEEP
sqlms-access
rachel
rachel

Reputation:

Access queries?

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

Upvotes: 0

Views: 150

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 1

Peter Perháč
Peter Perháč

Reputation: 20792

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 :)

Upvotes: 0

Related Questions