match field based on table

[odun]

Hello all,

Happy New Year.

Hope you ca help with the following.

I have 2 tables in access. One is a table with the us state abbreviations.

I have another table, one of the fields is an address field, e.g.

SOQUEL CA 95073
SOUTHAVEN, WA 98671
SOUTHBURY, CT 06488
SPENCER IA 51301
SPOKANE, WA 99201
SPRINGFE VA 22150

I would like to create a query, joining these two tables together so that the query can give me the 2 state abbreviation e.g.:

Address field/ Abbreviation field
SOQUEL, CA 95073/ CA
SOUTHAVEN, WA 98671/ WA
SOUTHBURY, CT 06488/ CT
SPENCER, IA 51301/ IA
SPOKANE, WA 99201/ WA
SPRINGFE, VA 22150/ VA

Is this possible?

Thanks for your help,

 

[Kraj]

You'll save yourself a heck of a lot of grief if you split the address field into seperate city, state and zip fields. Seriously, don't even try to work around a combined address field. You're better off splitting the data up.