zipcode table

[slimjen1]

Hi all. Using access 2003. I have a table which has a zipcode field. I have a update query to have the city and states field based on a zipcode lookup table. When I run it the majority of the city and states fill in the way expected. But there are a handful that dont. For ex. lets say "12345" is one of the zipcode that is listed for 50 records. Out of maybe 50 records; only half of them updates. The other half remains blank. I cant see anything different. Can someone maybe shed some light on this. This is really strange
Thanks so much.

 

[bob fitz]

Can you attach a copy of the db

 

[slimjen1]

See attached. You may have to hold do the shift button to open.
Thanks for your help

 

[Alansidman]

I ran a query against your temp table and put Null in the criteria for the Receiving State to see what results. I then looked up some of the zips and found that for example 77523 was not in your zip table. 46385 was not in your zip table. Also some of the receiving and sending zips were not five characters long, but in some cases only 4 and in others they were nine (zip plus 4). So they were unable to match up. I think that your issue is data integrity. You may have to do some data clean up to get it to populate properly.

Alan

 

[slimjen1]

Thanks. I am aware of these. But can you see why some of the zipcodes for "28273" are appending for Charlotte, NC and other "28273" are not?
Thanks so much for helping me out.

 

[Beetle]

Maybe I'm missing something, but why are you redundantly storing City and State (or even the Zip Codes themselves) in the Shipment table? The data already exists in the Zip Code table. All you need to store is the ZipID, once for SenderZip and once for ReceiverZip (you would need to change these fields to Number data type). Then in a query you would join the Zip Code table to the Shipment table to return the correct City and State info for each row.

For data entry involving Shipments, you would use combo boxes on a form for both of the ZipID fields in the Shipments table. The combo boxes would return ZipID and ZipCode from the Zip Codes table.

 

[Alansidman]

Jen;
Sean has made an excellent point, however, as to the original question as to why they do not appear. I created a new query again against your tmp table. In criteria for sending zip code I put in 28273, for receiving state I put Is Null and for receiving zip code I put Is not Null. I then checked each of the returning records zip codes against your zip code file and none of the zip codes were present. So you either have faulty data (bad zip codes) or you zip code file is not up to date.

Alan

I just ran the numbers against a zip file that I have and they all appeared. I think that your zip code file is not up to date. The file is to big to attach even if zipped. Here is a link where you can grab it.

https://www.box.com/s/950cb8c90236c3f2b0a4

 

[slimjen1]

Sorry; a database I inherited and never changed. As soon as I have the time; I will definitly look at design changes. but for now; the users need this data. Thanks

 

[slimjen1]

Thks all. Alan; I will update the file now. But; again; the "28273" is in the zipcode list and it was updating some records and not others. I just found it a little wierd and frustrating tryiing to find out why it was doing this. As to the design; you guys are absolutly correct. I need to look at the design now. I scaled down the table to just the fields I need to be looked at for proprietary reasons; but in fact there are a few fields that are redundant and have to be updated like the zipcode. It's just to much to do right this minute because the users need the data. Thanks again

 

[Pat Hartman]

Beetle,
Zip codes are strings and cannot be correctly stored as numbers (edited). That would put New England out of business because the leading zero would be dropped

I have used zip code tables in several applications and the problem is that they are moving targets. Zip codes come and go and the files get outdated quickly unless you pay to subscribe to an update service. I find that they are useful to save typing (enter the zip rather than city and state) but that's it. I still store the city and state and allow not in list entries for the zip. That allows the data entry clerk to override the info coming from the zip code table if it conflicts with what the customer is saying is valid.

 

[Beetle]

Beetle,
Zip codes are strings and cannot be correctly stored as text. That would put New England out of business because the leading zero would be dropped

I think you meant to say that Zip Codes cannot be correctly stored as numbers, and I agree, but that's not what I was suggesting. The OP has a surrogate Autonumber primary key (ZipID) in his Zip Code table, so my suggestion was to store that value in FK fields in the shipment table. Those FK fields would need to be number in that scenario.

I'm not arguing the merits of Zip Code tables. I agree with you that they are not always 100% reliable, I was only making a suggestion based on the OP's particular circumstances. If a person is going to use City and State info from a Zip Code table to update close to 5000 records in another table for the purposes of a report or something (which appeared to be the case here), then they are in essence relying strictly on the Zip Code table for the data, so why run the update in the first place? You don't really gain anything, and, if there is outdated information in the Zip Code table, then you now have outdated information in two places. Better (IMHO) to just join the Zip Code table in a query in this circumstance (either on ZipID to numeric FK fields, or on the existing ZipCode text fields).

I still store the city and state and allow not in list entries for the zip. That allows the data entry clerk to override the info coming from the zip code table if it conflicts with what the customer is saying is valid.

I don't disagree with you here either, but this is something, I would think, that you are doing at the point of data entry, and the data (City/State) is being stored in the Customers or Vendors table, not a Shipments table, no?

 

[Pat Hartman]

I missed the part about the zip table having a surrogate key. That is something I would never do for the reasons given. This is one of those rare cases where you actually have a viable candidate key. Once you go the surrogate key route, you give up on allowing alternate entry and so the city, state, and zip fields get removed from all tables except this one.