Thread: many rows w/two columns i need max value return for row

Good afternoon! I think this is a relatively simple thing to do, but I do now know how! I am using MS Access 2007

Here is a sample of my data table. Top row is column headings. This is a multi-row table. Columns seperated by -

Product - Week - column 1 value - column 2 value
LEGS - WK1 - 20 - 40
DRUMS - WK4 - 60 - 0

I need to have the query return the following:
LEGS - WK1 - 40
DRUMS - WK4 - 60

I need SQL code or another way of bringing back the max value of column 1 value and column 2 value in my query results.

In a new column in a query type
Max Value: IIF([Column 1 Value] > [Column 2 Value], [Column 1 Value], [Column 2 Value])

Make sure that the check box to display Columns 1 and Columns 2 are unchecked in your query.

Your SQL Statement would look something like this:

Select Product, Week, IIF([Column 1 Value] > [Column 2 Value], [Column 1 Value], [Column 2 Value]) As Max Value
FROM YourTableName;

Alan