Count in Zip Codes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I'd like to design a query that counts number people in zip codes. My zip
codes, however, are set up in 9 digits. How do I group zip codes in 5 digits?

Thanks,
Ellen
 
Make a calculated field on just the left five and group/count on that.

Example:

SELECT Left([Zip Code],5) AS Zip, Count(Left([Zip Code],5)) AS CountZip
FROM tblHomeAddress INNER JOIN tblPeople ON tblHomeAddress.[Home Address ID]
= tblPeople.[Home Address ID]
GROUP BY Left([Zip Code],5);
 
Thanks, Wayne!!

Now my zip codes end with a "-". How can I get rid of that ending hyphen,
so that only 5 digits show as the zip code?

Ellen

Wayne Morgan said:
Make a calculated field on just the left five and group/count on that.

Example:

SELECT Left([Zip Code],5) AS Zip, Count(Left([Zip Code],5)) AS CountZip
FROM tblHomeAddress INNER JOIN tblPeople ON tblHomeAddress.[Home Address ID]
= tblPeople.[Home Address ID]
GROUP BY Left([Zip Code],5);


--
Wayne Morgan
MS Access MVP


Ellen said:
Hello,
I'd like to design a query that counts number people in zip codes. My zip
codes, however, are set up in 9 digits. How do I group zip codes in 5
digits?

Thanks,
Ellen
Click to expand...
Click to expand...
 
The 5 left characters should all be numbers, even if you are storing the "-"
character in the field. However, if you are formatting the field and
including the "-" in the format, it will show, but isn't actually there. The
field doesn't need to be formatted for the query, but probably doesn't
matter either, as far as the count goes.

Also, I realized that I had a logic error in the example I posted last time.
This should work better.

SELECT Left([tblHomeAddress].[Zip Code],5) AS Zip, Count(tblPeople.[Person
ID]) AS CountZip
FROM tblHomeAddress INNER JOIN tblPeople ON tblHomeAddress.[Home Address ID]
= tblPeople.[Home Address ID]
GROUP BY Left([tblHomeAddress].[Zip Code],5);

--
Wayne Morgan
MS Access MVP


Ellen said:
Thanks, Wayne!!

Now my zip codes end with a "-". How can I get rid of that ending hyphen,
so that only 5 digits show as the zip code?

Ellen

Wayne Morgan said:
Make a calculated field on just the left five and group/count on that.

Example:

SELECT Left([Zip Code],5) AS Zip, Count(Left([Zip Code],5)) AS CountZip
FROM tblHomeAddress INNER JOIN tblPeople ON tblHomeAddress.[Home Address
ID]
= tblPeople.[Home Address ID]
GROUP BY Left([Zip Code],5);


--
Wayne Morgan
MS Access MVP


Ellen said:
Hello,
I'd like to design a query that counts number people in zip codes. My
zip
codes, however, are set up in 9 digits. How do I group zip codes in 5
digits?

Thanks,
Ellen
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Wayne. I'll need to study your script to understand it, but what was
helpful was the "Left([Zip Code],5" part. I found that if I remove the input
mask, the hyphens disappear.

Bye,
Ellen

Wayne Morgan said:
The 5 left characters should all be numbers, even if you are storing the "-"
character in the field. However, if you are formatting the field and
including the "-" in the format, it will show, but isn't actually there. The
field doesn't need to be formatted for the query, but probably doesn't
matter either, as far as the count goes.

Also, I realized that I had a logic error in the example I posted last time.
This should work better.

SELECT Left([tblHomeAddress].[Zip Code],5) AS Zip, Count(tblPeople.[Person
ID]) AS CountZip
FROM tblHomeAddress INNER JOIN tblPeople ON tblHomeAddress.[Home Address ID]
= tblPeople.[Home Address ID]
GROUP BY Left([tblHomeAddress].[Zip Code],5);

--
Wayne Morgan
MS Access MVP


Ellen said:
Thanks, Wayne!!

Now my zip codes end with a "-". How can I get rid of that ending hyphen,
so that only 5 digits show as the zip code?

Ellen

Wayne Morgan said:
Make a calculated field on just the left five and group/count on that.

Example:

SELECT Left([Zip Code],5) AS Zip, Count(Left([Zip Code],5)) AS CountZip
FROM tblHomeAddress INNER JOIN tblPeople ON tblHomeAddress.[Home Address
ID]
= tblPeople.[Home Address ID]
GROUP BY Left([Zip Code],5);


--
Wayne Morgan
MS Access MVP


Hello,
I'd like to design a query that counts number people in zip codes. My
zip
codes, however, are set up in 9 digits. How do I group zip codes in 5
digits?

Thanks,
Ellen
Click to expand...
Click to expand...
Click to expand...
 
Back
Top