How do I compare 2 fields and return the lowest value of each record?

Question

I'm slowly learning access to make a database of products and suppliers for my parents' business. What i've got is a table of products indexed by our product reference and 2 more tables for 2 different suppliers that contains the suppliers product reference and price that links with our reference.

I've made a query that performs a left outer join such that it returns a table of our products with each supplier's reference and price, i.e:

Ref | Product Name | Supplier 1 Ref | Supplier 1 Price | Supplier 2 Ref | Supplier 2 Price

Here's the query I used:

SELECT Catalog.Ref, Catalog.[Product Name], Catalog.Price, 
    [D Products].[Supplier Ref], [D Products].Cost, 
    [GS Products].[Supplier Ref], [GS Products].Cost

FROM ([Catalog] LEFT JOIN [D Products] ON Catalog.Ref = [D Products].Ref)

LEFT JOIN [GS Products] ON Catalog.Ref = [GS Products].Ref;

Not all products are available from both suppliers, hence the outer join.

What I want to do (with a query?) is to take the table produced by the query above and simply show the product reference, cheapest supplier reference and cheapest supplier price, i.e:

Ref | Cheapest Suppplier Ref | Cheapest Supplier Price

Unfortunately my SQL knowledge isn't quite good enough to figure this out, but if anyone can help i'd really appreciate it.

Thanks, Rob

Answer 1

Please consider a single table to hold your supplier product cost data. With this sample product_catalog data:

product_id product_name price
         1 foo              5
         2 bar             10
         3 baz             15

The suppliers table might look like this:

supplier_id product_id cost
D                    1 2.5
D                    2 7.5
GS                   1 2.75
GS                   3 10

Then a GROUP BY query, qryLowestPrice, will show you the lowest cost for each product:

SELECT s.product_id, Min(s.cost) AS MinOfcost
FROM suppliers AS s
GROUP BY s.product_id;

You can then use qryLowestPrice in another query to pull in the other information (product_name and supplier_id) you want:

SELECT q.product_id, p.product_name, p.price, s.supplier_id, s.cost
FROM
    (qryLowestPrice AS q
    INNER JOIN product_catalog AS p
    ON q.product_id = p.product_id)
    INNER JOIN suppliers AS s
    ON (q.MinOfcost = s.cost) AND (q.product_id = s.product_id);

This is the output from that second query based on the sample data above:

product_id product_name price supplier_id cost
         1 foo              5 D           2.5
         2 bar             10 D           7.5
         3 baz             15 GS          10

Answer 2

Try this

SELECT  Catalog.Ref, 
        Catalog.[Product Name], 
        Catalog.Price, 
        [D Products].[Supplier Ref], 
        [D Products].Cost, 
        [GS Products].[Supplier Ref], 
        [GS Products].Cost,
        iif ([D Products].Cost < [GS Products].Cost, [D Products].[Supplier Ref], [GS Products].[Supplier Ref]) AS CheapestSupplier,
        iif ([D Products].Cost < [GS Products].Cost, [D Products].Cost, [GS Products].Cost) AS CheapestSupplierPrice
FROM (  [Catalog] LEFT JOIN 
        [D Products] ON Catalog.Ref = [D Products].Ref) LEFT JOIN 
        [GS Products] ON Catalog.Ref = [GS Products].Ref;

Answer 3

I think that you'll want to have the corresponding Supplier Ref for the lowest price. It can be done with a CASE WHEN construct :

CASE WHEN expression THEN true_part ELSE false_part END

It'll be :

SELECT Ref,
  CASE WHEN Supplier1Price < Supplier2Price THEN Supplier1Ref ELSE Supplier2Ref END AS "Cheapest Supplier Ref",
  CASE WHEN Supplier1Price < Supplier2Price THEN Supplier1Price ELSE Supplier2Price END AS "Cheapest Supplier Price"

As you're using Access, the syntax is a bit different. You can use the function IIF(condition, true_part, false_part).

SELECT Ref,
  IIF(Supplier1Price < Supplier2Price, Supplier1Ref, Supplier2Ref) AS "Cheapest Supplier Ref",
  IIF(Supplier1Price < Supplier2Price, Supplier1Price, Supplier2Price) AS "Cheapest Supplier Price"