Query

[Queries]

I have 3 tables (TRUCK, DRIVER and ASSIGNED). ASSIGNED is used to record
which DRIVER has which TRUCK by the date. I have more trucks than drivers.
What is the best approach to list which trucks currently have no drivers.

 

[KARL DEWEY]

Try this putting your table and field names --
SELECT TRUCK.TruckID
FROM TRUCK LEFT JOIN ASSIGNED ON TRUCK.TruckID = ASSIGNED.TruckID
WHERE (ASSIGNED.TruckID Is Null) AND (ASSIGNED.WorkDate = Date());

 

[John Spencer]

SELECT TruckNumber
FROM Truck
WHERE TruckNumber NOT IN (
SELECT TruckNumber
FROM Truck
WHERE DateField = Date())

In query design view
== Add the Truck table
== Add the fields you want to see
== Under TruckNumber (or what ever the identifying field is) enter something
like the following into the criteria.
NOT IN (SELECT TruckNumber FROM Truck WHERE [Datefield] <> Date())

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I have 3 tables (TRUCK, DRIVER and ASSIGNED). ASSIGNED is used to record
which DRIVER has which TRUCK by the date. I have more trucks than drivers.
What is the best approach to list which trucks currently have no drivers.

SELECT <whatever fields you want to see>
FROM TRUCK
WHERE NOT EXISTS
(SELECT TruckID FROM Assigned WHERE <some appropriate date criteria on the
date field or fields in ASSIGNED>)