Access 2007 Subquery syntax needed or other method?

I have a query that produces this:

Vendor      Department      Items_Per_Dept_Per_Vend
JAM                Drum                            615
JAM                Band                            602
JAM                      Guitar                            222
Fender             Guitar                           349
Fender          Pro-Audio                             4

I'd like another query to summarize the records with the top values of the most "Items_Per_Dept_Per_Vendor" for each vendor with it's assigned department:
JAM             Drum                               615
Fender       Guitar                              349

Thanks, Kevin
itsquadSystems AdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YZlatCommented:
SELECT t1.Vendor, t1.Department,a.MaxItems
FROM Table1 as t1, 
(SELECT Vendor, MAX(Items_Per_Dept_Per_Vend) As MaxItems FROM Table1 GROUP BY Vendor) as a
WHERE t1.Vendor=a.Vendor AND t1.Items_Per_Dept_Per_Vend=a.MaxItems

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itsquadSystems AdminAuthor Commented:
So bizarre to see a query in design view not have the original query or table that's being called on/used... until you view & do it in the SQL View.  Then it gradually got easier and easier to see what the SQL was doing with the "Alias" or "AS".  What a fun lick to have under my programming belt now!  Thanks again for all the help!!
0
itsquadSystems AdminAuthor Commented:
Microsoft Access should incorporate this as a query wizard - I could see it getting used allot!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.