Query

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.

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());

--
Build a little, test a little.


"Queries" wrote:

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 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

Queries wrote:
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.

On Wed, 2 Sep 2009 12:36:02 -0700, Queries
wrote:

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)

--

John W. Vinson [MVP]