Normalizing tables

[lpgagirl]

I am trying to normalize my DB. I currently have split a main table into 3 separate tables (Main, Personal info, Preparedness) I will likely be splitting the third table even further. I have linked the 3 tables via a primary key (ContactID - autonumber). When I have the form open it is based on a main query which sorts contacts names in ascending order. Do I need to add the other two tables/and fields to the query in order to get all 3 tables info. showing on the form? Or do I need to change the control source statement in the fields to get the info. I thought putting =[personal info]![home address] as the control source for example that would retrieve the info from the personal info table.

Thanks


Jeannie

http://thefloyds.50megs.com/index.html

 

[Edski]

Hi lpgagirl,
You have to add the other 2 tables to your query (and include the fields you want to show on the form). Putting '=[personal info]![home address]' as the control source will not work. Note that if you just wanted fields from the first table, then you could have simply based the form on the table, and not the query, since you can sort in the form anyway. What is your reason for splitting the tables?
Also, it doesn't sound like what you are doing is Normalization.
Cheers.

 

[lpgagirl]

Edski,

Initially, I had one very large table with about 50 fields to accomodate my form. The form is used to search/add/edit employee information. I am required to have all the info. on one form. It was suggested that I normalize my tables (split it up into like tables). My form has three employee info sections Contact, personal and training. So I decided to split my table up into these three areas. Now I am trying to link them and get them to return correct records.

Should I just use one table again? If so, can I copy one table into another?

Frustrated.....

Jeannie

http://thefloyds.50megs.com/index.html

 

[Edski]

lpgagirl,
Fifty fields isn't a lot. You can keep the separate tables, link them through a query and then base the form on the query if you prefer. If the tables are linked one-one then there's nothing to stop you from putting it all into one big table.

Normalization isn't the process of splitting big tables into smaller ones, unless the smaller ones represent information about totally different entities. Normalization is the method of structuring your database so that redundancy is minimized and data quality is maximized. A database with only one table won't have to worry about normalization (as long as the information in the table all relates to one single entity). For example, in your table, if 'training' is just a yes/no field then there's no problem. However, if you record training information for an employee that has had more than one training course, then you will need to normalize.

Hope that helps.

 

[BNPMike]

A good practice is always to base your forms and reports on queries. Never on tables.

Queries in Access are really what are called 'views' in other relational databases. There are many good reasons to use views/queries but one is it allows you to get the view you want of the data. What you seem to be doing is splitting your tables in order to see the data in a convenient fashion. Split your tables according to the rules of normalisation. Then split your views of that data using queries. Queries can give you slices of a table, or join many tables together, and can re-format the data eg join surname and firstname into a single name field.

At a guess Personal and Contact should be in one table and training in another. This would imply a third table - Courses/Qualifications which stores details of the types of training people can have.

 

[Edski]

Sorry Mike but I have to slightly disagree with you on your first line.
Never say never
I don't see a problem with using tables as the source for a form or report. I agree that queries are more flexible, but if your table is simple or well designed, then why bother with a query?
Cheers.

 

[BNPMike]

Edski

The technical argument for always using a view is that it allows you to change the underlying tables. The relational model is very good at allowing the data model to be developed over time either because the requirement changes or because the designer didn't get it right first time.

A view isolates you from such changes. If you have a form that now runs off a table and you decide to split that table into two, and change some fieldnames, with a view you only need to amend the view once to allow all the affected programs to run as before. Writing programs directly against tables means any table changes have to be propogated to potentially each individual progam that accesses the table.

 

[MinnKota]

I once had a database that had a form that was based off of a query of two tables (one to many relationship) Everything was working well until I tried to have the form update some data through Visual Basic. Then I got update errors galore and no matter what I did, I couldn't do what I wanted so I just forced everything into one table and scraped the query.

I realize that queries are more flexible, but they also can result in major headaches.

 

[lpgagirl]

Thanks for your input. I have certainly been learning alot in the last little while.

Jeannie

http://thefloyds.50megs.com/index.html