Zip Codes

[Laurie B.]

I have a table with a zip code field formatted as text. All zip codes in this table have 5 numbers. [no hyphen plus the addtional 4 numbers].



I had to link this zip code table to another table by zip code (formatted as text). The result is that I am only getting the zip codes that match the zip codes from the second table that have the 5 digits. Example - I am getting all the 12345 zipcodes but not the 12345-6789.

I appreciate your help. I just received my Microsoft Academic Course for Access 2007.
Laurie B.
New User

[ajetrumpet]

help with what, exactly? there really is no question.

[Laurie B.]

Sorry for not being clear. I need help in understanding why the query results are only listing the Accounts with a zip code = 12345 and not including the accounts with 12345-6789. My relationship between the two tables are the zip codes.

What I need to see from Table B, the "Accounts" table are all the accounts with the zip codes from Table A, the "Zip Code" table. The accounts table is not consistant with the zip code format of zip + 4. There are records with zip codes #####-#### and then there are records that just have #####.

My dilemma is that my query is only returning those Accounts that have zipcodes equal 12345 in the zip code field from the Account table. I need to see ABC Company however many times it is in the Accounts table whether it has a zip code of 12345 or 12345-####.

Do I need to format this zip code field differently in the tables or do I need to be more specific and write something in the query design itself?

I hope this helps.
Laurie B.

[ajetrumpet]

what's the sql?

[Laurie B.]

SELECT [APR 104 zip codes].ZIP, [District Account List].BUSINESS_PARTNER, [District Account List].NAME1, [District Account List].NAME2, [District Account List].STREET, [District Account List].CITY1, [District Account List].STATE, [District Account List].ZIP
FROM [APR 104 zip codes] LEFT JOIN [District Account List] ON [APR 104 zip codes].ZIP = [District Account List].ZIP;

I can see and understand that it is doing exactly what I am asking.

[ajetrumpet]

actually, if you left join these two tables, you're not going to see the 9 digit zips, unless it has a matching parent in the zip table 'A'.

if you want to see everything, change the join props in the query wizard to a RIGHT JOIN. You'll see blank records inserted on the parent side for 9 digits zips, but at least you'll see them.

[Laurie B.]

Although I had some cleaning up to do - that worked.

Thank you and I appreciate your time.
Laurie B.