Max?

[texasalynn]

Registered: Jun 2002

online
06-17-2002 01:57 PM

--------------------------------------------------------------------------------


I am trying to get a MAX on several fields in a query so thought I could use:

result: Max(Array([CM=PM],[CM=PM w/inc],[CM>0 & PM=0],[CM/STD],[CM<PM & PM<>0]))

But it doesn't like that - I get a message that says:

You tried to execute a query that doesn't include the specified expression "combine" as part of the aggregate funtion.

Any suggestions or help is greatly appreciated!
texasalynn

 

[rich.barry]

Now quite clear what you want.

If it is a row with the max of each field in it, then turn on the totals (right click on a field) and set each total to Max.

If it is the Max of all fields combined, make a Union Query by going into SQL view and typing
Select Field1 From Table1 Union Select Field2 From Table1 etc etc;
which will put everything in a single long column, then use another query with the max total on to grap the biggest value from this list.

then again, maybe I'm still on the wrong track..

 

[Pat Hartman]

Max() (and the other aggregate functions) only takes a single column name or an expresession containing some calculation involving a column name. Therefore, the following will work because they both result in a single value for the function to evaluate:

Max(SomeField)
Max(SomeField + SomeOtherField)

The aggregate functions in Access work ONLY with a single column of a recordset and they find the answer among the rows of that recordset.

There are NO aggregate functions in any relational database that use multiple columns of the same row as their domain.

If you want to find the maximum value among some number of columns of the same row, you'll need to write your own function and pass it all the columns as parameters.

I would hazard an example based on what you put in the Array() function but I couldn't figuire out what you are trying to accomplish.

Try putting your problem into words.

 

[texasalynn]

OK - to put into words:

I have a query with about 7 fields that I want to find the largest value. (There are more than 7 fields in the query but for simplicity I will use that for my example)

For example (For fields I have ABC... then next row shows data)
A B C D E F
0 0 1.25 0 0 2.56

So the max for this example would return 2.56


I can't use the total method because I need each row evaluated not a total of field values.

I hope that makes it a little more confusing
Thanks . . .
texasalynn

 

[Pat Hartman]

The union query that Rich suggested will work for you if you cannot write your own VBA function. You need a separate select query in the union query for each column. For example:

Select KeyFld, OtherField1 as OtherField
From YourTable
Union Select KeyFld, OtherField2 as OtherField
From YourTable
Union Select KeyFld, OtherField3 as OtherField
From YourTable;

Then you can use the Max() function:

Select KeyFld, Max(OtherField)
From YourUnionQuery;