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