Find Minimum number in access

J

Jason

I have 8 columns in access with different numbers. In the 9th column, how can
I write a formula in update query to populate the minimum number in the
previous 8 columns (in the same row) and have it ignor the 0 or blanks?

Thanks a lot!!
 
D

Douglas J. Steele

You shouldn't. There's no reason to store that information. It can (and
should) be calculated in a query. In that way, you don't have to worry if
one of the 8 fields gets updated without the minimum getting recalculated.

Write a function that accepts 8 variants (in case any of the fields can be
Null). Have it return the minimum of the 8 values, and use that function as
a computed field in a query. Use the query wherever you would otherwise have
used the table.
 
J

John Vinson

I have 8 columns in access with different numbers. In the 9th column, how can
I write a formula in update query to populate the minimum number in the
previous 8 columns (in the same row) and have it ignor the 0 or blanks?

Thanks a lot!!
Click to expand...

Your table should NOT contain such a field.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

For that matter, having eight fields across and needing to treat them
all in parallel suggests that you have a one to many (one to eight to
be precise) relationship incorrectly embedded in a single record.

You'll need some VBA code to parse the eight fields and find the least
of them. But I'd STRONGLY suggest either doing this in Excel, as it's
really a spreadsheet structure, or properly normalizing your table!


John W. Vinson[MVP]
 
D

Douglas J. Steele

onedaywhen said:
Other advice: 'Write a function that accepts 8 variants..' Am I missing
something or can this design flaw be flattened out using a dervied
table and the desired minimum be found using a single query (perhaps in
a VIEW) e.g.

CREATE TABLE TEST (
ID INT NOT NULL PRIMARY KEY,
col1 INT,
col2 INT,
col3 INT
)
;

SELECT DT1.ID,
MIN(DT1.all_cols) AS min_col_value
FROM (
SELECT ID, col1 AS all_cols
FROM TEST
UNION
SELECT ID, col2 AS all_cols
FROM TEST
UNION
SELECT ID, col3 AS all_cols
FROM TEST
) AS DT1
GROUP BY DT1.ID
Click to expand...

You're right that it can be at least partially done that way. I say
partially because you'd need to join the results of that query back to the
original query in order to get the desired format (a row with the 8 numbers
and the minimum as an additional field). It may well be that it would be
more efficient your way, though.
 
Top