another simple question from a beginner

[Bobby Neal]

I'm back again with another simple question. I am having a horrible time trying to locate material on simple procedures that I can't figure out how to do. I try to search this forum, but I must be "search word" illiterate, because my searches pull up everything but what I am looking for. Any tips? Here is my current problem. I have a table which contain 3 fields (among others) for city, st and zip. In field rules under city, to speed up entry, I set up a lookup table using the current table as the lookup table to fill in the city, st and zip fields. It works fine except that there are lots of entries from each city and there is no place to mark "unique records only". I know I can use the "previous values", but I would have to select from 3 different lookup tables to get city, st and zip. Is there a filter expression to prevent duplicate records being shown in the lookup box? Or am I going about this the wrong way?

Thanks,
Bobby

[Ohlen Cartmell]

RE: another simple question from a beginner

FWIW here's what I would do:

I would build a separate table containing only the zip code, city, and state. In this table I would place every combination of city, state and zip I would ever use in the main table. This table would be used only as a lookup table. Then in the main table I would lookup the zip code in the lookup table and have that lookup fill in the city and state. You can even set alpha to let the user fill in new combinations in the lookup table as they occur.

[jwolfe11]

RE: another simple question from a beginner

I would create an index on the database and limit the index to unique only. Use this index to link for the lookup. It should limit the records to unique only.

[Geoffrey Hollander]

RE: another simple question from a beginner

Bobby,

Ohlen is right; you should set up a ZIP, City, State table just for a general purpose lookup. Keep in mind that this kind of data has certain characteristics to which you will need to pay some attention.

First, it's in hierarchical order: cities within states, ZIPs within cities, so you can have several cities in each state but only one state per city, etc. As a result, if you want to be sure that your table is dupe-free, you will want to check on the ZIP field only.

Now it may be that in your table you have duplicate ZIPs with different, yet correct, cities. In that case you have two choices. One is to pick the most "accurate" city-ZIP combo and trash the others; or correct the table by adding a vanity city field(s)(the USPS allows up to two) and add the different cities to the same ZIP's record. You can later choose whether or not you want to use the USPS preferred city (the most "accurate") or the vanity city when you either use the table as a lookup or post from it when correcting a list accross the board.

So what's a vanity city? Santa Barbara is a good example. If you live in the northern end of Santa Barbara you live in Goleta. If you live at the southern end, you might well be living in Montecito. The USPS prefers that addresses in these areas use Santa Barbara (Goleta may now be separate), but beacause they are all legitimate names for their respective areas, all are legal.

Montecito is a very "prestigious" high-end area; the kind that if you live there, you want everyone to know about it, hence the term "vanity" address or city. Some customers can get powerful offended if you were to address your mail piece to them using the more generic USPS preferred city rather than where they wish to be known as living. It's also true for many businesses that want a prestigious address on their letterhead (like me!).

If all you want is to put the right city and state with the right ZIP, the first alternative would probably be best.
Incidentally, the USPS will sell you a table of all the current US ZIPs with their appropriate cities, vanity cities and states. It might make things easier and faster.

Hope that his was of some help...

Regards,

Geoffrey Hollander

[martinwcole]

RE: another simple question from a beginner

why not just use the zip code file that comes on the alpha disk - that's all i have ever used. you can delete the zips for states that you don't need. And use field rules to fill in city-state-county

[Geoffrey Hollander]

RE: another simple question from a beginner

Martin,

That's a great idea, especially since the USPS version runs about $300 (for a 1-year quarterly subscription).

Whether or not one decides to use Alpha's supplied ZIP table or the USPS's depends on how current and accurate your database needs to be. If you want to regularly mail to these addresses, you might want the USPS version (and update your list {table] whenever a new one is issued) in as much as ZIPs are always changing.

If you are an occasional mailer, you mailing house or letter shop will probably CASS code the file before presorting and that will correct most of those obsolete ZIPs.

I know I'm picking nits, but I've delt with many clients who didn't think that this was particularly imoprtant, only to find it expensive and frustrating to correct later on.

Regards,

Geoff Hollander

[Bobby Neal]

RE: another simple question from a beginner

Thanks for your time guys.

Bobby

[russ Boehle]

RE: another simple question from a beginner

There are zip code dbf's available on the internet. Do a search. I got one about a year ago that I think is more up to date than the one with alpha. Not sure where I got it but it also has county info which is useful if you do anything by county. (we use it for sales territories). We do it basically as stated above. Set prompt order so after the name you go to zip code, the lookup will fill in the city and state. We still allow the cursor to go through the city and state fields so if there is some variation, which occasionally occurs, it can be changed manually. Or you can put skips on the city and state fields so once they are filled in you skip them to save time.

Russ