Normalization

[danbl]

I have a data base with four tables that need to be normalized. I am new at this and am looking for help!!!

There is no primary keys assigned in any of the tables and there are three fields common to all tables (Date, Date of Birth and Physician). Each table stores specific data and then through queries generates reports. All was working okay until I tried to come up with one data entry and one data edit form instead of four.

Help please

 

[boblarson]

Post your entire table structure as you currently have it. The easiest way might be to post a copy of the database with just the tables.

 

[danbl]

Hi Bob,

Thanks for the reply. Here it is.

Dan

 

[boblarson]

I have a data base with four tables that need to be normalized.

Actually, your entire structure needs to be normalized. Not just 4 tables. I'll be analyzing this and anyone else who wants to chime in, feel free. The challenge is going to be getting some of the repeating fields into the proper structure so you add ROWS for those instead of having the repeating columns (i.e. VTE Prophylaxsis Used, VTE Prophylaxsis Used_2, VTE Prophylaxsis Used_3, etc.)

 

[danbl]

The repeating columms are because there are a number of types that can be used during one senario.

I have figured that there needs to be unique identifiers in the tables but not sure how this figure into the relatinoships and how that will affect the data already entered.

Unfortunately I am one of those who learns better by seeing rather than just reading about it.

 

[boblarson]

So, I've worked on medical type databases before but what are these abbreviated terms:

VTE
PQ
PNA
DVT
PAT at AMAASS
NA_1 (not applicable???)
NA_2 (not applicable???)


And what is

DOB3
Date3
Physician3
in the t_DVT_Data table?

DOB4
Date4
Physician4 in the t_Temperature_Monitoring table?

Why do you not have a patient table, which has the DOB information? Is this supposed to be anonymous data?

 

[danbl]

Bob,

The data in the tables I sent is ficticious.

VTE = venious thromboembolis
PQ = refers to questions in the perioperative table
PNA = refers to Not Applicable for questions in the perioperative table

DVT = Deep Vein Thrombosis
PAT at AMAASS = Pre Admission testing a facility
NA_1 (not applicable???) correct
NA_2 (not applicable???) correct

DOB3 = Date of Birth in the third table
Date3 = Date of service in the third table
Physician3 = Physician in the third table in the t_DVT_Data table?

DOB4 (Same as above but for the fouth table.
Date4
Physician4 in the t_Temperature_Monitoring table?

This initally started as a one table statistical db but has evolved to this. I now realize this could and should have been done differenty.

Bing a novice at this, I now see the error I made.

 

[boblarson]

So is this data coming from another data source and you are using this for reporting purposes or is it going to be entered by hand by users?

 

[danbl]

Teh data is being entered by a single user and is used for reporting purposes.

 

[Simon_MT]

I have posted an answer in your other thread.

Simon

 

[boblarson]

I have posted an answer in your other thread.

Simon

Looks like this thread probably shouldn't have been. And perhaps the database doesn't need to be normalized to 3NF but it should be done to some extent as it currently won't provide an easy way for a lot of reporting. I'm going to step back a bit though and let the other thread continue on. I may lock this thread in a bit.

 

[danbl]

Bob,

I have done some of the reading Simon suggested and will be making some changes. I would appreciate your review and comment.

What is the best way to get this to you in a day or so, if this is not the right place for this post??

Dan

Dan

 

[vbaInet]

Bob,

I have done some of the reading Simon suggested and will be making some changes. I would appreciate your review and comment.

What is the best way to get this to you in a day or so, if this is not the right place for this post??

Dan

Dan

You could point Bob to the thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=202862

 

[danbl]

I have read the references to normalization as well as the use of primary keys and foreign keys.

Using the zip file I posted, I created a primary key in table 1 and then made it a foreign key in table 2 to see what happened. When I open table 2 there is nothing there in the foreign key field. I tried making in an auto number field as well but am not allowed to do this.

Why do I not see anything in table 2?
If I need to auto number the foreign field, why is Access not allowing me to do this?
Is there a basic example to look at and the associated relationship structure?

Dan