Help finding table entries w/No Matching entries in another table...

[fixumdude]

I have 2 tables:
'Vehicles' which has a UniqueVehicleNumber

and

'Transactions' with a UniqueVehicleNumber which equals the Vehicles.UniqueVehicleNumber

The Transactions table stores vehicle service data with a time/date and the UniqueVehicleNumber

I need to get a list of Vehicle numbers from the Vehicles table that are NOT in the Transactions table for a given period of time.

i.e. "Show me a list of all Vehicles that are NOT in the Transactions table between 1/1/2002 and 1/2/2002"

I can get a list of vehicles not in the transaction table and a list of transactions between two dates, but I'm having trouble getting a SQL statement that will give me both at the same time.

here is the sample of each SQL statement:
this one gives me transactions between two dates:

SELECT [Service Transactions].TransUniqueVehicleNumber
FROM [Service Transactions]
WHERE ((([Service Transactions].TransTimeDate) Between #1/20/2002# And #1/31/2002#));

this one gives me vehicles not in the transaction table:

SELECT Vehicles.VehicleUnique
FROM Vehicles LEFT JOIN [Service Transactions] ON Vehicles.VehicleUnique = [Service Transactions].TransUniqueVehicleNumber
WHERE ((([Service Transactions].TransUniqueVehicleNumber) Is Null));

Any help in the right direction would be greatly appreciated.

 

[mdthornton]

You'll always have problems specifying criteria on data on the other side of a left join. The simplest way to do this in 2 stages...

1. Write a query (qTransactions) which returns all vehicle #'s in the required date range.

2. Write a second query which left joins Vehicle to qTrans


Good Luck,
Mike T