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

Question

I am slowly exploring access to make a database of products and suppliers for my parents' business. I have a product table indexed by our product link, and 2 more tables for 2 different suppliers that contain a link to supplier products and a price that links to our link.

I made a query that performs a left outer join in such a way that it returns a table of our products with every link and vendor price, i.e.

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

Here is 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, therefore, an external connection.

( ?) - , , , , ..

Ref | Suppplier Ref |

, SQL , , - , .

,

Answer 1

, , . product_catalog :

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

:

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

GROUP BY, qryLowestPrice, :

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

qryLowestPrice (product_name supplier_id), :

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);

:

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

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 you will want to have an appropriate Ref provider at the lowest price. This can be done using the construct CASE WHEN:

CASE WHEN expression THEN true_part ELSE false_part END

This will:

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"

How you use Access, the syntax is a little 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"