Show Records From Table1 That Aren't in Table2...

[Guest]

Here's my setup:

tblEquipment:
pkEquipmentID
EquipmentName
Description

tblEmployees:
pkEmployeeID
EmployeeFirstName
EmployeeLastName

tblHireDetails:
pkTransactionID
fkEquipmentID
fkEmployeedID
HireDate
ReturnDate

I would like a query that selects EquipmentID's for all EquipmentID's in
tblEquipment that aren't in tblHireDetails. I would like to extend that query
to also include EquipmentID's from tblEquipment that are in tblHireDetails
but ReturnDate is not null. An EquipmentID can be in tblHireDetails more than
once but it can only appear in one record with a null ReturnDate field.

The query should only include a single EquipmentID.

The basic intention is to query for available equipment. Equipment is only
available when it is not in tblHireDetails or it is in tblHireDetails but
every instance has a ReturnDate.

Any help is appreciated.

Thanks

Dave

 

[Graham R Seach]

David,

Use the Unmatched Query Wizard, which is available on the New Query dialog
when you create a new query.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia

 

[Guest]

Thanks. That's the first half. However, it now needs to also include
EquipmentID's that ARE in tblHireDetails but the associated ReturnDate is not
null.

Dave

 

[Graham R Seach]

David,

You might want to create a UNION query - one side containing the
non-matches, and the other containing the matches.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------

 

[Guest]

Thanks. The UNION query works except one slight snag. If an EquipmentID
appears twice or more, and one or more of those records have a return date,
that EquipmentID is still returned by the query. Here is my SQL (I'm using
PlantID and PlantHireDetails here instead of EquipmentID and HireDetails):

SELECT PlantHireDetails.PlantID
FROM PlantHireDetails
WHERE (((PlantHireDetails.ReturnDate) Is Not Null));
UNION SELECT Plant.PlantID
FROM Plant LEFT JOIN PlantHireDetails ON Plant.PlantID =
PlantHireDetails.PlantID
WHERE (((PlantHireDetails.PlantID) Is Null));

 

[Guest]

Plant/Equipment that is unavailable:

SELECT DISTINCT PlantHireDetails.PlantID
FROM PlantHireDetails
WHERE PlantHireDetails.ReturnDate Is Null;

Plant/Equipment that is available:

SELECT DISTINCT Plant.PlantID, Plant.PlantName
FROM Plant LEFT JOIN PlantOnHire ON Plant.PlantID =
[PlantOnHire].PlantID
WHERE [PlantOnHire].PlantID IS NULL;