Query

  • Thread starter Thread starter Queries
  • Start date Start date
Q

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.
 
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());
 
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
 
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.
Click to expand...

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>)
 
Back
Top