Access 2003: Can’t filter Part-no’s by highest $Spend Plant/Site

5 pts.
Tags:
MS Access 2003
SELECT statement
I have a file of Part-no with the spend by Plant (or Location).  A given Part-no may have multiple records for the different Plant Locations.

I am trying to capture one record for each Part-no that included the Plant with the highest spend and what that amount is.

I tried to do a summary query sorting each part by descending order of Plant spend.  Then I made the query a Summary Query with a "Group By" Part-no and using the option of "First" Plant-Name.  Since the file is sorted by descending spend the "First" should get me the highest Plant Name for each part-no. 

This is not working correctly.  Many Part-no's do not have the Highest Spend selected.

I see that there are options to use "From" together with "Where" but I don't know if this will provide the right solution or how to use this feature.

Please advise any suggestions.

Thanks.



Software/Hardware used:
MS Access 2003, Windows XP.

Answer Wiki

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

Try with an SQL query like this:
<pre>
SELECT y.*
FROM your_table y
JOIN
(SELECT part_no,MAX(plant_spend) plant_spend
FROM your_table
GROUP BY part_no) t
ON y.part_no = t.part_no
AND y.plant_spend = t.plant_spend;</pre>

or this:

<pre>SELECT *
FROM your_table
WHERE (part_no,plant_spend) IN
(SELECT part_no,MAX(plant_spend)
FROM your_table
GROUP BY part_no);</pre>

(The second syntax may not work in Access)

Discuss This Question:  

 
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

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