asked on

How do I find the max value among fields in each record of a recordset?

I have a table with the following fields:

ID, Week1, Week2, Week3, ... , Week52

For each record, I need to identify the week with the maximum value. What's the best way to do this using VBA, queries, etc.

Akenathon

select ID, greatest(Week1, Week2, Week3, ... , Week52) from the_table

ASKER CERTIFIED SOLUTION

Carlos Villegas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carlos Villegas

Only works with numbers but also can be changed a little to work with strings, dates, etc...

SOLUTION

Rob Brockett

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Brockett

Drat, I must remember to refresh the page before posting...

Ah well...
I think that my UpdateTableWithMaxWeekNames does what you want. You can change the name of the Table & the output field ("highestWeek") as required.

Akenathon,
I can't get "greatest" to work in Access, should it?
Do I need to set a reference, or is it relying on something you haven't posted?

Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...

Rob Brockett

hi ltdanp22,

Have any of our posts helped/are we on the right track?
I would appreciate some feedback and I'm guessing the others may feel the same.

Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...

ltdanp22

ASKER

Sorry guys. Used the procedures above and forgot to accept a solution. Akenathon, Greatest doesn't seem to work in Access.

Rob Brockett

Thanks for getting back to us - I'm pleased we could help :-)

Rob

Carlos Villegas

Me too! thanks!