If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find Min Value in Row or Selected Cells
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. |
#2
|
|||
|
|||
Find Min Value in Row or Selected Cells
On Sat, 6 Feb 2010 15:41:02 -0800, JCowell
wrote: 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. 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|