Newbie, needs help on returning all records even if zero

[laudie]

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.
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]![Miscellaneous]))
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));

 

[Phil]

Yeaks. That is a bit large to grind through without knowing the
database structure. However, a method I use may help.

I have a series of reports that return sales by Customer broken down by
Item type, one per page, over various date ranges. It is sort of a
report card of sales for each customer. Items types might be Engines,
Tires, Windshields and Transmissions.

We needed the report to return the sales of each type for a customer,
even if they purchased no Engines, we need to see a line for Engines.
Like you, with no matching records, we would get no data.

I simply created a Sales_Type_Table with four entries In it.

Engines
Tires
Windshields
Transmissions

That table went into the query before the Sales data, with the join
configured so that ALL records from my Sales_Type_Table and only those
records from Sales where Sales_Type are equal. (A left join). I made
sure on my base query, to pull "Sales Type" not from the sale file, but
from the Sales_Type_Table.

This forces the query to give me a line for every entry in
Sales_Type_Table, regardless of whether there is supporting data in the
Sales Table or not.

Make sense?

Phil

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.
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]![Miscellaneous]))
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));

 

[laudie]

Makes sense. However to clarify your structure, after you created a
Sales_Type_Table did you then put a linked field in your Sales Data
table? So that when they are entering the Sales Data they also enter
the sales type? As I said, this is all pretty new to me.

 

[Phil]

No.

In your case, I would use your vehicle table, if it contains one record
for each vehicle, (based on Vehicle_Number) and use that to drive your
query. Get it? Drive? Vehicle database?...

Link the Vehicle number from Vehicle and transaction together with a
Left join, (ALL records from Vehicle table, only matching records from
Transactions table)

This forces the query to give you a return for each Vehicle, before it
goes out looking for any transactions for that vehicle. Just remember
to display the Vehicle_No from the Vehicle table, NOT the transaction table.

Looking at your query, You seem to have everything you need, so it may
just be a matter of getting the query joined right.

Sometime I find simplification helps me to get something working. Try
starting with a query that does no summing, and just pulls one field
from Transactions. Put an easy to recognize fake vehicle in the vehicle
database. With no matching records in the Transactions, it wil be easy
to see if it is working. Then start adding in the the other fields,
sums, etc. When you are all done, don't forget to remove the fake.

You have a lot of data to work with between those tables, so I am not
surprised you are having a bit of difficulty getting it all straightened
out, beginner or not.

Phil