Looking for Minimum Value in several fields

[utzja1]

I'm building a query from a table that includes a set of vertical clearance measurements for each asset. The fields are named [CLD2] through [CLD9]. Not all fields will have measurements entered in them.

I am trying to build an expression in the query that takes the minimum of the values from [CLD2] to [CLD9] and assigns it to [CLD1] for each record in the data set. I seem to be battling my EXCEL background, because I keep trying to define the MIN function with a list of values only to be told I have the wrong number of arguments in the expression.

In ACCESS 2013, what is the process for this? Is it necessary to create a sub-query and somehow determine the minimum value from that?

Thanks in advance for your consideration.

 

[pbaldy]

In a relational database, we would not have fields for each value, like you would in a spreadsheet. We would have a record for each value, and the Min() function works across records, not fields. I'd fix the design, but if you keep it this may work for you:

https://support.microsoft.com/en-us/kb/209857

You may want to read up on normalization, which would guide you towards a proper design:

http://www.r937.com/Relational.html