find the first 2 highest values from a record (1 Viewer)

Dear All,

I have a table with 3 fields and I would like to insert a forth and fifth field that would calculate the max and 2nd highest value for each record (I mean the max 2 values out of the three fields)

Eg.
Field1---Field2---Field3---Newfield1------- Newfield2
2---------3------- 4------------4---------------3
5---------6---------5-------------6---------------5
6---------6---------3------------6---------------6

Could you please help me how to achieve that?

Many thanks!!

copy and paste the functions in Standard Module:


to use in Simple Select Query:


select field1, field2, field3, fnMax1(field1,field2,field3) As NewField1, fnMax2(field1,field2,field3) As NewField2 from yourTable;



to update Newfield1, NewField2 in the table using Update query:


Update yourTable set NewField1=fnMax1(field1,field2,field3), NewField2=fnMax2(field1,field2,field3);

Access has no function to do this because the repeating group violates first normal form. What you have is essentially a spreadsheet and you will find that Excel is much better suited to working with spreadsheets than a real relational database tool such as Access is.

To normalize the table, you would create a separate table that contains one row for each of the repeating columns. In a properly normalized schema, adding the two additional values would not change any query, form, or calculation. Please note that you will have to modify the suggested UDF to increase the argument count each time you add additional arguments.

The query would be:
Select Top 2 SomePKfield, DataField
From YourTable
Order By DataField Desc;

Thanks sir , I was able to get the answer. I made a small modification . I used = along with > and < sign in first line in both the functions.

I'm just curious ...
If you used all three operators, < and > and = in Ariel's code, won't it always exit the function at that line?