Reporting Max values by location/month

[rmm13]

I have query working for a report - it's a 3 layer thing. Based on
http://www.techonthenet.com/access/queries/max_query2.php

Identify max value data by location and month

Select the unique max records

Sum the max number of people by location

However I can only get the data for each of the location per month. So the only way I can think of collecting this data for the year is to run the query per month in a recordset and write it into a table to collect the whole years data.

I'm hoping someone has a clever solution.

 

[pbaldy]

You'd also group on a expression based on your date field, like Format(DateField, "yyyymm")

 

[rmm13]

Thanks Paul

That seems to work really well with a crosstab - still testing with all different data. I had to develop this all for Monthly reports then extend it - missed the simple date grouping. I am using MonthName(DatePart("m",[DistDate])).

 

[pbaldy]

Happy to help. Note that you won't get proper sorting with month names, which is why I typically use the Format() function (though I might have another field the user sees). That would be irrelevant if you only view a month at a time.

 

[rmm13]

By sorting do you mean the order across the report Jan, Feb, Mar etc?

I put "Jan", "Feb","Mar" etc. in the Column Heading properties - and it added a Pivot to the SQL in the crosstab and it displays the months in order.