Topics Search

City State and Zip Tables

City State and Zip Tables
Views: 4
I'm toying with the idea of starting a new project, so I'm in brainstorming mode for table design. I'll be recording customer information in this application. Typical stuff: First and Last Names, Company, Street, Apt, City State and Zip, Phone numbers(s) and extensions, E-mail.

How do you guys recommend setting up the tables for City State and Zip. I was thinking that I would have
StateAbbr (Limited to 2 letters)

FKStateID (Lookup to TBL__State)

FKCityID (Lookup to TBL__City

My customer information then would record only the zip code (PKZipID). And I could then use queries for the state, city, and zip information for forms, reports, etc.

Or is this beyond overkill
Sponsored Links:

More topics


City, State, Zip

I have a table that has City, State and Zip Code for most areas of the U.S. The zip code field selects Zip, city, and state, in that order.

For the City, it looks to zip.column(1) and state looks to zip.column(2). After updating the Zip Code field, the City updates automatically, but the State does not.

Selecting the State Code

So I have a bunch of address that have:

Street Address City St Zip

Nothing is standardized accept the order. There could be a comma between city and state and there may or may not be a space between the state and zip. The zip also varies in size (#####, #####-####, or #########). Sometimes the zip is not there at all. I am looking for a way to identify the State or at least the position that is starts at. Any help would be awesome

Combine fields into one

I have a form for taking orders and would like to combine [Customer Name], [Address], [City], [State], and [Zip Code] into one field so it can copy the complete name/address and then paste it into another application. The final combo field would have to be similar to how a normal address would be printed.

Customer Name
City, State. Zip Code.

The name and address each on their own lines and city, state and zip code on one line with a coma after city

Filtering a combo box based on data in previous field

I have 2 tables

customers & cities

In the cities table I have city, state & zip fields

I want to build a customers form that uses data from the cities table.

Here is what I want to be able to do.

Enter a city in 1st combobox (lets say springfield), tab to the state combo box and only have those states with a springfield listed (Mo, MA, IL, VA, OH), then tab to the 3rd box and in ithave the available zip codes for my city and state combo (lets say springfield ma) with 01101,01102,01103

Zipcode Lookup Autofill

I know similar questions has been asked before in the form but I cannot find a really good solution.

I worked with FileMaker a lot more from my previous job. My current job they use MS Access. I am trying to get familiar with Access but I am having troubles. In FM I was able to type a zip code and it would automatically populate the City and State. I have a data base with all of the city and states in access. How can I do the same thing in access like I was able to do in FM?

When I type the zip the city and state are automatically populated? Please I am VERY new at this and willing to learn

Combine City State, Zip into one display field

I have a field on my form that I need to combine my [city] [state] and [zip] fields into one field, BUT ALSO make that field editable. I already know how to simply combine those fields using =[city]&" "&[State]&", "&[Zip] in the control source, but although that displays everything in the correct format, I need to be able to edit it also.

So for example if the field were to display "New York NY, 10011" and I wanted to change it to "Manhattan NY, 10012" I could just type it into the field and it would update the apropriate fields.

Split field into 3

I have an address field that contains city, state and zip. I'm looking for a way to split this into 3 fields(city, state and zip).

RAHWAY , NJ 07065

It's probably easy.but I can't seem to come up with anything that will work.

Error 2950 when running setvalue macro

I have a form called 'CompanyInfo' . It contains a zip code, city, and state field. I have a seperate zip code table. On entry to the city field, I am running an embedded macro that sets the value of the city field to this:
DLookUp("[City]","[Zip Codes]","[Zip Code] =" & [Forms]![CompanyInfo]![Zip Code])

When I use the form and it enters the city field, I keep getting an error 2950. The database is trusted so that should not be the problem.

Binding a Combo Box Search Result

I have admitted in threads in the past, that I am a rookie when it comes to access. But through all of your help, I have learned a great deal.

However, I have gotten myself stuck again.

I have a table with all the Zip codes and the City and States they belong to.

In my form, I have an unbound combo box search that finds the zip code and then in my Text Box, I have a formula in the Control Source that says =[Combo632].[column](2) It will show the city or state based on which column it is in.

My issue. I dont know how to bind those results. So that when I save the record, it saves the correct city and state in the form.

List style combo box issue

I'm new to Access and need some help with a simple task which is giving me serious trouble. I have built a table called customer details and it contains a field called city, and another field for state.

I want a user to be able to select a state from the stat combo box that I've created and have all the cities in the selected state appear in the city combo box.
At the moment I created 2 tables (one for city and one for state) and I have every city and state packed into those tables, so when I click the city combo box, all the cities in the countryare listed, but I want only the cities in the selected state to appear instead.