Home > IT Answers > Development > MS Access-Query
Help

Question

  Asked: Mar 6 2008   4:54 PM GMT
  Asked by: FranktheMan

MS Access-Query


Access, Database programming, Microsoft Access

I am Using MS Access. I have a table that has price quotes from subcontractors. The fields are: SubID; TradeID; TradeValue (this is the Price Quote). I typically have 3 quotes for each TradeID. How do I query to get a result of the lowest price for each TradeID?

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
-1
Click to Vote:
  •   0
  •  -1
Last Answered: Mar 7 2008  8:28 AM GMT  by Mrdenny


Latest Contributors: Buddyfarr


this should help:

SELECT tablename.TradeID, Min(tablename.TradeValue) AS Lowest
 FROM tablename
 GROUP BY tablename.TradeID


"tablename" would be the name of the table that you are pulling this information from in Access.

This updated code is based on the new requirement that it show the lowest quote per TradeId. To sort the data by on column or the other add the ORDER BY command after the GROUP BY command.
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Development and Database.

Looking for relevant Development Whitepapers? Visit the SearchWinDevelopment.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

FranktheMan  |   Mar 6 2008  9:22PM GMT

I see what you are doing with this answer.. But I must have phased my question wrong.
I want ONLY the Lowest Price(TradeValue) In each catagory (TradeID)
SQL you gave just sorts the prices

 

Buddyfarr  |   Mar 6 2008  9:30PM GMT

well if you use the above it will give you the lowest to highest, you could add a FIRST statement to choose only the very first one once it is ordered lowest to highest and that would give you the first answer, which would be the lowest of all of them.

 

Ahafez  |   Mar 12 2008  11:15PM GMT

Use a query and Group on TradeID and for the tradevalue set it to Min, this should display only the minimum value for each TradeId. Use the query as a Source in a Report or Form designe.