Comparison of two access tables that are identical in structure but not data
I have two tables in an Access database, Table1 and Table2 with exactly the same structure, but Table1 has more data. I want to figure out what data I am missing in table2. The primary key for each table consists of text fields:
- CenterName
- BuildingName
- FloorNo
- RoomNo
Each center can have many buildings, and two different centers can have a building of the same name. Room numbers and floor numbers may be the same for different buildings and different centers.
I tried
SELECT t1.CenterName, t1.BuildingName, t1.FloorNo, t1.RoomNo, t2.CenterName
FROM Table1 as t1 LEFT JOIN Table2 as t2 ON t1.CenterName=t2.CenterName
WHERE t2.CenterName Is Null;
But the above does not return any data, meaning all Centers are in both tables. But that doesn't tell me anything about the rest of the fields that might be missing from table2. Can anyone please help rewrite my query so that it works as intended?
I am using a SQL Server database, so creating queries in Access is a bit tedious for me. Before migrating all data to SQL Server for analysis, I would like to see if I can help here.
source to share
Concatenate all four fields that make up the primary key.
SELECT
t1.CenterName,
t1.BuildingName,
t1.FloorNo,
t1.RoomNo,
t2.CenterName
FROM
Table1 AS t1
LEFT JOIN Table2 AS t2
ON
t1.CenterName = t2.CenterName
AND t1.BuildingName = t2.BuildingName
AND t1.FloorNo = t2.FloorNo
AND t1.RoomNo = t2.RoomNo
WHERE t2.CenterName Is Null;
source to share