Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-05-2017, 04:27 AM   #1
DampFloor
Newly Registered User
 
Join Date: Nov 2017
Posts: 10
Thanks: 3
Thanked 1 Time in 1 Post
DampFloor is on a distinguished road
Query to select lowest cost vendor

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

DampFloor is offline   Reply With Quote
Old 12-05-2017, 04:48 AM   #2
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 4,732
Thanks: 50
Thanked 1,578 Times in 1,504 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about arnelgp has a spectacular aura about
Re: Query to select lowest cost vendor

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 12-06-2017, 06:11 AM   #3
DampFloor
Newly Registered User
 
Join Date: Nov 2017
Posts: 10
Thanks: 3
Thanked 1 Time in 1 Post
DampFloor is on a distinguished road
Re: Query to select lowest cost vendor

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.

DampFloor is offline   Reply With Quote
Old 12-06-2017, 06:49 AM   #4
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 4,732
Thanks: 50
Thanked 1,578 Times in 1,504 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about arnelgp has a spectacular aura about
Re: Query to select lowest cost vendor

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;
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
DampFloor (12-09-2017)
Old 12-07-2017, 04:35 AM   #5
DampFloor
Newly Registered User
 
Join Date: Nov 2017
Posts: 10
Thanks: 3
Thanked 1 Time in 1 Post
DampFloor is on a distinguished road
Re: Query to select lowest cost vendor

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
DampFloor is offline   Reply With Quote
Old 12-07-2017, 05:30 AM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,622
Thanks: 37
Thanked 3,117 Times in 3,026 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: Query to select lowest cost vendor

Quote:
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
DampFloor (12-09-2017)
Old 12-07-2017, 05:49 AM   #7
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 4,732
Thanks: 50
Thanked 1,578 Times in 1,504 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about arnelgp has a spectacular aura about
Re: Query to select lowest cost vendor

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.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 12-10-2017, 05:04 PM   #8
DampFloor
Newly Registered User
 
Join Date: Nov 2017
Posts: 10
Thanks: 3
Thanked 1 Time in 1 Post
DampFloor is on a distinguished road
Re: Query to select lowest cost vendor

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!

DampFloor is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Displaying the lowest cost only cary1234 Queries 14 09-08-2013 08:16 AM
Find Lowest Cost and Matching Column Name chicka7684 Queries 1 01-19-2012 10:08 AM
Select Lowest Value out of Repeated Records snakebonne Queries 4 02-23-2011 06:26 AM
Extracting lowest price from 31 downloaded Excel vendor price lists larrynew Queries 9 04-21-2009 09:57 AM
Query for Lowest 2nd lowest and 3rd lowest value mpb.vu2 Queries 1 05-25-2006 03:59 PM




All times are GMT -8. The time now is 10:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World