Miles per Gallon Report

[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 have 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 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

 

[Jeff Boyce]

Consider using queries to get what you are looking for, then basing your
reports on those queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[babyatx13 via AccessMonster.com]

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 have 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 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 have had the same problem with an inventory issue; I came to a conclusion
that Access has trouble extracting a nested date in a subreport with a
parameter, because the parameter only looks at the main report, I’ve tried
nesting the parameter but this doesn’t work either, (referring your post in
the “Query†section) that is why I suggested putting all of your data into
one table. It is much easier to run the report this way and it actually works.


K Board

 

[Jeff Boyce]

I suspect that the problems you (and Access) would have dealing with "all
your data in one table" would far exceed any benefit.

If you'll post the expressions you used, the newsgroup readers may be able
to offer suggestions for alternate approaches.

I have had the same problem with an inventory issue; I came to a
conclusion
that Access has trouble extracting a nested date in a subreport with a
parameter, because the parameter only looks at the main report, I've tried
nesting the parameter but this doesn't work either, (referring your post
in
the "Query" section) that is why I suggested putting all of your data into
one table. It is much easier to run the report this way and it actually
works.


K Board

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[babyatx13 via AccessMonster.com]

I suspect that the problems you (and Access) would have dealing with "all
your data in one table" would far exceed any benefit.

If you'll post the expressions you used, the newsgroup readers may be able
to offer suggestions for alternate approaches.

I have had the same problem with an inventory issue; I came to a
conclusion

[quoted text clipped - 7 lines]

K Board

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry about that Jeff what I suggested in the Query forum is this.
make a new table and populate it from an append query for each
existing table, Make sure the 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.

Sorry should have reposted this.

K Board

 

[babyatx13 via AccessMonster.com]

I suspect that the problems you (and Access) would have dealing with "all
your data in one table" would far exceed any benefit.

If you'll post the expressions you used, the newsgroup readers may be able
to offer suggestions for alternate approaches.

I have had the same problem with an inventory issue; I came to a
conclusion

[quoted text clipped - 7 lines]

K Board

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry there was more

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


K Board

 

[Jeff Boyce]

I may be able to save you some extra work...

Rather than running a series of append queries and copying (i.e.,
duplicating) data into a new (?temporary) table whose only purpose is to
feed a report, you could...

Write a query that returns the data you need for your report, then base your
report on that query, rather than a table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I suspect that the problems you (and Access) would have dealing with "all
your data in one table" would far exceed any benefit.

If you'll post the expressions you used, the newsgroup readers may be able
to offer suggestions for alternate approaches.

I have had the same problem with an inventory issue; I came to a
conclusion

[quoted text clipped - 7 lines]

K Board

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry about that Jeff what I suggested in the Query forum is this.
make a new table and populate it from an append query for each
existing table, Make sure the 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.

Sorry should have reposted this.

K Board

 

[babyatx13 via AccessMonster.com]

I may be able to save you some extra work...

Rather than running a series of append queries and copying (i.e.,
duplicating) data into a new (?temporary) table whose only purpose is to
feed a report, you could...

Write a query that returns the data you need for your report, then base your
report on that query, rather than a table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry for being naïve Jeff, I’m sure you know what you are talking about, but
I tried that and it doesn’t work, the mileage keeps repeating in the query, I
must be doing something wrong, maybe you could post a sample of the query you
propose will work.

K Board

 

[Jeff Boyce]

Queries are based on data/tables.

Tables are joined based on shared fields and relationships.

I'm confused now about whether we are working on a Mileage report or an
Inventory report.

Please re-post the tables and their relationships. Originally, I understood
that there could be multiple transactions per vehicle, and multiple mileage
records per vehicle (but no more than once per month).

So each vehicle can have multiple transactions and multiple mileage records.
Are you trying to find all transactions and all mileage? If so, these sound
like independent (they both relate to vehicles, but don't relate to each
other). To do that, you'd need two queries (one each), and two sub-reports
(one per query), and could embed those in a main report (?vehicle-related).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[babyatx13 via AccessMonster.com]

Queries are based on data/tables.

Tables are joined based on shared fields and relationships.

I'm confused now about whether we are working on a Mileage report or an
Inventory report.

Please re-post the tables and their relationships. Originally, I understood
that there could be multiple transactions per vehicle, and multiple mileage
records per vehicle (but no more than once per month).

So each vehicle can have multiple transactions and multiple mileage records.
Are you trying to find all transactions and all mileage? If so, these sound
like independent (they both relate to vehicles, but don't relate to each
other). To do that, you'd need two queries (one each), and two sub-reports
(one per query), and could embed those in a main report (?vehicle-related).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I understand that and agree but I think we have gotten off base here.

