Count in Zip Codes

[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

 

[Wayne Morgan]

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);

 

[Guest]

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

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

 

[Wayne Morgan]

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

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

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

 

[Guest]

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

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

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

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