Comparing queries

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.
--
Build a little, test a little.


"David" wrote:

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.

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;


--
Daryl S


"David" wrote:

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.