If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Miles per Gallon Report
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 |
#3
|
|||
|
|||
Miles per Gallon Report
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 |
#4
|
|||
|
|||
Miles per Gallon Report
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 |
Thread Tools | |
Display Modes | |
|
|