Exclude all records based on null value

[Donna]

I have a database which I want to calculate vehicle monthly usage. I also
need to be able to show daily which vehicles should be used based on that
usage and not show vehicles which have already been signed out, so I need a
query(s) that will exclude any vehicle that has an "ending mileage" that is
null; i.e. Vehicle#1 has been sign out and returned several times during the
month, but has not been returned yet today, so it should not appear on the
list of available vehicles.

Fields I think I need to use are "Vehicle#", "Returned Y/N",
"EndingMileage". I currently use Min and Max to calculate the mileage.
Thanks for any suggestions.

 

[John Spencer]

Easiest way is to build a query of all vehicles that are signed out and then
use that query in an unmatched query to get the available vehicles.

SELECT [Vehicle#]
FROM VehiclesOutTable
WHERE EndingMileage is Null

Now with that saved query

SELECT V.*
FROM VehiclesTable as V LEFT JOIN qVehiclesOut as Q
ON V.[Vehicle#] = q.[Vehicle#]
WHERE q.[Vehicle#] is Null

Alternative query and slower
SELECT V.*
FROM VehiclesTable as V
ON V.[Vehicle#] Not Exists
(SELECT *
FROM VehiclesOutTable
WHERE VehiclesOut.[EndingMileage] is Null
AND VehiclesOutTable.[Vehicle#] = VehiclesTable.[Vehicle#])

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