Help! Lookup ?.

[IATAILG8ER]

I am new to access and have I question that I think should be easy to answer. I am importing a mailing list from text to access. The list contains these fields Name, Address, City, State, Zip. What I would like to do is create a field in that table based on the zip code field. The new field would be zipthree and would contain the first three digits of the zip code field. Can I do this in the table or does it it have to be a query. Any help would be great. Thanks

Ex: Record 1 / John Doe / 123 Main St / Anytown / St / 12345-4545 / 123

123 field is not in the original data but would be created from the first three digits on the zip code field.

 

[Elana]

Since the first three digits can always be calculated, you don't need to create a field in the table to store them.

Use this in your query to extract the first three digits:

In the query grid, place this code:

ZipFirstThree: Left([ZIP],3)

That's it...

hth
Elana

 

[IATAILG8ER]

I still am having problems

I tried that and it still didn't work for me. Here is what I am trying to do. I've attached my database as an example. I have two tables one called "add1" that contains the addresses and another one called "zone" that contains the zones for the addresses. My query will look up the 3 digit "zipzone" and assign a zone number to it. What I am doing is creating the zipzone in excel and importing it into access. What I would like to do is import just the zip code field and assign the zone from it. Thank you for responding to my earlier message.

 

[neileg]

No database attached!

Assuming you import your zone table into Access and it looks like this:
tblZone
ZipZone
ZoneNumber

Create a query based on your address table and add a field like Elana says.

Then create a second query. Bring in the first query and tblZone. Create a relationship between the calculated field ZipFirstThree and ZipZone by clicking and dragging from one to the other in the upper pane of the query. The drop all of the fields from your first query into the query grid, and drop the field ZoneNumber into the grid too. Run the query and this will attach the zone number to your addresses.

 

[IATAILG8ER]

Thanks

Thanks, that worked great. I didn't fully understand where I was suppose to put the formula. I am kind of green when it comes to access and was trying to build it in the criteria box for that field. It works fine now. I have just one other question. Let's say some of my records don't have a zip or the zip is Canadian. My zone table only contains 000 thru 999. How can I get access to import the other records and assign a 0 (zero) as the zone.

Example: T3K 1T3 is a Canadian zip so the first three digits would be T3K, which doesn't fall in my zone table. I would like to assign a zero as the zone. Currently the records aren't showing up in the query because they don't match anything in the zone table. Any help would be great Thanks Chuck.

 

[neileg]

If Canadian codes always begin with a letter, use ISNUMBER in an iff statement to trap these and the blank fields eg:
ZipFirstThree: iff(ISNUMBER(Left(ZIP,1),Left([ZIP],3),"XXX")
and put XXX in your zone table with a value of 0.