Look up values

[Saturn57]

I have a table with an item number and prices from several vendors and I would like to be able to look up the prices for a particular item and return the three best prices. Can anyone help me with this function?

 

[SkipVought]

"...return the three best prices..."

What's your definition of best?

Seems to me, it depends on which side of the cash register yer standin'.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[genomon]

Well, without table names & field names, I would say have a look at inner joins and the TOP keyword in SQL. This could get you started:

http://www.justphukit.com/sql/sql-tutorials-1.php

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[Saturn57]

Looking to buy so the least expensive prices.

 

[lespaul]

too funny Skip!!

SELECT TOP 3 ItemNumber, Prices FROM TableName Order by Prices ASC

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[ck1999]

Can you not change the sort order to get the bottom 3?

ck1999

 

[Saturn57]

Not sure how to apply SELECT TOP 3..... since I have a column with each vendors prices in it along side the item number eg.

ITEM # PRICEVENDOR1 PRICEVENDOR2 PRICEVENDOR3 etc....
1234 27.23 10.00 5.00

 

[ck1999]

How many vendors do you have?

It makes it harder since the table in not normalized!

ck1999

 

[genomon]

You could load the values in a multidimensional array, then select out the "best" price for each vendor. Or to keep it simple, develop a normalized database, where each vendor has its own table. You can keep the current structure (bad idea) and just create new tables for each vendor with part number and price, loading them with SQL; then select out what you need...

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[Saturn57]

If i use a table for each vendor how do I choose the best three???

 

[genomon]

Create a table for each vendor.
Select the "best" price from each.
Logically compare the prices.

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[lespaul]

create a query that normalizes your data:

SELECT Item, "Vendor1" As Who, PriceVendor1 As Price FROM tableName WHERE PriceVendor1<> 0
UNION
SELECT Item, "Vendor2" As Who, PriceVendor2 FROM tableName WHERE PriceVendor2<> 0
etc
SELECT Item, "Vendorn" As Who, PriceVendorn FROM tableName WHERE PriceVendorn<> 0

for however many Vendor fields you have .

then:

SELECT TOP 3 Item, Who, Price FROM qryNormal WHERE Item = 1234 ORDER BY price ASC

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Saturn57]

Im sorry but my programming skills are limited. COuld you give me an example. Thanks in advance.

 

[lespaul]

I did....paste the first SQL above in a query (you'll need to open the SQL view of a query, not the design grid), put in the correct table name, add any other Vendor# fields you have and name the query: qryNormal

paste the second SQL in a different query, run it.

you should get what you need. You may need to make sure that PriceVendor is a number field, if it's a text field you'll need to add " around it...make sure that Item is a number field, if it's text it will also need " around it.

leslie

 

[Saturn57]

We have many vendors with prices is there an easier way than setting this up with a table for each vendor and comparing each one?

 

[lespaul]

I didn't suggest setting up a table for each one...did you try my suggestions?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Saturn57]

No I will try now. But I have a question right now I have prices in a table and the vendor is the field name. Do I have to create a row that puts the vendor name repeatedly next to each price in order to call it out or can I associate the field name to the price without doing that.

 

[lespaul]

The first query is going to take this table
[tt]
ITEM # PRICEVENDOR1 PRICEVENDOR2 PRICEVENDOR3 etc....
1234 27.23 10.00 5.00
[/tt]

and transform it into this result set:
[tt]
Item Who Price
1234 Vendor1 27.23
1234 Vendor2 10.00
1234 Vendor3 5.00
etc.
[/tt]
for every item in the table. The second one is going to return:
[tt]
Item Who Price
1234 Vendor3 5.00
1234 Vendor2 10.00
1234 Vendor1 27.23[/tt]
for whatever item number you enter in the criteria of the query:

WHERE Item = 1234

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[SkipVought]

"Do I have to create a row that puts the vendor name repeatedly next to each price in order to call it out or can I associate the field name to the price without doing that."

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}