Comparing queries

  • Thread starter Thread starter David
  • Start date Start date
D

David

Hi,

I am using a database storing equipment for rooms where I have two queries
set up, one listing original equipment and another listing current equipment
for room layouts.

The two queries work fine independantly, but I now want to compare
differences between these where I need to know:

1 - equipment in both original and current rooms.
2 - equipment in original room now missing from current room.
3 - equipment in current room, not required in original room.

Regards,
Dave.
 
You did not say anything about the table structure so I will go with what you
posted.
Use a union query from your two queries, without an 'ALL' to build a list of
rooms. Left join it to the two queries and then display orignal vs present.
 
Dave -

1. SELECT OrigQry.EquipID, OrigQry.RoomID FROM OrigQry INNER JOIN CurrQry
ON OrigQry.EquipID = CurrQry.EquipID AND OrigQry.RoomID = CurrQry.RoomID;

2. SELECT OrigQry.EquipID, OrigQry.RoomID FROM OrigQry LEFT JOIN CurrQry ON
OrigQry.EquipID = CurrQry.EquipID AND OrigQry.RoomID = CurrQry.RoomID WHERE
CurrQry.EquipID is NULL;

3. SELECT CurrQry.EquipID, CurrQry.RoomID FROM CurrQry LEFT JOIN OrigQry ON
OrigQry.EquipID = CurrQry.EquipID AND OrigQry.RoomID = CurrQry.RoomID WHERE
OrigQry.EquipID is NULL;
 
Back
Top