Calculated field - Min of several columns

[JDB]

I am building a line capacity table for a can routing in a factory.

Several machines, each as a line capacity in a column - one column one
machine.

How do I find the minimum of the line capacities of one set of machine
across the seveal columns?

I tried DMin but that did not seem to work.

Can I write a VBA function with the calculated field of the query in
the query?

Thanks

JDB

 

[Allen Browne]

The best solution here would be to dedesign the table.
A normalized table would not have seprate columns for each machine.
Instead, the fields would be like this:
MachineID which machine this is
CapacityTypeID what this row is measuring
Capacity Number: the capacity of this machine.
Now you have only 1 column to look in to get the answer.

If you do need to use an unnormalized table, this MinOfList() function will
select the minimum non-null value from across several columns:
http://allenbrowne.com/func-09.html

 

[JDB]

Thanks very much. This will be very helpful. I am passing the data
to a linear program so need the data in the table in that fashion.

Can I then use this function in a calculated field of a query.

Thanks again from Oceana

JDB

 

[Lord Kelvan]

umm i agree with allen you shouldnt have a column per machine you
should have a row per machine unless we are missunderstanding how the
data works if you wanted the max accross a set of columns you could
try a corsstab query but thats just a guess

 

[JDB]

I am developing a routing thru a factory for an SKU. This SKU can
follow one of several paths. Each set of machines on that path has a
capacity, and the min of the capacity is the max speed of the line. I
ran a query on the normalized tables for each machine and have many
unique routings. I need to calculate the min of the capacities of the
route.

A crosstab query would be nice but I have nothing to cross tab.

Let me think about your ideas tonight becasue maybe there is something
I am not seeing in your suggestions.

Thanks for the input.

 

[JDB]

I am developing a routing thru a factory for an SKU.  This SKU can
follow one of several paths.  Each set of machines on that path has a
capacity, and theminof the capacity is the max speed of the line.  I
ran a query on the normalized tables for each machine and have many
unique routings.  I need to calculate theminof the capacities of the
route.

A crosstab query would be nice but I have nothing to cross tab.

Let me think about your ideas tonight becasue maybe there is something
I am not seeing in your suggestions.

Thanks for the input.



- Show quoted text -

I built a simple function to calculate the min value and put it into a
calc field(which I did not know I could do.
Problems solved.

Thanks

 

[John Spencer]

Yes, you can use the function in a query.

In Design View you would enter something like the following in a field "cell".

TheSmallest: MinOfList([Fielda],[Fieldb],[Fieldc])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County