MS Access-Query
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
March 6, 2008 4:54 PM
UPDATED:
March 12, 2008 11:15 PM
Answer Wiki:
this should help:
<pre>SELECT tablename.TradeID, Min(tablename.TradeValue) AS Lowest
FROM tablename
GROUP BY tablename.TradeID</pre>
"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.
To see all answers submitted to the Answer Wiki: View Answer History.
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
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.
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.