Table Comparison

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables

Table One has two fields, room number and item code
Table Two has two fields, room number and item code

I what to find all record in table One that mach the record in table Two
Then I what to find all the record in table One that don’t mach table Two
Then I what to find all the record in table Two that don’t mach table One

Any ideas on how to do this
 
In separate queries

(All Matches)
SELECT T1.*
FROM T1 INNER JOIN T2
On T1.RoomNumber = T2.RoomNumber AND
T1.ItemCode = T2.ItemCode

(In Table 1, not in 2)
SELECT T1.*
FROM T1 LEFT JOIN T2
On T1.RoomNumber = T2.RoomNumber AND
T1.ItemCode = T2.ItemCode
WHERE T2.RoomNumber Is Null

(In Table2, not in 1)
SELECT T2.*
FROM T1 Right JOIN T2
On T1.RoomNumber = T2.RoomNumber AND
T1.ItemCode = T2.ItemCode
WHERE T1.Roomnumber is Null

In the query grid.
Select both tables

For query one
Drag from RoomNumber to Roomnumber and From ItemCode to ItemCode

For query two - do the same, but double click on the join lines and set them for
all in table1 and only those ... in table 2
Add RoomNumber and Item code for table 1 and Room number for table 2 to field list
type IS NULL in Criteria under table 2 room number

For query three switch table 1 and table 2 ---
 
Thanks

John Spencer (MVP) said:
In separate queries

(All Matches)
SELECT T1.*
FROM T1 INNER JOIN T2
On T1.RoomNumber = T2.RoomNumber AND
T1.ItemCode = T2.ItemCode

(In Table 1, not in 2)
SELECT T1.*
FROM T1 LEFT JOIN T2
On T1.RoomNumber = T2.RoomNumber AND
T1.ItemCode = T2.ItemCode
WHERE T2.RoomNumber Is Null

(In Table2, not in 1)
SELECT T2.*
FROM T1 Right JOIN T2
On T1.RoomNumber = T2.RoomNumber AND
T1.ItemCode = T2.ItemCode
WHERE T1.Roomnumber is Null

In the query grid.
Select both tables

For query one
Drag from RoomNumber to Roomnumber and From ItemCode to ItemCode

For query two - do the same, but double click on the join lines and set them for
all in table1 and only those ... in table 2
Add RoomNumber and Item code for table 1 and Room number for table 2 to field list
type IS NULL in Criteria under table 2 room number

For query three switch table 1 and table 2 ---
Click to expand...
 
Back
Top