Back to the original post.
Pbielawski’s original post was
“How can I get my main report to list only transactions between the mileage
dates in my sub report?â€
To which I respond:
It has been my experience you cannot pass this parameter (Between [Enter
Start Date] And [Enter End Date]) from a report to a sub report successfully.
If I am wrong I apologize, but if there is a way to do it please enlighten us,
I would also like to know how to do this.

Thanks
K Board

 

[Jeff Boyce]

If you use a form to capture the criteria (Start, End), then your reports
and subreport (via their underlying queries) can refer to the form. There
is no "passing" required...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Queries are based on data/tables.

Tables are joined based on shared fields and relationships.

I'm confused now about whether we are working on a Mileage report or an
Inventory report.

Please re-post the tables and their relationships. Originally, I
understood
that there could be multiple transactions per vehicle, and multiple
mileage
records per vehicle (but no more than once per month).

So each vehicle can have multiple transactions and multiple mileage
records.
Are you trying to find all transactions and all mileage? If so, these
sound
like independent (they both relate to vehicles, but don't relate to each
other). To do that, you'd need two queries (one each), and two
sub-reports
(one per query), and could embed those in a main report
(?vehicle-related).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I understand that and agree but I think we have gotten off base here.

Back to the original post.
Pbielawski's original post was
"How can I get my main report to list only transactions between the
mileage
dates in my sub report?"
To which I respond:
It has been my experience you cannot pass this parameter (Between [Enter
Start Date] And [Enter End Date]) from a report to a sub report
successfully.
If I am wrong I apologize, but if there is a way to do it please enlighten
us,
I would also like to know how to do this.

Thanks
K Board

 

[babyatx13 via AccessMonster.com]

If you use a form to capture the criteria (Start, End), then your reports
and subreport (via their underlying queries) can refer to the form. There
is no "passing" required...

Regards

Jeff Boyce
Microsoft Office/Access MVP
[quoted text clipped - 41 lines]

Thanks
K Board

quoted text clipped -
Jeff Boyce wrote
“So each vehicle can have multiple transactions and multiple mileage records.

Are you trying to find all transactions and all mileage? If so, these sound
like independent (they both relate to vehicles, but don't relate to each
other). To do that, you'd need two queries (one each), and two sub-reports
(one per query), and could embed those in a main report (?vehicle-related).

If you use a form to capture the criteria (Start, End), then your reports
and subreport (via their underlying queries) can refer to the form. There
is no "passing" required...â€


Using this combination does not work because there is no “date†field in the
main report.
Cannot get it to work with only one sub report either, where sub report is
linked by Vehicle and in any section of the report. Or linked by Vehicle;Date
and in any section of the report,


Sorry Jeff but I just don’t see how you are doing it, any other suggestions?

K Board

 

[Jeff Boyce]

I didn't suggest using a date field in the main report. I suggested using a
form to gather the date criteria, then referring to the form from within the
selection/criteria of the reports' underlying queries with something like:

Forms!frmTheDataGatheringForm!txtFromDate

Regards

Jeff Boyce
Microsoft Office/Access MVP

If you use a form to capture the criteria (Start, End), then your reports
and subreport (via their underlying queries) can refer to the form. There
is no "passing" required...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Queries are based on data/tables.

[quoted text clipped - 41 lines]

Thanks
K Board

quoted text clipped -
Jeff Boyce wrote
"So each vehicle can have multiple transactions and multiple mileage
records.

Are you trying to find all transactions and all mileage? If so, these
sound
like independent (they both relate to vehicles, but don't relate to each
other). To do that, you'd need two queries (one each), and two
sub-reports
(one per query), and could embed those in a main report
(?vehicle-related).

If you use a form to capture the criteria (Start, End), then your reports
and subreport (via their underlying queries) can refer to the form. There
is no "passing" required..."


Using this combination does not work because there is no "date" field in
the
main report.
Cannot get it to work with only one sub report either, where sub report is
linked by Vehicle and in any section of the report. Or linked by
Vehicle;Date
and in any section of the report,


Sorry Jeff but I just don't see how you are doing it, any other
suggestions?

K Board

 

[babyatx13 via AccessMonster.com]

I didn't suggest using a date field in the main report. I suggested using a
form to gather the date criteria, then referring to the form from within the
selection/criteria of the reports' underlying queries with something like:

Forms!frmTheDataGatheringForm!txtFromDate

Regards

Jeff Boyce
Microsoft Office/Access MVP
[quoted text clipped - 40 lines]

K Board

Thank you sooo much, that clears things up a lot. And lo and behold it works
too. ) I do appreciate the time you took to walk me through that. I only
hope pbielawski’s question got answered too.

Just goes to show, ya learn something new every day. )
Thanks again!!


K Board