There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Access 2007: "I want to be normal." or, help me understand and make a table/form normal
Hello, I am new to database design and some of the experts here have said that my forms should follow standard normalization procedures to avoid future problems. I have read through a few online sources but I'm still not 100% sure how I should go about doing this in the form that I want to attach.
First normal form standard says that I should not have repeating groups, but I have many. This is because I am trying to capture all of the information in academic articles (Such as http://www.plosone.org/article/info%3Adoi%2F10.1371%2Fjournal.pone.0014550). If I were trying to capture the patient and demographic data in table 1 from this article I would need to have four groups for race. So I thought it would be good to have in my table Race1, Race2, Race2, Race4 etc... and then Race1Per for the percent of the population etc. However, this seems to go against normalization practices. By 1NF standard I should have a table something like: [Call Number] [Race] [RacePer] and then just repeat the entry each time for the race options. How could I accomplish this with a form? I'm at a loss right now for how best to construct these forms/tables and any help would be greatly appreciated.
Thank you,
Bevo
EE-ABS-Example.accdb
First normal form standard says that I should not have repeating groups, but I have many. This is because I am trying to capture all of the information in academic articles (Such as http://www.plosone.org/article/info%3Adoi%2F10.1371%2Fjournal.pone.0014550). If I were trying to capture the patient and demographic data in table 1 from this article I would need to have four groups for race. So I thought it would be good to have in my table Race1, Race2, Race2, Race4 etc... and then Race1Per for the percent of the population etc. However, this seems to go against normalization practices. By 1NF standard I should have a table something like: [Call Number] [Race] [RacePer] and then just repeat the entry each time for the race options. How could I accomplish this with a form? I'm at a loss right now for how best to construct these forms/tables and any help would be greatly appreciated.
Thank you,
Bevo
EE-ABS-Example.accdb
Who is Participating?
see also this demo about creating a third table to break a many to many relationship between two tables
http://office.microsoft.com/en-us/access-help/demo-set-the-relationship-between-two-tables-HA010254901.aspx?pid=CH100739911033
there are also lots of information on that link regarding table normalization
http://office.microsoft.com/en-us/access-help/demo-set-the-relationship-between-two-tables-HA010254901.aspx?pid=CH100739911033
there are also lots of information on that link regarding table normalization
Hi Capricorn1 and Jim, thanks for the great resources. I think I've normalized each table now, but I am running into many problems with the forms now (maybe because of the database relationships?). Previously, I had everything on this form in one table with the exception of some fields which were looked up to populate combo/list boxes. Now, I am getting an error when I open the form (it is asking me for fields in these separate, related tables) and showing me a header only. I'm kind of stumped on how to add all these new, separate table sources into the form.
Here is now I envision the form working in its new, normalized fashion (please tell me if this is possible or if I am barking up the wrong tree here).
1.) Users will enter general study characteristics in table tblPtCharacteristics which has the primary key, [Call Number]
2.) Users will define groups for this record which are stored in tblGroup (there can be one or many 'groups' per record)
3.) Users will describe the characteristics of the group defined in item 2. (tblRaceDesc, tblStageCatDesc, tblMale, tblHistologySiteDesc, tblDiseaseDesc) which are in their own tables now and related to the [Call Number] in 1 and also have a field for the group that they are describing
(I am not sure how I do this, the linking to both call number and group defined in 2. I tried to put [Group] in each of the tables mentioned above, but I am not sure if I am doing this correctly.)
4.) I want users to be able to click a button on the record to add a new group, or any of the other table categories
5.) Create some kind of summary for the [Call Number] record by [Group] so that the user can see quickly if he or she has entered everything necessary.
Any help would be excellent. I think I understand some of this stuff, but it is hard for me to understand everything from the examples shown above.
Thanks again,
Bevo
(Also, why is Access creating strange items in my 'fields list' such as Disease_tblDiseaseDesc)
EE-ABS-Example.accdb
Here is now I envision the form working in its new, normalized fashion (please tell me if this is possible or if I am barking up the wrong tree here).
1.) Users will enter general study characteristics in table tblPtCharacteristics which has the primary key, [Call Number]
2.) Users will define groups for this record which are stored in tblGroup (there can be one or many 'groups' per record)
3.) Users will describe the characteristics of the group defined in item 2. (tblRaceDesc, tblStageCatDesc, tblMale, tblHistologySiteDesc, tblDiseaseDesc) which are in their own tables now and related to the [Call Number] in 1 and also have a field for the group that they are describing
(I am not sure how I do this, the linking to both call number and group defined in 2. I tried to put [Group] in each of the tables mentioned above, but I am not sure if I am doing this correctly.)
4.) I want users to be able to click a button on the record to add a new group, or any of the other table categories
5.) Create some kind of summary for the [Call Number] record by [Group] so that the user can see quickly if he or she has entered everything necessary.
Any help would be excellent. I think I understand some of this stuff, but it is hard for me to understand everything from the examples shown above.
Thanks again,
Bevo
(Also, why is Access creating strange items in my 'fields list' such as Disease_tblDiseaseDesc)
EE-ABS-Example.accdb
First....
Forget even thinking about forms until you are certain that the tables are properly normalized and related.
You have 19 tables, yet only 7 of them are displayed as being related in the relationship window.
You also have a great many of your tables do not have primary keys, thus preventing you from establishing "Referential Integrity"..
So perhaps we should go back even further...
Jim has posted a great deal of useful information in his post above.
But remember no Expert here can really provide any *Specific* advice on designing a database that they are not intimately familiar with.
Your follow up post now lists 5 *concerns*.
So it is difficult to see what the "One" question is here, and what the ultimate "Solution" here needs to be.
So like Jim, I applaud you for wanting to do things correctly as a first step.
So I think your first basic question should be:
"This is what I want to keep track of, what tables/fields do I need."
Then when you get this question resolved, post another question, something like this perhaps:
"Here is my design, how do I relate the ___ Table to the ___ Table to make sure that ____."
Then once this is resolved move on to the next set of tables.
Each question should be followed up by you posting your results and clarifying each solution.
In this way each question is resolved and serves as a stepping stone (Building block) for the question.
Makes since
;-)
From what you posted, it is difficult to determine if all your tables are designed properly because we don't know the specifics of your requirements or design parameters.
In other word, simply joining tables that are 3NF does not automatically make your design valid.
Jim and or/cap may be able to go into the specifics of this a bit deeper...
It just seem to me that the question is too broad to be covered in one specific "Question" here.
And as such, should perhaps be better answered as multiple, sequential questions.
JeffCoachman
Forget even thinking about forms until you are certain that the tables are properly normalized and related.
You have 19 tables, yet only 7 of them are displayed as being related in the relationship window.
You also have a great many of your tables do not have primary keys, thus preventing you from establishing "Referential Integrity"..
So perhaps we should go back even further...
Jim has posted a great deal of useful information in his post above.
But remember no Expert here can really provide any *Specific* advice on designing a database that they are not intimately familiar with.
Your follow up post now lists 5 *concerns*.
So it is difficult to see what the "One" question is here, and what the ultimate "Solution" here needs to be.
So like Jim, I applaud you for wanting to do things correctly as a first step.
So I think your first basic question should be:
"This is what I want to keep track of, what tables/fields do I need."
Then when you get this question resolved, post another question, something like this perhaps:
"Here is my design, how do I relate the ___ Table to the ___ Table to make sure that ____."
Then once this is resolved move on to the next set of tables.
Each question should be followed up by you posting your results and clarifying each solution.
In this way each question is resolved and serves as a stepping stone (Building block) for the question.
Makes since
;-)
From what you posted, it is difficult to determine if all your tables are designed properly because we don't know the specifics of your requirements or design parameters.
In other word, simply joining tables that are 3NF does not automatically make your design valid.
Jim and or/cap may be able to go into the specifics of this a bit deeper...
It just seem to me that the question is too broad to be covered in one specific "Question" here.
And as such, should perhaps be better answered as multiple, sequential questions.
JeffCoachman
Thanks for the great advice everyone. I will be closing this question and posting smaller, more specific questions like Jeff has suggested. I appreciate everyone's advice and their patience with me as I try to learn this topic.
Thanks again,
Bevo
Thanks again,
Bevo
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month4 days, 1 hour left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
First, let me congratulate you on wanting to take this important first step with your DB, which is starting out with a proper design. Many do not bother to do so and as a result, end up with a lot of problems and a poorly performing application that is difficult to maintain and use.
First thing is that you want to have a table for each "thing" in your database; ie. Patents, Races, Languages, etc.
For each of those "things", you want all the data associated with it to be in that table so that data will be stored only once.
So for patients, I might have a table like this:
PatientID - Autonumber - Primary Key
LastName - Text
FirstName - Text
DateOfBirth - Date/Time
Each of those fields should be indivisible or in other words, should contain only one value. For example, you do not want this:
"English, French, and German"
as a single field for "languages" in a patient record. That's first normal form.
Second normal form is that every field in a table depends on the primary key. In other words, it belongs to that "thing".
Here's an example. Lets say I wanted to record events that these people might come to. You might do a table like this:
tblEvents
EventName
AttendeesLastName
AttendeesFirstName
and the data might look like this:
Cancer Answers Dettman Jim
Cancer Answers Smith John
Cancer Answers Doe Jon
Cancer Answers Doe Jane
Cancer 101 Dettman Jim
Cancer 101 Doe Jon
Note that the event name repeats for each particpient. Also note that the participients names repeat every time they attended an event. That's not what we want as those fields belong to other "things". The fields last and first name belong to a "patient", not to an "event".
What we really want is to record the fact that they attended an event. By doing the above (storing the name in multiple places) if we needed to update their name, we would be forced to go through every record in that table, plus update thier patient record.
So instead we would do this:
tblEvent
EventID - Autonumber - Primary Key
EventName - Text
EventDate - Date/Time
MaxNoOfAttendiesAllowed - Long
tblEventAttendies
EventID - Long - Lookup to tblEvents
PatienID - Long - Lookup to tblPatients
tblEvent is the list of events:
EventID EventName
1 Cancer Answers
2 Cancer 101
and tblEventAttendies is a list of who came. However it doesn't store a name, but a pointer to the "patient" (which is called a foreign key), which has all the information on the patient.
When we create a form or report then, we use a query or view, to join the tables based on those pointers and have access to the data even though it is being stored only once.
The other part to this is repeating groups. Above, I said that I spoke three languages. Because of 1NF, I would need to store each seperately:
"English" "French" "German"
The problem I have now though is I have a repeating field:
Jim Dettman "English" "French" "German"
Jon Doe "English" "French" "German" "Spanish"
Jane Doe "English"
This is a red flag that I need to do something different. When you have repeating fields, you need to break them out into seperate tables like this:
tblPatients
PatientID - Autonumber - Primary Key
LastName - Text
FirstName - Text
DateOfBirth - Date/Time
tblLanguages
LanguageID - Autonumber - Primary Key
LanguageName - Text
tblPatientLanguages - One record per patient/language combination
PatientID - Long - Lookup to tblPatients
LanguageID - Long - Lookup to tblPatients
with this setup, which is a "one to many" relationship (one patient can have one or more languages), I can have a patient and record as many languages as I need to (or just one) simply by adding records to this table.
3NF (Third nomal form) says that you don't store calculated values. For example, you would not store a patients DOB and their age. Age is a calculated value based on the date and their DOB.
Another example, an extended price, which is: Quantity * Price . You store quantity and price, but not the result of quantity * price
I skimmed over a couple of things but those are the basics, so I am going to stop there and let you ask any questions you might have on the above.
JimD.