querry LOC

  • Thread starter Thread starter Octet32
  • Start date Start date
O

Octet32

I have a table that has 5 fields [State][Zip][Company][RT][LOC_NO]
There are many RT numbers can I show which route that has the most LOC_NO
for each zip and company number?



SELECT Zip_codes, Company,RT,
Count(LOC_NO)
FROM ZIPS
GROUP BY Zip, Company,RT, State
HAVING (((State)="AK"))
ORDER BY Zip;






Zip company RT LOC_NO
99501 200 301 3
99501 200 305 3
99501 200 310 1484
99501 200 320 5
99501 200 330 5
99501 200 340 7
99501 200 350 2
99501 200 360 12
99501 200 370 1
99501 200 380 4
 
One method would use a correlated sub-query in the where clause.

SELECT Zip, Company,RT
FROM ZIPS
WHERE RT In
(SELECT TOP 1 RT
FROM ZIPS as Temp
WHERE Temp.Zip = ZIPS.Zip
AND Temp.Company = ZIPS.Company
GROUP BY RT
ORDER BY Count(Loc_NO) Desc)
WHERE State = "AK"
ORDER BY Zip

By the way it is usually best if you post the actual SQL you are using by copy
and paste instead of typing. For instance, you refer to a field as Zip_Codes
and Zip - so that can lead to confusion when attempting to answer your question.

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

John Spencer said:
One method would use a correlated sub-query in the where clause.

SELECT Zip, Company,RT
FROM ZIPS
WHERE RT In
(SELECT TOP 1 RT
FROM ZIPS as Temp
WHERE Temp.Zip = ZIPS.Zip
AND Temp.Company = ZIPS.Company
GROUP BY RT
ORDER BY Count(Loc_NO) Desc)
WHERE State = "AK"
ORDER BY Zip

By the way it is usually best if you post the actual SQL you are using by copy
and paste instead of typing. For instance, you refer to a field as Zip_Codes
and Zip - so that can lead to confusion when attempting to answer your question.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have a table that has 5 fields [State][Zip][Company][RT][LOC_NO]
There are many RT numbers can I show which route that has the most LOC_NO
for each zip and company number?



SELECT Zip_codes, Company,RT,
Count(LOC_NO)
FROM ZIPS
GROUP BY Zip, Company,RT, State
HAVING (((State)="AK"))
ORDER BY Zip;






Zip company RT LOC_NO
99501 200 301 3
99501 200 305 3
99501 200 310 1484
99501 200 320 5
99501 200 330 5
99501 200 340 7
99501 200 350 2
99501 200 360 12
99501 200 370 1
99501 200 380 4
Click to expand...
.
Click to expand...
 
Back
Top