If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Comparing queries
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. |
#3
|
|||
|
|||
Comparing queries
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. |
Thread Tools | |
Display Modes | |
|
|