Need help in this report

[Guest]

I'm embarrased to show you the many attempts I have made as solving this problem... so I'll just ask my question and hope that someone can lead me in a more direct manner to a solution...
I've asked this in several different places, hoping to get a bunch of answers to choose from... you're all geniuses in my mind.. ignore my spelling, I have the flu.
I'm not sure where to begin... I have a report that is dealing with figuring Miles per gallon on cars within a fleet. On my report I have been able to get this calculation fine, except that the beginning odometer reading that I am using for this monthly report is the first entry of that current month. I need this odometer reading to be the last odometer reading of the previous month. I have a query set up to give me the previous months odometer reading that I use on a report on total monthly mileage, but it gives me every month that has a record. I only want the month immediately preceeding the month of the report (based on a range that is already set up in a query). Of course there are multiple cars that I'm working with. AND I want it to show up on the report in the detail summary to be able to calculate an accurate MPG ((current mileage - max of last months mileage)/total gallons purchased). If that's as clear as mud, I don't know what is! Hope you can decifer my rambling...anymore, I'm not even sure what I'm saying...

 

[Marshall Barton]

I'm embarrased to show you the many attempts I have made as solving this problem... so I'll just ask my question and hope that someone can lead me in a more direct manner to a solution...
I've asked this in several different places, hoping to get a bunch of answers to choose from... you're all geniuses in my mind.. ignore my spelling, I have the flu.
I'm not sure where to begin... I have a report that is dealing with figuring Miles per gallon on cars within a fleet. On my report I have been able to get this calculation fine, except that the beginning odometer reading that I am using for this monthly report is the first entry of that current month. I need this odometer reading to be the last odometer reading of the previous month. I have a query set up to give me the previous months odometer reading that I use on a report on total monthly mileage, but it gives me every month that has a record. I only want the month immediately preceeding the month of the report (based on a range that is already set up in a query). Of course there are multiple cars that I'm working with. AND I want it to show up on the report in the detail summary to be able to calculate an accurate MPG ((current mileage - max of last months mileage)/total gallons purchased). If that's as clear as mud, I don't know what is! Hope you can decifer my
rambling...anymore, I'm not even sure what I'm saying...

I'm pretty sure that you want to use a subquery to get the
previous months odometer. It would have been easier/clearer
if you had posted the report's record source query, but
here's the general idea:

SELECT tblcars.*,
(SELECT Max(odometer)
FROM tblcars AS X
WHERE X.carID = tblcars.carID
AND X.readingdate < [Enter Start Date]
) AS PreviousOdometer
FROM tblcars
WHERE tblcars.readingdate
BETWEEN [Enter Start Date] AND ([Enter End Date]