Find Min Value in Row or Selected Cells

J

JCowell

I have a query where I have product SKU's with various Pgrogram Prices. I
want to create an expression to look at these multiple prices and return the
lowest price and/or the heading for the lowest price:


Name APrice BPrice CPrice SeasonP FlyerP *BestProg *Best
Price
Widgit 1 1.01 1.25 1.54 1.11 0.99 FlyerP
0.99
Widgit 2 2.01 2.12 2.37 2.99
APrice 2.01
Widgit 3 1.23 1.22 1.25 1.24
BPrice 1.22
Widgit 4 4.25 4.35 4.76 3.99
SeasonP 3.99

I have list of items with different program prices. I want to create
*BestProg and *Best Price. I'm looking at about 110,000 seperate records so
I'm trying to create an expression in a query to find this info for me.
Pretty new Access but, I know the basics. Not really comfortable with SQL
but can use it a little. Any help would be greatly appreciated. There are
some Blank values where a specific item may not have independant pricing on
all programs.
 
J

John W. Vinson

I have a query where I have product SKU's with various Pgrogram Prices. I
want to create an expression to look at these multiple prices and return the
lowest price and/or the heading for the lowest price:


Name APrice BPrice CPrice SeasonP FlyerP *BestProg *Best
Price
Widgit 1 1.01 1.25 1.54 1.11 0.99 FlyerP
0.99
Widgit 2 2.01 2.12 2.37 2.99
APrice 2.01
Widgit 3 1.23 1.22 1.25 1.24
BPrice 1.22
Widgit 4 4.25 4.35 4.76 3.99
SeasonP 3.99

I have list of items with different program prices. I want to create
*BestProg and *Best Price. I'm looking at about 110,000 seperate records so
I'm trying to create an expression in a query to find this info for me.
Pretty new Access but, I know the basics. Not really comfortable with SQL
but can use it a little. Any help would be greatly appreciated. There are
some Blank values where a specific item may not have independant pricing on
all programs.
Click to expand...

The problem is that you're "committing spreadsheet" - your table design is
WRONG. If you have a one (item) to many (prices) relationship, a better design
is to use two tables in a one to many relationship:

Products
ProductID <e.g. an autonumber or a SKU>
ProductName <e.g. "Widgit 1">

Programs
ProgramID < primary key>
ProgramName <e.g. A, B, Season, Flyer>
<you might want fields for effective date, etc.>
ProgramPrice
ProductID <which product is this a price for>
ProgramID <which program>
Price <e.g. 2.01>

You could then use a very straightforward Totals query to go across records
and find the best price. This would let you easily add a new price program;
with your current design you'll need to change the structure of your table,
rewrite all your queries, redesign all your forms and reports... ouch!!!

You can use a "Normalizing Union" query to recast your current data:

SELECT [Name], "A" As Program, APrice AS Price
FROM yourtable
WHERE APrice IS NOT NULL
UNION ALL
SELECT [Name], "B" As Program, BPrice AS Price
FROM yourtable
WHERE BPrice IS NOT NULL
UNION ALL
SELECT [Name], "Flyer" As Program, Flyer AS Price
FROM yourtable
WHERE Flyer IS NOT NULL
UNION ALL
<etc>

Base a Totals query on this query, grouping by name and selecting Min of Price
for example.
 
Top