Max Query

  • Thread starter Thread starter NewtoAccess
  • Start date Start date
N

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.
 
SELECT TOP 1 Organization, [Number of Employees], [Region Code]
FROM YourTable
ORDER BY [Number of Employees];
 
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
 
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.


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

--
KARL DEWEY
Build a little - Test a little


NewtoAccess said:
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.
Click to expand...
Click to expand...
 
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.
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.


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

--
KARL DEWEY
Build a little - Test a little


NewtoAccess said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top