Max Query

[NewtoAccess]

I need help with what's most likely a very simple query. I have the
following fields:
Organization
Number of Employees
Region Code

I need to pull the region code of the organization with the greatest number
of employees. I rather easily was able to run a query that told me which
organization had the greatest number of people, but I had some difficulty in
pulling in the region code.

Any help is greatly appreciated.

[KARL DEWEY]

SELECT TOP 1 Organization, [Number of Employees], [Region Code]
FROM YourTable
ORDER BY [Number of Employees];

--
KARL DEWEY
Build a little - Test a little

[John Spencer]

SELECT TOP 1 Organization, [Number of Employees], [Region Code]

FROM SomeTable
ORDER BY [Number of Employees] DESC

OR

SELECT Organization, [Number of Employees], [Region Code]
FROM SomeTable
WHERE [Number of Employees] =
(SELECT Max([Number of Employees])
FROM SomeTable)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

[NewtoAccess]

Karl,

Thank you for the reply. However, this should better illustrate my problem.

Manufacturer Number of Cars Produced Region Code
(Plant Loc.)
Chevy(A) 10 East
Chevy(B) 5 West
Chevy(C) 8 East

I need to know the Region code of the manufacturer that produced the most
cars.

[Bob Barrows [MVP]]

SELECT Top 1 [Region Code] from table
order by [Number of Cars Produced] DESC

Of course if plant Chevy(D) in West also produced 10 cars, then this
query would return two results.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.