How do I compare 2 fields and return the lowest value of each record?
- by BigRob
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
