title: How do I compare 2 fields and return the lowest value of each record?
body: <p>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.</p>
  
  <p>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:</p>
  
  <blockquote>
    <p>Ref | Product Name | Supplier 1 Ref |
    Supplier 1 Price | Supplier 2 Ref |
    Supplier 2 Price</p>
  </blockquote>
  
  <p>Here's the query I used:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Not all products are available from both suppliers, hence the outer join.</p>
  
  <p>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:</p>
  
  <blockquote>
    <p>Ref | Cheapest Suppplier Ref |
    Cheapest Supplier Price</p>
  </blockquote>
  
  <p>Unfortunately my SQL knowledge isn't quite good enough to figure this out, but if anyone can help i'd really appreciate it.</p>
  
  <p>Thanks, Rob</p>