Query on 2 tables with like data

[DJM1216]

Hi there, here is my scenario.

I have 2 tables, one which is a customer address list, the other is a list of specific zip codes I need a customer count for.

The problem is the customer list has zip formatted in zip+4 (for example XXXXXXXXX) while the zip code list only contains 5 digit zips (XXXXX).

Is there a way to set up this query to disregard the last four digits of the zip? Or is there a way in Access to parse a column?

Also, how do I run a count query?

Thanks in advance for your assistance.

 

[Andrew Fergus]

Hi there, try this :

LEFT([ZIPCODE],5)

NB : this assumes you have called your variable "ZIPCODE" and the variable name in the customer address list table is different to the variable name for the zip code in the zip codes table (otherwise this won't work).

Hope this helps, Andrew

Is there a way to set up this query to disregard the last four digits of the zip?

 

[Norie]

Create a new field in the customer list call it say ShortZip.

Then create an update query to populate this field using Andrew's suggestion. Left([Zipcode],5).

You can now create a query linking the customer list and the specific zip codes using this new field.