Finding the Maximum meter reading.

[Voyager48]

I am trying to make a query that will give me the total engine hrs a truck was used during a 6 mo. period, to get a refund of part of the fuel tax. An Access database table that holds meter reading for trucks,mileage, engine hours, PTO hours. I need to develop a query that will subtract the jan or jun meter reading from the maximum meter reading for each truck.

Database: layout
recordID
Unit
read date (this is always the first day of the month.)
todays date
Miles
engine_hours
Pto_hours

When I use max([End Month]![Engine_Hours])I get an error stating that I did not include "unit" as part of the aggurate function. I have not been able to figure out what I am doing wrong.

 

[jhall156]

In access (as in most ANSI compliant RDBMS's, FoxPro being an exception) when you group record columns they must either be aggregated (i.e. SUM, COUNT, AVG etc) or grouped by. An easy way to remember is that GROUP BY will return only 1 row for each distinct set of the grouped by fields, the rest of the columns will be aggregates of some kind. JHall