I’m looking for some advise what I should do. I need to create a report on vehicle mileages. I have a daily table which data is collected in Veh, Date. Kms/Mile reading. I have a query which gets the data out by selected date range but I need to be able to use the first reading and last reading only for each vehicle. My problem is how to get just the start and end readings as each vehicle may have several entries in the table. Any help advise appricated.
Using only start and end figures
- Thread starter SteveE
- Start date
dcx693
Registered User.
- Local time
- Today, 00:32
- Joined
- Apr 30, 2003
- Messages
- 3,265
Will the entries for a vehicle always be contiguous for a particular date? Meaning could they skip mileage for some reason? If not, then you just need to find the max and min values for that date. The easiest way would be a Dmin or Dmax function.
If you do need to account for the possibility of gaps in the mileage, my first thought would be to design a custom function, retrieve all the values from the table, get the max and min numbers, then subtract out the gaps.
If you do need to account for the possibility of gaps in the mileage, my first thought would be to design a custom function, retrieve all the values from the table, get the max and min numbers, then subtract out the gaps.
dcx693
Registered User.
- Local time
- Today, 00:32
- Joined
- Apr 30, 2003
- Messages
- 3,265
The Access online help for these functions isn't so bad, so check it out. Basically, any of the "D" functions like Dmax, dmin, and dlookup are used to look up data in a table or query that is not available (usually because it's not joined to your other tables) in your current query or form. In this case, though, the data could be made available, but you can have multiple results for start and end mileage. The DMax will work to find the max out of all the results it finds. Likewise for Dmin.
You tell the function what field you want to search (the start or end field in this case), what table/query you want to search in, and what criteria you have for the search (the date and the Veh).
You may soon discover that correct syntax is the bane of the "D" function user. Post back if you need help.
You tell the function what field you want to search (the start or end field in this case), what table/query you want to search in, and what criteria you have for the search (the date and the Veh).
You may soon discover that correct syntax is the bane of the "D" function user. Post back if you need help.
