Last result that meets a criteria

[brumshine]

I have an access database that stores a truck number, mileage, and time stamp of log on and off times. I would like to be able to create a report that displays the total miles driven per month for all units.

My first attempt grabbed the maximum and the minimum mileage values and then subtracted to find the difference. This is not accurate enough, about once a month users "fat finger" there mileage and submit an inaccurate value.

What I would like to do is just get the mileage submitted on the first day of the month and the last day. Can I perform a sort and somehow get the last result that meets a given criteria for each unit each month?

I'm using access in the design view.

Thanks in advance for any ideas or suggestions,


BRUMSHINE

 

[Bilbo_Baggins_Esq]

well, there are plenty of ways to do this.

Here is one that comes to mind.

Write a query which pulls your desired fields, and add to it 1 field to get the year of the date and 1 field to get the month of the same date.
2 fields, 1 year and 1 month.

Then do a Totals query off that query.
Group by truck number, year, and month, and then get the min mileage in one column and max mileage in another column.

I am assuming that in order to get the total mileage for each month, you would want to get the begin mileage on the first day, and the end mileage on the last day (and hope they did not suffer from fat finger during the logging of either value). The above would look for the lowest logged mileage and max logged mileage by month and year. That should work unless fat fingers accidentally logged a lower or higher mileage on a day other than the first or last month.


or another way:
If you want to force it to look at the dates first and get the values from them, you'd need to write query to get the min and max dates for each truck, year, month first and then join (on truck number and date) that query with the original query in a third query to pull the associated mileage records from the matching dates (this assumes you have only one record per truck per day).

As a side note, you may want to build in some error checking to limit fat finger related errors in mileage records.