Prev: Changing existing data in database to title case
Next: Querying using SQL statement
From: JCowell on 6 Feb 2010 23:59
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.
From: Allen Browne on 7 Feb 2010 00:53
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"JCowell" <JCowell(a)discussions.microsoft.com> wrote in message
news:567D52A0-D211-43B7-ACF9-50770FD6DB7D(a)microsoft.com...
> 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.

 | 
Pages: 1
Prev: Changing existing data in database to title case
Next: Querying using SQL statement