MS Access-Query

15 pts.
Tags:
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • FranktheMan
    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
    15 pointsBadges:
    report
  • Buddyfarr
    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.
    6,850 pointsBadges:
    report
  • Ahafez
    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.
    60 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following