We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
216 Views
Last Modified: 2012-05-11
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.
Comment
Watch Question

select ID, greatest(Week1, Week2, Week3, ... , Week52) from the_table
Full Stack .NET Developer
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Carlos VillegasFull Stack .NET Developer

Commented:
Only works with numbers but also can be changed a little to work with strings, dates, etc...
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Commented:
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...
CERTIFIED EXPERT

Commented:
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...

Author

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

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

Rob
Carlos VillegasFull Stack .NET Developer

Commented:
Me too! thanks!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.