Showing un-matched fields in a basic query

[Adrian]

Hi there,

I have 2 sets of data created into 2 seperated tables. They are linked
through a single PRIMARY KEY. Access's query is able to link and display
fields from these 2 tables if the PRIMARY KEY is correct. BUT what about
displaying all the fields where the PRIMARY FIELDS do not match?

example: -
Table 1
container number (primary key)
destination port

Table 2
container number (primary key)
destination port

I would like to show the the container numbers and destination ports from
both table 1 & 2 which DO NOT MATCH.

PLEASE HELP!! Thank you!

Regards,
Adrian
(a in-frequent access user)

 

[Dennis]

Queries - New - Find Unmatched Query Wizard

 

[John Spencer]

A query like this one will show all the containers Table 1 that are not
in table 2

SELECT "Not In B" as Missing
, A.[Container Number]
, A.[Destination Port]
FROM Table1 as A LEFT JOIN Table2 as B
ON A.[Container Number] = B.[Container Number]
WHERE B.[Container Number] is Null

For the other way round:
SELECT "Not In A" as Missing
, B.[Container Number]
, B.[Destination Port]
FROM Table1 as A RIGHT JOIN Table2 as B
ON A.[Container Number] = B.[Container Number]
WHERE A.[Container Number] is Null

If you want those where the Destination does not match although the
Container numbers do match.

SELECT "Different Port" as Missing
, A.[Container Number]
, A.[Destination Port]
, B.[Destination Port]
FROM Table1 as A INNER JOIN Table2 as B
ON A.[Container Number] = B.[Container Number]
WHERE A.[Destination Port] <> B.[Destination Port]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Adrian]

Hi Dennis,

Thank you I will try it out and revert. But I have a "hunch" it'll work.

Cheers,
Adrian

 

[Adrian]

Dear John,

thank you for the detailed answer. I will experiment and revert.

Best Regards,
Adrian Tan

A query like this one will show all the containers Table 1 that are not
in table 2

SELECT "Not In B" as Missing
, A.[Container Number]
, A.[Destination Port]
FROM Table1 as A LEFT JOIN Table2 as B
ON A.[Container Number] = B.[Container Number]
WHERE B.[Container Number] is Null

For the other way round:
SELECT "Not In A" as Missing
, B.[Container Number]
, B.[Destination Port]
FROM Table1 as A RIGHT JOIN Table2 as B
ON A.[Container Number] = B.[Container Number]
WHERE A.[Container Number] is Null

If you want those where the Destination does not match although the
Container numbers do match.

SELECT "Different Port" as Missing
, A.[Container Number]
, A.[Destination Port]
, B.[Destination Port]
FROM Table1 as A INNER JOIN Table2 as B
ON A.[Container Number] = B.[Container Number]
WHERE A.[Destination Port] <> B.[Destination Port]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Queries - New - Find Unmatched Query Wizard