Finding the Sum of the 2 Highest Amounts out of 8 Colms

[AMEXGUY]

I need help writing a expression that will take the 2 highest dollar amounts from eight seperate Colms and sum them together in a seperate colm for each person. Thanks for anyone who can help


Kevin
AMEXGUY

 

[Cads]

When I saw your request my immediate thought was Excel rather than Access. Comparing 8 fields in every record in a query and extracting the top 2 seems a little hairy to me!

In Excel, by using the MAX function it would be easy to find the highest; however, for the second highest you'd need to write a function to extract that.

In my case, I've bought an Excel Add-in from

http://www.add-ins.com

called the Spreadsheet Assistant (~£35 English) which has built-in functions to do what you want. In that product there is a function call HIGHNCELLS which enables you to extract the highest N cells from a selection. By using this function with the INDEX function it is also possible to extract individual nth highest values (eg the 2nd, 3rd, 4th, etc highest).

I've managed to do what you want in Excel using these functions. Is it worth thinking about exporting your table to Excel to do what you want?

 

[AMEXGUY]

Thanks CADS, I will look into that idea but I am still looking at the idea of writing an expression. I am thinking something like an iff expression.

 

[evalesthy]

See Michael Red's excellent post in this forum thread702-451672 and also MS Knowledge Base article Q209857. I think these will help you accomplish what you want to do.