Micrsoft Access how to select highest value?

brinky_2

Hi, is there any easy way in access to look at 3 columns of data, pick the highest value and insert it into another column?

Many thanks

isofa

Use a query, first to join the 3 columns of data into one, the the Max function to pick out the highest value. As you want to insert it into another column rather than a row, the best way would be to setup one select query, then one update query based on that for clarity.

This would be simpler in Excel.

Money_Saver

You might find this site helpful

http://www.access-programmers.co.uk/forums/index.php

astreix

Do you want to pick the max of three values in each row? If you want to do that add a blank column (for output). Use an update query and update that column as max(col1, col2, col3)

jamalfatty

You could use the Switch function within your SELECT statement ->

Switch (columnA > columnB and columnA > columnC, columnA, columnB > columnA and columnB>columnC, columnB, columnC > columnA and columnC > columnB, columnC)

Not most elegant of solutions but works without depending on other queries and ensures always accurate as is recalculated each time query is run.

As your selecting the MAX of multiple fields though, this suggests that they are all similar or even identical datatypes, might be more beneficial redesigning the tables to enable you to use the built in aggregate functions for this purpose