Max Function

[Guest]

I have an access query with months of the year in it with collections data
under each month, and I want to find the max of the collections as a
calculated field?? How do i do this:-

Here is an example of the query with the calculated max field at the end,
how do i get the max of the months??

A/c No. Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Max()
xxxxx 2 5 5 5 8 4 6 7 5 9 3
9

Thanks

Paul

 

[Allen Browne]

In query design view, one of your field will have Value in the Crosstab row.
What's in the Total row under this field?

If the Value occurs once only, you can add the same field to your query
again. This time make it a Row Heading in the Crosstab row, and Max in the
Total row. The max will appear on the left, but will have the desired value.

If the value is actually more involved (e.g. summing multiple values), then
presumably you want the Max to be the largest sum. You may need to craft a
subquery or a DSum() expression, and set it up as a Row Heading.

 

[Guest]

Allen,

The query is just a select query querying a table, not a crosstab.

I just want to add a calcualted column at the end which gives me the max of
the monthly columns? Is this possible?

Thanks

Paul

 

[John Spencer]

Is this a crosstab query or are you getting information from a table that is
structured with a field for each month?

If you are using a crosstab query, then you can calculate the Max() value in
the query's Select clause. In the grid, add the "Month" field a second
time, select Max under it in the Total "cell" and Row Heading for the
Crosstab "cell"

If your table structure is more like
AcNo
Jan
Feb
....
Nov
Dec

then you will need to use a VBA function to calculate the Max for the row or
normalize the data with a union query and then use that as the source for a
crosstab query. You could use a really, really complex switch function to
do this in a query, but I wouldn't.

Post back if you need a VBA function to get the maximum value in a record
with repeating fields.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Allen Browne]

Oh, dear. You built the table like a spreadsheet instead of a database.
The best solution will be to build a normalized table, as John Spencer
suggests.

If that's not possible, you could copy the code from this link:
http://allenbrowne.com/func-09.html
and use MaxOfList() in your query. You will end up typing something like
this into the Field row in query design:
MaxOfList([Jan], [Feb], [Mar], ...