Need Min Value from Row and Heading Value that Corresponds to it.

  • Thread starter Thread starter JCowell
  • Start date Start date
J

JCowell

I have a table that looks somewhat like this w/o the *'d headings:

h1 h2 h3 h4 h5 h6 h7 *Min Price *Heading
$5.00 $6.00 $3.00 $4.00 $9.00 $1.00 $9.00 $1.00 h6
$3.00 $6.00 $8.00 $65.00 $9.00 $2.00 $4.00 $2.00 h6
$4.00 $9.00 $5.00 $7.00 $1.00 $2.00 $8.00 $1.00 h5
$9.00 $7.00 $6.00 $3.00 $9.00 $8.00 $2.00 $2.00 h7
$7.00 $8.00 $9.00 $7.00 $9.00 $8.00 $8.00 $7.00 h1


I have several SKU's with various price contract/programs. I want to find
the best price in one cell and the heading that would correspond to that cell
in another.

I was trying to do this with an expression in a Query. Any help is greatly
appreciated.
 
See the MinOfList() function here:
http://allenbrowne.com/func-09.html

To use it in your query, the expression to type into the Field row would be:
MinOfList([h1], [h1], [h2], [h3], [h4], [h5], [h6], [h7])

In the end, the problem is really with your table design. Repeating fields
violate basic relational design rules. You need many records instead of many
fields. For more info, search for 'normalization'. Here's a list of places
you could start:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
 
Back
Top