Query to select lowest cost vendor (1 Viewer)

DampFloor

Registered User.
Local time
Yesterday, 23:46
Joined
Nov 16, 2017
Messages
37
Hello,

I am working on a school project in which i need to create a database for a restaurant chain. right now i have two table in which i input quotes for a specific category of ingredients from two different suppliers. I want a query which can identify which supplier is the overall cheapest and then populate a table with those ingredients and prices.

Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:46
Joined
May 7, 2009
Messages
19,169
you only need 1 table there.
the table must have:

supplierID
ingredientID
quotationPrice

then you can use aggregate query to
group supplierID
sum or avg the quotationPrice
add sort order on the
sum of quotationPrice, ascending.

make another query that will
query the above query.
using Select Top 1, you'll get
the the supplieID with the least
price.
 

DampFloor

Registered User.
Local time
Yesterday, 23:46
Joined
Nov 16, 2017
Messages
37
I got a the query to work which shows the sum of both suppliers. I did this one table like you said. However i cannot get a query to work which shows only the min of the two. I would also need all the data from the identified cheapest quotes to populate future tables.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:46
Joined
May 7, 2009
Messages
19,169
on the query that you made, you
add Sort to Quotation amount field.
Order it Descending.

then you create another query based
on the first query you made.

on new query, don't select
any table or query.
press SQL on the ribbon.
then enter:

SELECT TOP 1 * From yourFirstQueryName;
 

DampFloor

Registered User.
Local time
Yesterday, 23:46
Joined
Nov 16, 2017
Messages
37
Ahh got it! Sorry I am quite new to Access and did not know i would have to go into SQL.

So now I have got all the prices for that one category of ingredients. There are several categories for which i will have to do the same. Is it possible to get my entire list of ingredients at their chosen price into one table? I can then use this table to populate engineered menus which will show me the current cost to make each menu item?

Thanks,

Damp
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:46
Joined
Feb 19, 2013
Messages
16,553
did not know i would have to go into SQL
as you get better, you will visit the sql window more and more often.

However be aware that to add TOP 1 and other predicates such as DISTINCT, you can do this from the query design window. Open the properties sheet, click on the top part of the query window and put a 1 in the Top Values property
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:46
Joined
May 7, 2009
Messages
19,169
now that you have the cheapest supplier
(i hope you saved the query),
you will need an Append Query to append
the records from Quotation table
that has the cheapest price to your
ingredient table.

create New Query (Query Design).
add the the Top 1 query you made.
add the quotation table.
drag the supplier id of the
first query to the supplier id of the
Quotation table.
this creates a Join.

select the field from Quotation table that you want to add
to your ingredient table.

on the ribbon, choose Append.
choose your ingredient table from the combobox.
Now, on Append To: field, match the field
of quotation to ingredient table.

run and save the query.
 

DampFloor

Registered User.
Local time
Yesterday, 23:46
Joined
Nov 16, 2017
Messages
37
Great! got it to work. This project has a ways to go so don't be suprised to see my name on this forum again.

Thanks!
 

Users who are viewing this thread

Top Bottom