Hi all, I am trying to write an Access query that will return the maximum of several fields. As an example, I have four to ten salespeople who have earnings each week. I want to say "TopSalesPerson=Max(Sales1,Sales2,Sales3...)". If this was Excel, I would type in cell A11 Max(A1:A10). Is there a similar function in Access?
Max of several fields
- Thread starter mmckeage
- Start date
- Local time
- Today, 05:11
- Joined
- Aug 30, 2003
- Messages
- 36,270
This type of thing:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209857
But it sounds like you may have a design issue (which is why there's no built-in way to do it).
http://support.microsoft.com/default.aspx?scid=kb;en-us;209857
But it sounds like you may have a design issue (which is why there's no built-in way to do it).
Thanks for the quick reply. I acknowledge that the design is not optimal (I am new to this), so any thoughts you might have to improving it would be great.
My database has the following fields: Year, Peak1, Peak2, Peak3, ... Peak12 representing the maximum for each month of the year. What I would like to do now is a winter peak which would be the maximum of Peak11 and Peak12 of Year-1 and January through March of the current year, and a summer peak that would be the maximum of Peak4 through Peak10.
I believe that your solution would find the summer peak. I thought I could create new fields in the query called Peak11LastYear and Peak12LastYear to resolve my need for a winter peak.
I would appreciate any additional comments on either my design or the calculations. I did come up with the right answer in my test data using a series of nested IIf statements, but it was really tedious.
My database has the following fields: Year, Peak1, Peak2, Peak3, ... Peak12 representing the maximum for each month of the year. What I would like to do now is a winter peak which would be the maximum of Peak11 and Peak12 of Year-1 and January through March of the current year, and a summer peak that would be the maximum of Peak4 through Peak10.
I believe that your solution would find the summer peak. I thought I could create new fields in the query called Peak11LastYear and Peak12LastYear to resolve my need for a winter peak.
I would appreciate any additional comments on either my design or the calculations. I did come up with the right answer in my test data using a series of nested IIf statements, but it was really tedious.
Last edited:
As an interim solution, I created a temporary query that created the following fields: LastYear=[Year]+1
[Peak11]
[Peak12]
I then created a query that joined my original table's [Year] with the temporary query's [LastYear]. I then used the function provided by pbaldy to find the winter maximum among [Peak11], [Peak12], [Peak1], [Peak2], and [Peak3]. I similarly created the summer maximum among [Peak4] through [Peak10]. Thanks...
[Peak11]
[Peak12]
I then created a query that joined my original table's [Year] with the temporary query's [LastYear]. I then used the function provided by pbaldy to find the winter maximum among [Peak11], [Peak12], [Peak1], [Peak2], and [Peak3]. I similarly created the summer maximum among [Peak4] through [Peak10]. Thanks...
Similar threads
