Miles per Gallon Report

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 wrote:
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


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
------------------------------------------------------------------------------
------------
Total TGal TMileage MPG
lbl =Sum([Gal]) =Sum([Mileage])
=TMileage/TGal

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

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


"babyatx13 via AccessMonster.com" wrote:

pbielawski wrote:
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


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
------------------------------------------------------------------------------
------------
Total TGal TMileage MPG
lbl =Sum([Gal]) =Sum([Mileage])
=TMileage/TGal

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

pbielawski wrote:
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

Hi
I am using Access 2002 on XP Pro.
[quoted text clipped - 39 lines]
lbl =Sum([Gal]) =Sum([Mileage])
=TMileage/TGal


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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1