multiple tables reffering to city, state, zip

[balfoura]

i'm about to make a change to my database, but before i do i wanted to make sure i have my facts straight. i hope i can explain this well and it makes sense.

when i first designed my database i made the mistake of putting all my address information into fields within other tables. so for example, Table A and Table B had fields for city, state, and country. so the city of New York could be contained multiple times within the two tables and each was a separate listing that could contain a typo.

now i'm going to fix this, as my database will be used in a print project. my question is, if Table A and Table B aren't directly related will I need to create two different tables to contain city names, etc.? it would be best to have just one table, but will this associate every Table A record relating to New York to every Table B record relating to New York? is there an generally accepted way to deal with this situation?

i hope i explained that well enough and you can tell what my question is. any help is greatly appreciated!

 

[MSAccessRookie]

i'm about to make a change to my database, but before i do i wanted to make sure i have my facts straight. i hope i can explain this well and it makes sense.

when i first designed my database i made the mistake of putting all my address information into fields within other tables. so for example, Table A and Table B had fields for city, state, and country. so the city of New York could be contained multiple times within the two tables and each was a separate listing that could contain a typo.

now i'm going to fix this, as my database will be used in a print project. my question is, if Table A and Table B aren't directly related will I need to create two different tables to contain city names, etc.? it would be best to have just one table, but will this associate every Table A record relating to New York to every Table B record relating to New York? is there an generally accepted way to deal with this situation?

i hope i explained that well enough and you can tell what my question is. any help is greatly appreciated!

It sounds like you are experiencing the pains of having to redesign tables in a database after it has been implemented for a period of time. I assure you that you are not alone, and that many of us have been in the same situation at one time or another. I suggest the following steps.

1. Create a single new City Names Table (tblCityNames?) that contains all of the appropriate columns, and one New Column to store an Updated ID .
2. Add all City related information from TableA. The default value for the Updated ID Column should be the Primary Key for the Row.
3. Add All City related information from TableB. The default value for the Updated ID Column should be the Primary Key for the Row.
4. MANUALLY eliminate duplicates, using the Updated ID Column to store the corrected value for any incorrect entries.

After you have created and updated the new table, you will need to go back and modify TableA and TableB.

1. Add a Column for the CityID
2. Choose the Updated ID Column from tblCityNames where the City Name Columns match. This will apply any error correction that you identified in step 4 above.

Once the Tables are updated, any Queries, Forms, Reports, Macros, or VBA Code that use TableA or TableB will also need to be modified. After you have all of them working, you probably want to go back and remove the obsoleted Columns from TableA and TableB.

If you have not noticed, it can be a lot of work. In hte end, however, it will prove to be worth it, and the system will be much more maintainable for future growth.

 

[jzwp22]

I would use the following structure. This will allow you to take care of City names that occur in multiple states: Dayton TN; Dayton, OH; Dayton, KY; Dayton, MN.

tblStates
-pkStateID primary key, autonumber
-txtStateName

tblCities
-pkCityID primary key, autonumber
-txtCityName

tblCityState
-pkCityStateID primary key, autonumber
-fkStateID foreign key to tblStates
-fkCityID foreign key to tblCities


Now in your A and B tables, just reference pkCityStateID as a foreign key; for example:

tblA
-yourprimarykey
-txtName
-txtStreetAddr
-fkCityStateID foreign key to tblCityState


Out of curiosity, if tables A and B both contain similar information, can't you combine them into 1 table?

 

[balfoura]

thanks for the suggestions so far!

unfortunately i can't combine tables A and B. while they both contain addresses, they are aren't directly related. my database deals with physicians and one table contains their current practice while the other contains locations where they trained. so, for example, a physician who currently has a practice in New York may not have ever trained there. because of this I want to make sure there are no unintentional relationships if i use one table for city names. would this be a problem if i use the above methods?

thanks again!

 

[jzwp22]

In the structure I proposed I don't think that there would be an issue.

Frankly, I don't see a problem putting all of the addresses in 1 table since a physician can have multiple associated addresses. You can add a field to distinguish the address whether it is a practice address or a training address. I would just add security around the addresses on forms and such to limit what your users can see/edit.

 

[balfoura]

thanks to you both for the help! i'll be working on creating my new tables from your suggestions. thanks again for the help and the sympathy

 

[jzwp22]

Glad we could help; good luck with your project.

 

[FeederPig]

My question is similar to balfoura and I think your thread answers it, but I have to ask. I am still in the design phase, so I have yet to enter data to experiment.

We provide inspection services to multiple customers at multiple sites. Multiple customers use the same site and multiple sites are used by the same customer. I have a Customer table and a Site table and use a CustomerSite table to join them. I need point-of-contact and address information for both my customers and the inspection sites.

Originally I had two large tables - one each for customer and site - that contained both point-of-contact and address information. I want to eliminate duplication and minimize empty cells by creating Contact and Address tables, eliminating this information from the Customer and Site tables.

Based on what I read in your string, this is the idea I came up with.

Customer Table
pkCustomerID
CustomerName
ContactID
AddressID

Site Table
pkSiteID
SiteName
ContactID
AddressID

CustomerSite Table
CustomerID
SiteID

Contact Table
pkContactID
-columns with contact information-

Address Table
pkAddressID
-columns with address information-

Does this seem workable? I have other subordinate tables that use CustomerID or SiteID as a secondary key.

 

[jzwp22]

Your CustomerSite table is fine except I would still add an autonumber primary key field. Also, if your table name is "CustomerSite Table", I recommend you get rid of the space. It is generally not recommended to have spaces or special characters in your table and field names. I usually use a tbl prefix

tblCustomerSite
-pkCustSiteID primary key, autonumber (pk denotes primary key)
-CustomerID
-SiteID

Now, with respect to the addresses and contacts, the way you have it set up, a company can have only 1 address and a site can have only 1 address. Additionally, a customer can have only 1 contact & likewise for a site. Is this consistent with your application? If not, you would have to approach it with a junction table just like you did with customer and sites.

 

[FeederPig]

Thank you for the reassuring feedback

I am working my way through the Access 2997 Insideout book as I try to learn Access. Their join table example has no primary key. That said, they also recommend always having a primary key so adding one makes sense. I use the tbl convention you describe and my table names are without spaces but thanks for the comment. Yes, each customer and site has only one address and one contact.

I have a detailed understanding of how my organization works and its report requirements. Translating that into Access tables is a learning experience. Again, thanks for the reassuring feedback. I feel like I am actually figuring this out.

 

[jzwp22]

You're welcome. Post back if you have additional questions & good luck on your project.