Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Posts
    8

    Unanswered: Query or code to note field with highest number

    I have query that returns sums of a number of fields for each case: ID field1 field2 field3 field4 field5

    I am trying to figure out how to get another field that tells me for each case, which field has the highest number. For example:

    ID field1 field2 field3 field5 field5 highest
    1 23 54 10 2 16 field2
    2 10 8 64 22 field5

    Also, some fields may be blank. Thanks in advance for any help on how to approach this.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    There is no native function to find the max value across fields (having to do it generally indicates a normalization issue). You can try this:

    http://support.microsoft.com/default...b;en-us;209857
    Paul

  3. #3
    Join Date
    Jul 2005
    Posts
    39
    Similar to pbaldy's pointer, you could create a tblOptionMax containing 3 fields id, refField and currentMaxValue. Then create a delete query to remove all rows from tblOptionMax. Follow this with an append query to insert into tblOptionMax all ids, firstFieldName and field1 from the original table say tblOption . Now write update query to compare tblOptionMax.currentMaxValue with nz(tblOption.field2) and update tblOptionMax.refField and tblOptionMax.currentMaxValue appropriately. Do a similar query for each of the other fields. After you run the last update query, tblOptionMax will have the refField and max value across fields.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •