Miles per Gallon Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I am using Access 2002 on XP Pro.
I have a database that tracks our fuel usage. One table, tblTransactions,
has transaction data in it; fldDate, fldQuinaty, fldVehicleID … In another
table, tblMileage, there is mileage data; fldDate, fldMilage, fldVehicleID…
Transaction are entered continually but mileage is entered once a month.
I am trying to create a report that will show all transaction for a vehicle
that has dates between the dates that are entered in the mileage table. The
report will display, Total Quantity, Total Miles and Miles per Gallon. In my
main report I am able to list the transaction and my sub report displays the
2 last mileage entries so I am able to get Total Quantity, Miles and MPG, but
the transactions are not between the mileage dates. This causes my MPG to be
way off.
How can I get my main report to list only transactions between the mileage
dates in my sub report?
Thanks
 
pbielawski said:
Hi
I am using Access 2002 on XP Pro.
I have a database that tracks our fuel usage. One table, tblTransactions,
has transaction data in it; fldDate, fldQuinaty, fldVehicleID … In another
table, tblMileage, there is mileage data; fldDate, fldMilage, fldVehicleID…
Transaction are entered continually but mileage is entered once a month.
I am trying to create a report that will show all transaction for a vehicle
that has dates between the dates that are entered in the mileage table. The
report will display, Total Quantity, Total Miles and Miles per Gallon. In my
main report I am able to list the transaction and my sub report displays the
2 last mileage entries so I am able to get Total Quantity, Miles and MPG, but
the transactions are not between the mileage dates. This causes my MPG to be
way off.
How can I get my main report to list only transactions between the mileage
dates in my sub report?
Thanks
Click to expand...


I would suggest you put all figures in one table.
Example:

KTransID Date Gallons Mileage
Autonumber Date/Time Number Number

And all of your Vehicles in another table.
Example:

KVehicleID Vehicle Etc….
Autonumber Text

As one Vehicle will have many transactions.

Report should look similar to this one

Vehicle
Date Gal
Date Gal
Date Gal
Date Mileage
 
Hi,
The transaction table is linked to another Access database, so I can not
make changes to it. Do you have any other suggestions?
Thanks
 
pbielawski said:
Hi,
The transaction table is linked to another Access database, so I can not
make changes to it. Do you have any other suggestions?
Thanks
[quoted text clipped - 39 lines]
lbl =Sum([Gal]) =Sum([Mileage])
=TMileage/TGal
Click to expand...
Click to expand...


I would suggest making a table and populate it from an append query for each
existing table, Make sure your table names and properties match.

Example:
Old Tables:
ID Date Quantity VehicleID
AutoNumber Date/Time Number Number

ID Date Mileage VehicleID
AutoNumber Date/Time Number Number



New Table:

ID Date Quantity Mileage VehicleID
Number Date/Time Number Number Number

You will need 2 append queries for both of the existing tables to append to
this one.
You can write a macro that will run them both one after the other with one
click.
From your new table write your report as mentioned before.

Note: you will have to run your append query every time there is new data to
add to your new table.

This should work.

K Board
 
Back
Top