State and Zip Mismatch in Access 97

[hianjali]

Hello,

I have to pull in the records which has state and zip mismatch. For this i created two tables state and zip.

State with State_ID,State_Code,State_name
Zip with State_ID,Zip

Where State_ID is primary key and Autogenerated.

Could someone give me the logic about how do i validate...this fields to the table "elgadr" with fields "erstat","erzpcd" for state and zip..

Thanks!!

 

[PHV]

Create a query named, say, qryValidZip:
SELECT S.State_Code, Z.Zip
FROM State As S INNER JOIN Zip As Z ON S.State_ID = Z.State_ID

Then your check query:
SELECT A.*
FROM yourTable As A LEFT JOIN qryValidZip As V
ON A.State_Code = V.State_Code AND A.Zip = V.Zip
WHERE V.Zip Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hianjali]

Thanks PHV. I wrote the first query as

SELECT State.State_Code, ZipCode.ZipCode
FROM State INNER JOIN ZipCode ON State.State_ID = ZipCode.State_ID;

and named as Qry_StateZip. It returned me all the state_ code and ZipCode

(Date Stored in State Table looks like)
State_ID State_Code State_Name
1 AL Alabama
5 AR Arkansas

(Data Stored in ZipCode Table looks like)
State_ID ZipCode
1 350 - 369
5 716 - 729 & 755

(Data Stored in elgadr table looks like)

erstat erzpcd
CA 95356-1751
CA 949201038

So, basically i will have to check the first 3 digits to check the valid zipcode for each state...

 

[hianjali]

Someone please help...can i use left function?

 

[MichaelRed]

even the complete zip code may overlap state boundaries. The limitation to three digits is certainly going to cause more mis-matches. Further, the Zip code assignemnt is not a STATIC data set, so it would be necessary to re-fresh the assignments frequently.





MichaelRed

 

[lespaul]

plus I would think you would need your zipcode table to store something more like:

State_ID    BeginZipCodeRange      EndZipCodeRange
1            350                        369
5            716                        729
5            755                        755

or

StateID      ZipCode
1              350
1              351
1              352....
1              369
5              716
5              717

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design