Return Records With Zero Amounts

[laudieda]

I am new to Access and I am in need of help.
I have an automobile ledger that I am trying to create in access which
keeps track of Gallons, Gas & Oil, Tires, Repairs....etc. My problem
is that the record for the Automobile is dropping if there is no
transactions on that particular Automobile for the month.
I have created the following query which is giving me the results I
want except for the fact that it is dropping inactive records.
I researched several sites, tried everything I have found and I would think this would work according to the post I have found.
Can anybody help?


SELECT DISTINCTROW Vehicle.VehicleNumber, Vehicle.Status_ID,
Vehicle.AssignedTo, Vehicle.Model, Vehicle.Mod_No, Vehicle.Year,
Vehicle.Drive, Vehicle.Fuel, Vehicle.EngineNumber, Vehicle.[RadioS/N],
Vehicle.DateAcquired, Vehicle.DateSold, Vehicle.PurchasePrice,
Vehicle.DirectExpense, Vehicle.DepreciableTotal, Vehicle.LicenseWeight,
Vehicle.OldVehicleNumber, Vehicle.EngineType_ID, Vehicle.RadioModel_ID,
Vehicle.Make_ID, Vehicle.District_ID, District.District,
Sum(IIf(IsNull([Transactions]![Gallons]),0,[Transactions]![Gallons]))
AS [Sum Of Gallons],
Sum(IIf(IsNull([Transactions]![Gas_Oil]),0,[Transactions]![Gas_Oil]))
AS [Sum Of Gas_Oil],
Sum(IIf(IsNull([Transactions]![Tires]),0,[Transactions]![Tires])) AS
[Sum Of Tires],
Sum(IIf(IsNull([Transactions]![Repairs]),0,[Transactions]![Repairs]))
AS [Sum Of Repairs],
Sum(IIf(IsNull([Transactions]![Miscellaneous]),0,[Transactions]![Miscellane­ous]))
AS [Sum Of Miscellaneous], IIf(IsNull([Sum Of Gas_Oil]+[Sum Of
Tires]+[Sum Of Repairs]+[Sum Of Miscellaneous]),0,[Sum Of Gas_Oil]+[Sum
Of Tires]+[Sum Of Repairs]+[Sum Of Miscellaneous]) AS [Running Total]
FROM (District RIGHT JOIN Vehicle ON District.ID = Vehicle.District_ID)
LEFT JOIN Transactions ON Vehicle.VehicleNumber =
Transactions.VehicleNumber
WHERE (((Transactions.TransactionDate) Between #12/31/2004# And
[Running Total: Ending Qtr Date]))
GROUP BY Vehicle.VehicleNumber, Vehicle.Status_ID, Vehicle.AssignedTo,
Vehicle.Model, Vehicle.Mod_No, Vehicle.Year, Vehicle.Drive,
Vehicle.Fuel, Vehicle.EngineNumber, Vehicle.[RadioS/N],
Vehicle.DateAcquired, Vehicle.DateSold, Vehicle.PurchasePrice,
Vehicle.DirectExpense, Vehicle.DepreciableTotal, Vehicle.LicenseWeight,
Vehicle.OldVehicleNumber, Vehicle.EngineType_ID, Vehicle.RadioModel_ID,
Vehicle.Make_ID, Vehicle.District_ID, District.District
HAVING (((Vehicle.Status_ID)=1));

 

[PHV]

Using Transactions in your WHERE clause defeats the LEFT JOIN !
You may try this thus:
...
WHERE (Transactions.TransactionDate Between #12/31/2004# And [Running Total: Ending Qtr Date])
OR Transactions.VehicleNumber Is Null
GROUP BY ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[laudieda]

Thanks for the quick response, but it still dropped a vehicle that is active but no transactions. Your statement about using transactions in my WHERE clause defeating my LEFT JOIN, makes sense to me so I tried this

WHERE (((Vehicle.Status_ID)=1)) OR (((Transactions.TransactionDate) Between #12/31/2004# And [Running Total: Ending Qtr Date])) OR (((Transactions.TransactionID) Is Null))

However when I did this it returned all my active vehicles but also returned every transaction on that particular vehicle regardless of the Qtr. Ending Date. I know if I changed it to

WHERE (((Vehicle.Status_ID)=1)) AND(((Transactions.TransactionDate) Between #12/31/2004# And [Running Total: Ending Qtr Date])) OR (((Transactions.TransactionID) Is Null))

It would limit the transactions to my Qtr ending date, however this is a problem when we have vehicles on our records that is active but no transactions for an entire year.

Any ideas?