Access 2003: Can’t filter Part-no’s by highest $Spend Plant/Site
Home > IT Answers > Microsoft Windows > Access 2003: Can't filter Part-no's by highes...
GatorRobNC
5 pts.
0
Q:
Access 2003: Can't filter Part-no's by highest $Spend Plant/Site
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.
ASKED: Oct 30 2009  6:32 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Carlosdl
29855 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Try with an SQL query like this:

 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;


or this:

SELECT *
 FROM your_table 
 WHERE (part_no,plant_spend) IN
 (SELECT part_no,MAX(plant_spend)
 FROM your_table 
 GROUP BY part_no);


(The second syntax may not work in Access)
Last Answered: Oct 30 2009  7:31 PM GMT by Carlosdl   29855 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



0