Rank Values inside Query

[JohnR]

I have a table listing a number of products and the vendor pricing from the
5 vendors who supply the product. I need to make a query that indicates
which customer has the lowest price, then the 2nd lowest price, and the 3rd
lowest price, etc. The end result shoud look something like this

Item Vend1Cost Vend2Cost Vend3Cost ... Lowest 2ndLowest 3rd Lowest
Box 2.00 2.50 1.75 Vend3 Vend1
Vend2

I currently have the first 4 columns and need to add the last 3 in this
example. There will be 5 vendors in the real table.

Thank you in advance for your assistance.

John R

 

[Duane Hookom]

What's your table structure? You are asking us to help you create a query
without a clue how your data is stored.

 

[JohnR]

Product, Vendor1Price, Vendor2Price, Vendor3Price, Vendor4Price,
Vendor5Price.

I need to add 5 columns indicating LowestPricedVendorName,
NextLowestPricedVendorName, etc. for all 5 vendors.

 

[Duane Hookom]

You can't do it because you don't have a field storing vendor name.

 

[Michel Walsh]

Hi,


SQL works vertically, not horizontally, a little bit as a nut is tight
clockwise, not anti-clockwise (generally).

If you would have:

Item, Vendor, Cost ' fields
Box 1 2.00
Box 2 2.50
Box 3 1.75 ' data sample


then,


SELECT item, vendor, cost
FROM myTable
ORDER BY item, cost DESC


does the job.


If you really need the rank of the cost, by item:


SELECT a.item, a.cost, LAST(a.Vendor), COUNT(*) as rank
FROM myTable As a INNER JOIN myTable As b
ON a.item=b.item AND a.cost >= b.cost
GROUP BY a.item, a.cost
ORDER BY a.item, cost DESC;



and you can then use a crosstab, on that query, to put the data back in an
horizontal "presentation".



Hoping it may help,
Vanderghast, Access MVP