How do I identify the lowest cost supplier from a list of 5?

W

Wayne-I-M

There is no question in the main area. ??

Also - if you have field names and formats etc you will get a better answer.
The more information you give the better the answer you may get.
 
B

Bob Quintal

=?Utf-8?B?U21hbGxSZXRhaWxlcg==?=
[email protected]:

The same way you' would do it manually: sort the list on cost and
pick the top 1.

The SQL would be

SELECT TOP 1 quotations.* from quotations order by cost.

In the query builder, make your query to select all the quotations
and just change the property labeled Top Values from all to 1
 
S

SmallRetailer

I'm new to Access. The information is across 5 fields by product for each
supplier. I have tried getting a min/max for the range into a column without
success.
 
B

Bob Quintal

=?Utf-8?B?U21hbGxSZXRhaWxlcg==?=
I'm new to Access. The information is across 5 fields by
product for each supplier. I have tried getting a min/max for
the range into a column without success.
Click to expand...

what is the information? It is impossible to provide specific
answers with such a general statement.
 
J

John W. Vinson

I'm new to Access. The information is across 5 fields by product for each
supplier. I have tried getting a min/max for the range into a column without
success.
Click to expand...

Well... that's because your table design is incorrect. You're "committing
spreadsheet". What will you do if you ever need a SIXTH supplier?

A properly normalized table design would have three fields (possibly more but
three essential ones): ProductID ( a link to a table of products ); SupplierID
( a link to a table of suppliers ); and the price. The query is very simple
with this design - just a totals query with a Max.

To get the maximum value of five *fields in a record* requires some VBA code.
But you'll really be better off recasting your table into a properly
normalized structure!

John W. Vinson [MVP]
 
Top