Table question

[P-C-Surgeon]

I have a database with several tables. In table one, I have a column with customer names. The column next to it has their pet names. Since some have multiple pets, their name is listed more than once. Table # one is completely filled out with all of my customers and pets. In table two, I have columns for customers, pets names, addresses, and telephone numbers. Table two is completely filled out except for the pet name column. Is there a way to link table one to table two based on the customer name column so that the pet name column in table two fills in automatically? By the way, table one lists the customer name multiple times based on the number of pets they own while table two only lists the customer name once. Any suggestions would be greatly appreciated. Thanks for your help

 

[MyBoo]

This is a perfect example of where table normalization was not done and the problems that arise because of it.

First, you should not store customer names and pets in the same table. They are two distinct entities therefore they should be store in seperate tables.

Redo your tables.. Ccreate a customer table... containing customer information. Then create a second table that would contain pet information. Create a one-to-many relationship between these two tables and your results will be that you have one customer name with one or more pets. Done correctly, in Access, you would then be able to open the customer table, click on the + (plus) sign that displays to the left of each row and it will display the pets associated with that customer.

Taken a step further... you should create another table that that links the services provided for each pet... this would be a one-to-many between the pets table and the services table.

 

[P-C-Surgeon]

One to many table relationship

I do like your suggestion regarding the one to many table relationship. I tried looking up some of the help files regarding this and was a little confused. I have used access for some time but have never used this feature. Is there an easy way to configure the one to many table relationship, and should each table have their own unique primary key?

 

[Andrew Fergus]

This page should help with your design normalisation :
http://www.datamodel.org/NormalizationRules.html

Andrew

 

[MyBoo]

Re: One to many table relationship

I do like your suggestion regarding the one to many table relationship. I tried looking up some of the help files regarding this and was a little confused. I have used access for some time but have never used this feature. Is there an easy way to configure the one to many table relationship, and should each table have their own unique primary key?

The only way to configure the relationships in Access is via Tools/Relationships or via VBA, which we won't get into here.

It does not hurt to provide primary keys for every table but, it's not always needed. You should review what you intend to enter into these tables first and decide if any one or more fields in a table can be used as the primary key. If yes, then that's the way to go. Starting out, you probably don't want to use more than two fields as a primary key. If no, create a RecID field... name it what you want.

If you link primary key to primary key, you create a one-to-one. If you link a primary key to a foreign key (non-primary key), then you create a one-to-many relationship. This is a basic explanation... but enough so that you can set your relationships correctly.

If you have further questions... provide some detail on the data you are storing and the responses may become more specific to your situation.

 

[P-C-Surgeon]

These suggestions have been very helpful and it is helping me to understand the structure of tables. However, I still have some questions that may be answered based on my example. I have a table that lists schools in Los Angeles which all have different names and a different 4 digit identification code (e.g. 8124). Shouldn't these be in the same table with the code as the primary key? Also, if a school manager manages 5-10 schools, instead of adding another column field in the first table for managers, I'm assuming that these should be in another table with its own primary key. (I have always kept these in the same table and listed the manager name multiple times depending on the school he manages [redundant information]) If I want to view the data in these two tables together, table one-school & ID code and table two-manager, would it be in a seperate table, form, and/or query and is there an easy way to explain? I apoligize for the confusion on something as simple as the structure of tables, but after using Access tables and forms for some time, I am finding out that I may have to learn tables all over again....Thanks in advance for any help with this and thanks for the help already submitted.

 

[Norie]

For the schools/managers scenario what you should have is a foreign key in the school table that holds the primary key from the manager table for the manager who manages that school.

If it's possible to have more than one manager for a school then what you would need to do is create a third table.

This third table would contain 2 foreign keys, one for the manager and one for the school.

By the way some people consider the best way to create a primary key is to use an AutoNumber field rather than actual data.

That might be useful if say the code or name of a school changed.

 

[MyBoo]

I have a table that lists schools in Los Angeles which all have different names and a different 4 digit identification code (e.g. 8124). Shouldn't these be in the same table with the code as the primary key? Also, if a school manager manages 5-10 schools, instead of adding another column field in the first table for managers, I'm assuming that these should be in another table with its own primary key. (I have always kept these in the same table and listed the manager name multiple times depending on the school he manages [redundant information]) If I want to view the data in these two tables together, table one-school & ID code and table two-manager, would it be in a seperate table, form, and/or query and is there an easy way to explain?

In order to "properly" address your table layout, you should list all fields you want to store. I know this is not always feasible, but maybe you could do this offline. Setting up your tables correctly, in the beginning, is very important.

From what you provide:
Schools Table - IdentificationCode, SchoolName, MgrCode
Manager Table - RecordID, ManagerName

IdentificationCode is pkey in Schools table
RecordID is is pkey in Manager table
Make MgrCode and RecordID the same type of field.

Open Tools/Relationships, display both tables. Link MgrCode in Manager table to MgrCode in School table. This will create a one-to-many relationship. You know have a table that contains one manager name each and another that contains one school each. The link is that you enter a MgrCode in both to tie the manager to the school. Do NOT set Cascade Deletes in the relationship. If you do and then you delete a Manager, the School would get deleted and you don't want that.

Once you enter the MrgCode into more than one school, open the Manager table... you should see a + (plus sign) to the left of each record. Click on the + and you should see what schools that Manager manages.

Enjoy!