If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Table problem
Sorry for understanding slowly. I get it from all your term said.
Now I would like to learn 1). how to normalize the table with data already in it? I need your suggestion. 2). if I would like to keep the structure for those tables already have data in. and design an new table like what your suggestion. What should I take care about new table to connect to existing tables. I may have more question about it late on. I try to learn the possibility for normolizing database so I can talk to them about changes. The statisticean already wrote the code to pull out the data from the database for other projects. Thank you very much for all of you for giving me reply, suggestion and patience. "Jeff Boyce" wrote: "better" for whom? Access is a relational database. If you want to get the best use of it, your data will need to be in a format that recognizes this (i.e., is well-normalized). As others have pointed out, more than 20 or 30 fields in a single table is usually a symptom... you may have committed spreadsheet on Access. Regards Jeff Boyce Microsoft Office/Access MVP "Redwood" wrote in message ... The fields in most of major tables in the database are over 100. This database handles several projects. This table is for new project. It would be better for keeping same with other projects. "Duane Hookom" wrote: Can you justify the need for more than about 30 fields? -- Duane Hookom MS Access MVP -- "Redwood" wrote in message ... I have a table which contains 245 fields. I need to change properties of some fields and get the message "property value is too large". I couldn't save any change. I try create a new table in different name and paste all the fields to new table. It only let me paste about 190 fields into the new table, then the same message comes up when I save the new table. I did compact and repair the database and didn't get any help. There is any way to solve this problem without editing registry entry for the local computer (I need to share the database with other user). |
#22
|
|||
|
|||
Table problem
It sounds as if you are building tables to match your forms. That way, all
of the information in a particular table matches the information in a particular form. Some of your forms have a lot of information in them. If you start using a different form, you build a new table to match that form. However, it also sounds as if you are using a field for each entry in your form. So your table might look like this: tbl_Form1 Form1ID (PK) PatientName Question1 Question2 Question3 This would be good way to organize your data, if you were using a spreadsheet. However, Access is not a spreadsheet, and cannot be used efficiently in this way. For instance, there is a definite small limit to how many fields you can put into a table. If, for instance, you have a form with questions, and those questions refer to a particular patient, for a particular visit, you could make some tables that look like this: tbl_Form1 [This table indicates that a form was filled out by a particular patient on a particular day] Form1ID (PK) PatientID Form1Date tbl_Patients [This table is for listing patients] PatientID (PK) PatientInfo tbl_Form1Questions [This table is for listing all of the questions on your form] Form1QuestionID (PK) Form1QuestionText tbl_Form1Answers [This table is for listing all of the answers from the form, using relationships to indicate which question the answer is for, and which patient, through the Form1ID, the form was filled out for] Form1AnswerID (PK) Form1ID Form1QuestionID Form1Answer "Redwood" wrote: Soryy for bothering you again. 1. I am not sure the mean "In the subform, key entry person would enter as many parameters as needed". I have a form for only enter visit information and the data will enter to visit table. I have ten form because I need the screen form to macth with paper form. Each screen form only enter part of fields data to this big table. e.g. 10 fields data from form1 and 15 fields data from form2....., add all these forms together, the fields will be over 200. I did not design one form for one visit. I would like to learn how can I enter as many parameters as needed. It will help me work out my problem. 2. I create the queries using patients table, visit table and this bigtable. patID field for join patient table and visit table. VisitID for join visit table and the big table. there is only one row for each patient in patient table and more then one row in visit table for each patient. The big table may have same or less rows then visit table for each patient. The visit table is for all projecta and the big table is only for a new project. I am not sure what's worng about queries, would you please let know? I used to have one database for one project and have a data management database to manage all the projects database. Here they use to put all projects in on database. "Dejan" wrote: It looks you already have troubles. Big table = 10 data entry forms. With proposed normalization, you would need only one data entry form. Or, better to say, set of forms. You would need a master form for each visit and subform for parameters. In the subform, key entry person would enter as many parameters as needed. From experience, it is much easier to query normalized than non normalized tables. Now, if you want to do simple counts for each parameter, you need about 200 queries, one for each parameter. With proposed normalized structure, you would have to create and run only one query. Seems like a quite diferrence to me. And yes, by adding something like PROJECTS table, you can keep multiple projects in the same Access database. In any case, good luck -- :-) "Dejan" wrote: It looks you are keeping track of some medical exams or so. For each visit you collect data on some or all of the following: MedicalHEENT, MedicalHEENTComm, NicotineUseY, LLateralFlexionLumbarDegree, ........ You can reduce number of fields from 245 to less by splitting data in 3 (three) tables: Visits, VisitParameters, VisitParameters Visits = info on visit only VisitParameters = all possible parameters you may expect to see in a visit VisitParameters = actual parameters found in particular visit Table structures: VISITS (VisitID, VistDate, patientID) etc -- all about visit, nothing about parameters PARAMETERS (ParamID, Description) = list of all parameters that you may collect on any given visit. Most of your fields from the original table will be stored here, as records. Example: ParamID, Description -------------------- "MedicalHEENT", "Medical HEENT" "MedicalHEENTComm", "Comment for Medical HEENT" "NicotineUseY", "Yes if the patient is nicotine user" "LLateralFlexionLumbarDegree","Degree of Lateral Lumbar Flexion" "LLateralFlexionLumbarPain","Presence of pain with LLFL" You got the idea? VISIT_PARAMETERS (VisitID, ParamID, Value) = this is the key piece in the story. For each visit, you enter as many records as you need. Like this: VisitID, ParamID, Value ----------------- 1, "MedicalHEENT", "37" 1, "MedicalHEENTComm", "very bad, but not life threatening" 1, "LLateralFlexionLumbarDegree","High" 1, "LLateralFlexionLumbarPain", "No" 1, "NicotineUseY","No" 2, "LLateralFlexionLumbarDegree","Medium" 2, "LLateralFlexionLumbarPain", "No" 2, "NicotineUseY","Yes" So, in Visit=1 we collected 5 parameters and recorded their values Visit 2 was not so rich, only 3 parameters. Of course, you may expand this, so for each Parameter in VISIT_PARAMETERS you may want to provide a lookup (combo box) list of valid values and so on. In any case, instead of one very wide table, you will have at least three narrow but long tables. Wide table means many fields, long table means many records. There is more room for design improvement, but this should give you an idea. -- :-) "Redwood" wrote: Here are some fields name in this table: BoneGraftEvalSFID(AutoNumber, Long Integer, Increment, No duplicateds), VisitID(Number,Long Integer), GraftEvalDate(Date/Time), DateOfMedicalEval(Date/Time), MedicalHEENT(Text,15, Combo box), MedicalHEENTComm(Text,200), NicotineUseY(Yes/No), NicotineUseN(Yes/No), LLateralFlexionLumbarDegree(Number, long Integer),LLateralFlexionLumbarPain(Text,15, Combo Box)), BridgingAcrossR(text,10,combo box), BridgingAcrossL(Text,10,combo box), Comments(Memo). The table has a relationship with Visit table by VisitID, Join type is 1. Enforce Referential Integrity box has been checked, Relationship type show one-to-many. The table structure should be right. I can change some check box fields to combo box fields to reduce number of fields, but there is not much room for it (about ten fields). Thank you very much for help! "John Vinson" wrote: On Wed, 29 Mar 2006 13:33:03 -0800, Redwood wrote: The database handles several projects. this table is for new project. the table based on the visitID which from visit table. The visitID is no duplicate. It's not that VisitID is a duplicate; the problem is that you are almost certainly storing a one-to-many relationship IN EVERY RECORD, by storing data (of some sort, we cannot see your structure so we don't know the details) IN FIELDNAMES. If you have fields named (for example) Checkpoint1Date, Checkpoint1Attained, Checkpoint1Comments, Checkpoint2Date, Checkpoint2Attained, Checkpoint2Comments, etc.etc., then your table structure *is simply wrong*. Could you post the names of ten or twelve of these far-too-many fields? We can very likely get you into a new mindset which will make Access work much more easily for you! John W. Vinson[MVP] |
#23
|
|||
|
|||
Table problem
You are right. The database is like you said. I already have patient table
and visit table. The big table is for all the question answer's form. If spliting the big table to the ten small tables is a good way to design and use the access in my case. I may try it, because I still didn't figure out how to design the screen form which should macth with paper form by using the normalized table. "mnature" wrote: It sounds as if you are building tables to match your forms. That way, all of the information in a particular table matches the information in a particular form. Some of your forms have a lot of information in them. If you start using a different form, you build a new table to match that form. However, it also sounds as if you are using a field for each entry in your form. So your table might look like this: tbl_Form1 Form1ID (PK) PatientName Question1 Question2 Question3 This would be good way to organize your data, if you were using a spreadsheet. However, Access is not a spreadsheet, and cannot be used efficiently in this way. For instance, there is a definite small limit to how many fields you can put into a table. If, for instance, you have a form with questions, and those questions refer to a particular patient, for a particular visit, you could make some tables that look like this: tbl_Form1 [This table indicates that a form was filled out by a particular patient on a particular day] Form1ID (PK) PatientID Form1Date tbl_Patients [This table is for listing patients] PatientID (PK) PatientInfo tbl_Form1Questions [This table is for listing all of the questions on your form] Form1QuestionID (PK) Form1QuestionText tbl_Form1Answers [This table is for listing all of the answers from the form, using relationships to indicate which question the answer is for, and which patient, through the Form1ID, the form was filled out for] Form1AnswerID (PK) Form1ID Form1QuestionID Form1Answer "Redwood" wrote: Soryy for bothering you again. 1. I am not sure the mean "In the subform, key entry person would enter as many parameters as needed". I have a form for only enter visit information and the data will enter to visit table. I have ten form because I need the screen form to macth with paper form. Each screen form only enter part of fields data to this big table. e.g. 10 fields data from form1 and 15 fields data from form2....., add all these forms together, the fields will be over 200. I did not design one form for one visit. I would like to learn how can I enter as many parameters as needed. It will help me work out my problem. 2. I create the queries using patients table, visit table and this bigtable. patID field for join patient table and visit table. VisitID for join visit table and the big table. there is only one row for each patient in patient table and more then one row in visit table for each patient. The big table may have same or less rows then visit table for each patient. The visit table is for all projecta and the big table is only for a new project. I am not sure what's worng about queries, would you please let know? I used to have one database for one project and have a data management database to manage all the projects database. Here they use to put all projects in on database. "Dejan" wrote: It looks you already have troubles. Big table = 10 data entry forms. With proposed normalization, you would need only one data entry form. Or, better to say, set of forms. You would need a master form for each visit and subform for parameters. In the subform, key entry person would enter as many parameters as needed. From experience, it is much easier to query normalized than non normalized tables. Now, if you want to do simple counts for each parameter, you need about 200 queries, one for each parameter. With proposed normalized structure, you would have to create and run only one query. Seems like a quite diferrence to me. And yes, by adding something like PROJECTS table, you can keep multiple projects in the same Access database. In any case, good luck -- :-) "Dejan" wrote: It looks you are keeping track of some medical exams or so. For each visit you collect data on some or all of the following: MedicalHEENT, MedicalHEENTComm, NicotineUseY, LLateralFlexionLumbarDegree, ........ You can reduce number of fields from 245 to less by splitting data in 3 (three) tables: Visits, VisitParameters, VisitParameters Visits = info on visit only VisitParameters = all possible parameters you may expect to see in a visit VisitParameters = actual parameters found in particular visit Table structures: VISITS (VisitID, VistDate, patientID) etc -- all about visit, nothing about parameters PARAMETERS (ParamID, Description) = list of all parameters that you may collect on any given visit. Most of your fields from the original table will be stored here, as records. Example: ParamID, Description -------------------- "MedicalHEENT", "Medical HEENT" "MedicalHEENTComm", "Comment for Medical HEENT" "NicotineUseY", "Yes if the patient is nicotine user" "LLateralFlexionLumbarDegree","Degree of Lateral Lumbar Flexion" "LLateralFlexionLumbarPain","Presence of pain with LLFL" You got the idea? VISIT_PARAMETERS (VisitID, ParamID, Value) = this is the key piece in the story. For each visit, you enter as many records as you need. Like this: VisitID, ParamID, Value ----------------- 1, "MedicalHEENT", "37" 1, "MedicalHEENTComm", "very bad, but not life threatening" 1, "LLateralFlexionLumbarDegree","High" 1, "LLateralFlexionLumbarPain", "No" 1, "NicotineUseY","No" 2, "LLateralFlexionLumbarDegree","Medium" 2, "LLateralFlexionLumbarPain", "No" 2, "NicotineUseY","Yes" So, in Visit=1 we collected 5 parameters and recorded their values Visit 2 was not so rich, only 3 parameters. Of course, you may expand this, so for each Parameter in VISIT_PARAMETERS you may want to provide a lookup (combo box) list of valid values and so on. In any case, instead of one very wide table, you will have at least three narrow but long tables. Wide table means many fields, long table means many records. There is more room for design improvement, but this should give you an idea. -- :-) "Redwood" wrote: Here are some fields name in this table: BoneGraftEvalSFID(AutoNumber, Long Integer, Increment, No duplicateds), VisitID(Number,Long Integer), GraftEvalDate(Date/Time), DateOfMedicalEval(Date/Time), MedicalHEENT(Text,15, Combo box), MedicalHEENTComm(Text,200), NicotineUseY(Yes/No), NicotineUseN(Yes/No), LLateralFlexionLumbarDegree(Number, long Integer),LLateralFlexionLumbarPain(Text,15, Combo Box)), BridgingAcrossR(text,10,combo box), BridgingAcrossL(Text,10,combo box), Comments(Memo). The table has a relationship with Visit table by VisitID, Join type is 1. Enforce Referential Integrity box has been checked, Relationship type show one-to-many. The table structure should be right. I can change some check box fields to combo box fields to reduce number of fields, but there is not much room for it (about ten fields). Thank you very much for help! "John Vinson" wrote: On Wed, 29 Mar 2006 13:33:03 -0800, Redwood wrote: The database handles several projects. this table is for new project. the table based on the visitID which from visit table. The visitID is no duplicate. It's not that VisitID is a duplicate; the problem is that you are almost certainly storing a one-to-many relationship IN EVERY RECORD, by storing data (of some sort, we cannot see your structure so we don't know the details) IN FIELDNAMES. If you have fields named (for example) Checkpoint1Date, Checkpoint1Attained, Checkpoint1Comments, Checkpoint2Date, Checkpoint2Attained, Checkpoint2Comments, etc.etc., then your table structure *is simply wrong*. Could you post the names of ten or twelve of these far-too-many fields? We can very likely get you into a new mindset which will make Access work much more easily for you! John W. Vinson[MVP] |
#24
|
|||
|
|||
Table problem
Using the normalized tables, you would present your data in a form by using a
main form to designate the patient and date, and then a subform to show the data in the same format that your form uses. You can make the subform look very similar, if not identical, to the paper form. A relational database can be very confusing, especially if you have used spreadsheets for a number of years. However, there is a cardinal rule of using a database, and that is to let the database do as much of the work as possible. Once your data goes into the normalized tables, you can display it in any form you want, either as a form or as a report. You can pull as much, or as little, of the data as you want. You can display it as a form or a datasheet. You can sort by date, patient, or answers. However, to do all of this you do need to create the tables correctly. Otherwise, you will do all of the work, instead of the database doing the work. You can leave your tables as spreadsheets, and still get some of the information you need, but there will be many things you will not be able to do. Good luck to you. "Redwood" wrote: You are right. The database is like you said. I already have patient table and visit table. The big table is for all the question answer's form. If spliting the big table to the ten small tables is a good way to design and use the access in my case. I may try it, because I still didn't figure out how to design the screen form which should macth with paper form by using the normalized table. "mnature" wrote: It sounds as if you are building tables to match your forms. That way, all of the information in a particular table matches the information in a particular form. Some of your forms have a lot of information in them. If you start using a different form, you build a new table to match that form. However, it also sounds as if you are using a field for each entry in your form. So your table might look like this: tbl_Form1 Form1ID (PK) PatientName Question1 Question2 Question3 This would be good way to organize your data, if you were using a spreadsheet. However, Access is not a spreadsheet, and cannot be used efficiently in this way. For instance, there is a definite small limit to how many fields you can put into a table. If, for instance, you have a form with questions, and those questions refer to a particular patient, for a particular visit, you could make some tables that look like this: tbl_Form1 [This table indicates that a form was filled out by a particular patient on a particular day] Form1ID (PK) PatientID Form1Date tbl_Patients [This table is for listing patients] PatientID (PK) PatientInfo tbl_Form1Questions [This table is for listing all of the questions on your form] Form1QuestionID (PK) Form1QuestionText tbl_Form1Answers [This table is for listing all of the answers from the form, using relationships to indicate which question the answer is for, and which patient, through the Form1ID, the form was filled out for] Form1AnswerID (PK) Form1ID Form1QuestionID Form1Answer "Redwood" wrote: Soryy for bothering you again. 1. I am not sure the mean "In the subform, key entry person would enter as many parameters as needed". I have a form for only enter visit information and the data will enter to visit table. I have ten form because I need the screen form to macth with paper form. Each screen form only enter part of fields data to this big table. e.g. 10 fields data from form1 and 15 fields data from form2....., add all these forms together, the fields will be over 200. I did not design one form for one visit. I would like to learn how can I enter as many parameters as needed. It will help me work out my problem. 2. I create the queries using patients table, visit table and this bigtable. patID field for join patient table and visit table. VisitID for join visit table and the big table. there is only one row for each patient in patient table and more then one row in visit table for each patient. The big table may have same or less rows then visit table for each patient. The visit table is for all projecta and the big table is only for a new project. I am not sure what's worng about queries, would you please let know? I used to have one database for one project and have a data management database to manage all the projects database. Here they use to put all projects in on database. "Dejan" wrote: It looks you already have troubles. Big table = 10 data entry forms. With proposed normalization, you would need only one data entry form. Or, better to say, set of forms. You would need a master form for each visit and subform for parameters. In the subform, key entry person would enter as many parameters as needed. From experience, it is much easier to query normalized than non normalized tables. Now, if you want to do simple counts for each parameter, you need about 200 queries, one for each parameter. With proposed normalized structure, you would have to create and run only one query. Seems like a quite diferrence to me. And yes, by adding something like PROJECTS table, you can keep multiple projects in the same Access database. In any case, good luck -- :-) "Dejan" wrote: It looks you are keeping track of some medical exams or so. For each visit you collect data on some or all of the following: MedicalHEENT, MedicalHEENTComm, NicotineUseY, LLateralFlexionLumbarDegree, ........ You can reduce number of fields from 245 to less by splitting data in 3 (three) tables: Visits, VisitParameters, VisitParameters Visits = info on visit only VisitParameters = all possible parameters you may expect to see in a visit VisitParameters = actual parameters found in particular visit Table structures: VISITS (VisitID, VistDate, patientID) etc -- all about visit, nothing about parameters PARAMETERS (ParamID, Description) = list of all parameters that you may collect on any given visit. Most of your fields from the original table will be stored here, as records. Example: ParamID, Description -------------------- "MedicalHEENT", "Medical HEENT" "MedicalHEENTComm", "Comment for Medical HEENT" "NicotineUseY", "Yes if the patient is nicotine user" "LLateralFlexionLumbarDegree","Degree of Lateral Lumbar Flexion" "LLateralFlexionLumbarPain","Presence of pain with LLFL" You got the idea? VISIT_PARAMETERS (VisitID, ParamID, Value) = this is the key piece in the story. For each visit, you enter as many records as you need. Like this: VisitID, ParamID, Value ----------------- 1, "MedicalHEENT", "37" 1, "MedicalHEENTComm", "very bad, but not life threatening" 1, "LLateralFlexionLumbarDegree","High" 1, "LLateralFlexionLumbarPain", "No" 1, "NicotineUseY","No" 2, "LLateralFlexionLumbarDegree","Medium" 2, "LLateralFlexionLumbarPain", "No" 2, "NicotineUseY","Yes" So, in Visit=1 we collected 5 parameters and recorded their values Visit 2 was not so rich, only 3 parameters. Of course, you may expand this, so for each Parameter in VISIT_PARAMETERS you may want to provide a lookup (combo box) list of valid values and so on. In any case, instead of one very wide table, you will have at least three narrow but long tables. Wide table means many fields, long table means many records. There is more room for design improvement, but this should give you an idea. -- :-) "Redwood" wrote: Here are some fields name in this table: BoneGraftEvalSFID(AutoNumber, Long Integer, Increment, No duplicateds), VisitID(Number,Long Integer), GraftEvalDate(Date/Time), DateOfMedicalEval(Date/Time), MedicalHEENT(Text,15, Combo box), MedicalHEENTComm(Text,200), NicotineUseY(Yes/No), NicotineUseN(Yes/No), LLateralFlexionLumbarDegree(Number, long Integer),LLateralFlexionLumbarPain(Text,15, Combo Box)), BridgingAcrossR(text,10,combo box), BridgingAcrossL(Text,10,combo box), Comments(Memo). The table has a relationship with Visit table by VisitID, Join type is 1. Enforce Referential Integrity box has been checked, Relationship type show one-to-many. The table structure should be right. I can change some check box fields to combo box fields to reduce number of fields, but there is not much room for it (about ten fields). Thank you very much for help! "John Vinson" wrote: On Wed, 29 Mar 2006 13:33:03 -0800, Redwood wrote: The database handles several projects. this table is for new project. the table based on the visitID which from visit table. The visitID is no duplicate. It's not that VisitID is a duplicate; the problem is that you are almost certainly storing a one-to-many relationship IN EVERY RECORD, by storing data (of some sort, we cannot see your structure so we don't know the details) IN FIELDNAMES. If you have fields named (for example) Checkpoint1Date, Checkpoint1Attained, Checkpoint1Comments, Checkpoint2Date, Checkpoint2Attained, Checkpoint2Comments, etc.etc., then your table structure *is simply wrong*. Could you post the names of ten or twelve of these far-too-many fields? We can very likely get you into a new mindset which will make Access work much more easily for you! John W. Vinson[MVP] |
#25
|
|||
|
|||
Table problem
Thank you very much. I will try it and be back with question about normalized
table next week. "mnature" wrote: Using the normalized tables, you would present your data in a form by using a main form to designate the patient and date, and then a subform to show the data in the same format that your form uses. You can make the subform look very similar, if not identical, to the paper form. A relational database can be very confusing, especially if you have used spreadsheets for a number of years. However, there is a cardinal rule of using a database, and that is to let the database do as much of the work as possible. Once your data goes into the normalized tables, you can display it in any form you want, either as a form or as a report. You can pull as much, or as little, of the data as you want. You can display it as a form or a datasheet. You can sort by date, patient, or answers. However, to do all of this you do need to create the tables correctly. Otherwise, you will do all of the work, instead of the database doing the work. You can leave your tables as spreadsheets, and still get some of the information you need, but there will be many things you will not be able to do. Good luck to you. "Redwood" wrote: You are right. The database is like you said. I already have patient table and visit table. The big table is for all the question answer's form. If spliting the big table to the ten small tables is a good way to design and use the access in my case. I may try it, because I still didn't figure out how to design the screen form which should macth with paper form by using the normalized table. "mnature" wrote: It sounds as if you are building tables to match your forms. That way, all of the information in a particular table matches the information in a particular form. Some of your forms have a lot of information in them. If you start using a different form, you build a new table to match that form. However, it also sounds as if you are using a field for each entry in your form. So your table might look like this: tbl_Form1 Form1ID (PK) PatientName Question1 Question2 Question3 This would be good way to organize your data, if you were using a spreadsheet. However, Access is not a spreadsheet, and cannot be used efficiently in this way. For instance, there is a definite small limit to how many fields you can put into a table. If, for instance, you have a form with questions, and those questions refer to a particular patient, for a particular visit, you could make some tables that look like this: tbl_Form1 [This table indicates that a form was filled out by a particular patient on a particular day] Form1ID (PK) PatientID Form1Date tbl_Patients [This table is for listing patients] PatientID (PK) PatientInfo tbl_Form1Questions [This table is for listing all of the questions on your form] Form1QuestionID (PK) Form1QuestionText tbl_Form1Answers [This table is for listing all of the answers from the form, using relationships to indicate which question the answer is for, and which patient, through the Form1ID, the form was filled out for] Form1AnswerID (PK) Form1ID Form1QuestionID Form1Answer "Redwood" wrote: Soryy for bothering you again. 1. I am not sure the mean "In the subform, key entry person would enter as many parameters as needed". I have a form for only enter visit information and the data will enter to visit table. I have ten form because I need the screen form to macth with paper form. Each screen form only enter part of fields data to this big table. e.g. 10 fields data from form1 and 15 fields data from form2....., add all these forms together, the fields will be over 200. I did not design one form for one visit. I would like to learn how can I enter as many parameters as needed. It will help me work out my problem. 2. I create the queries using patients table, visit table and this bigtable. patID field for join patient table and visit table. VisitID for join visit table and the big table. there is only one row for each patient in patient table and more then one row in visit table for each patient. The big table may have same or less rows then visit table for each patient. The visit table is for all projecta and the big table is only for a new project. I am not sure what's worng about queries, would you please let know? I used to have one database for one project and have a data management database to manage all the projects database. Here they use to put all projects in on database. "Dejan" wrote: It looks you already have troubles. Big table = 10 data entry forms. With proposed normalization, you would need only one data entry form. Or, better to say, set of forms. You would need a master form for each visit and subform for parameters. In the subform, key entry person would enter as many parameters as needed. From experience, it is much easier to query normalized than non normalized tables. Now, if you want to do simple counts for each parameter, you need about 200 queries, one for each parameter. With proposed normalized structure, you would have to create and run only one query. Seems like a quite diferrence to me. And yes, by adding something like PROJECTS table, you can keep multiple projects in the same Access database. In any case, good luck -- :-) "Dejan" wrote: It looks you are keeping track of some medical exams or so. For each visit you collect data on some or all of the following: MedicalHEENT, MedicalHEENTComm, NicotineUseY, LLateralFlexionLumbarDegree, ........ You can reduce number of fields from 245 to less by splitting data in 3 (three) tables: Visits, VisitParameters, VisitParameters Visits = info on visit only VisitParameters = all possible parameters you may expect to see in a visit VisitParameters = actual parameters found in particular visit Table structures: VISITS (VisitID, VistDate, patientID) etc -- all about visit, nothing about parameters PARAMETERS (ParamID, Description) = list of all parameters that you may collect on any given visit. Most of your fields from the original table will be stored here, as records. Example: ParamID, Description -------------------- "MedicalHEENT", "Medical HEENT" "MedicalHEENTComm", "Comment for Medical HEENT" "NicotineUseY", "Yes if the patient is nicotine user" "LLateralFlexionLumbarDegree","Degree of Lateral Lumbar Flexion" "LLateralFlexionLumbarPain","Presence of pain with LLFL" You got the idea? VISIT_PARAMETERS (VisitID, ParamID, Value) = this is the key piece in the story. For each visit, you enter as many records as you need. Like this: VisitID, ParamID, Value ----------------- 1, "MedicalHEENT", "37" 1, "MedicalHEENTComm", "very bad, but not life threatening" 1, "LLateralFlexionLumbarDegree","High" 1, "LLateralFlexionLumbarPain", "No" 1, "NicotineUseY","No" 2, "LLateralFlexionLumbarDegree","Medium" 2, "LLateralFlexionLumbarPain", "No" 2, "NicotineUseY","Yes" So, in Visit=1 we collected 5 parameters and recorded their values Visit 2 was not so rich, only 3 parameters. Of course, you may expand this, so for each Parameter in VISIT_PARAMETERS you may want to provide a lookup (combo box) list of valid values and so on. In any case, instead of one very wide table, you will have at least three narrow but long tables. Wide table means many fields, long table means many records. There is more room for design improvement, but this should give you an idea. -- :-) "Redwood" wrote: Here are some fields name in this table: BoneGraftEvalSFID(AutoNumber, Long Integer, Increment, No duplicateds), VisitID(Number,Long Integer), GraftEvalDate(Date/Time), DateOfMedicalEval(Date/Time), MedicalHEENT(Text,15, Combo box), MedicalHEENTComm(Text,200), NicotineUseY(Yes/No), NicotineUseN(Yes/No), LLateralFlexionLumbarDegree(Number, long Integer),LLateralFlexionLumbarPain(Text,15, Combo Box)), BridgingAcrossR(text,10,combo box), BridgingAcrossL(Text,10,combo box), Comments(Memo). The table has a relationship with Visit table by VisitID, Join type is 1. Enforce Referential Integrity box has been checked, Relationship type show one-to-many. The table structure should be right. I can change some check box fields to combo box fields to reduce number of fields, but there is not much room for it (about ten fields). Thank you very much for help! "John Vinson" wrote: On Wed, 29 Mar 2006 13:33:03 -0800, Redwood wrote: The database handles several projects. this table is for new project. the table based on the visitID which from visit table. The visitID is no duplicate. It's not that VisitID is a duplicate; the problem is that you are almost certainly storing a one-to-many relationship IN EVERY RECORD, by storing data (of some sort, we cannot see your structure so we don't know the details) IN FIELDNAMES. If you have fields named (for example) Checkpoint1Date, Checkpoint1Attained, Checkpoint1Comments, Checkpoint2Date, Checkpoint2Attained, Checkpoint2Comments, etc.etc., then your table structure *is simply wrong*. Could you post the names of ten or twelve of these far-too-many fields? We can very likely get you into a new mindset which will make Access work much more easily for you! John W. Vinson[MVP] |
#26
|
|||
|
|||
Table problem
On Thu, 30 Mar 2006 12:20:03 -0800, Redwood
wrote: You are right. The database is like you said. I already have patient table and visit table. The big table is for all the question answer's form. If spliting the big table to the ten small tables is a good way to design and use the access in my case. I may try it, because I still didn't figure out how to design the screen form which should macth with paper form by using the normalized table. Using a paper form as the design of a relational database table is almost GUARANTEED to give you a horribly badly denormalized structure. Don't confuse data STORAGE with data PRESENTATION. They are two quite different tasks with different requirements. Data storage is best done in a normalized structure - usually tall/thin tables. Admittedly, giving a user access to table datasheets of properly normalized tables would be an *extremely* frustrating experience - they'd see long lists of meaningless numbers with a few sort-of meaningful field values. That's why one generally will NOT do so. It can be difficult to design a good Form to make the transition from a paper data source to the normalized tables easy for the user. That's why good database designers don't come cheap! It's a lot of work! However, you'll find that once you've invested the work once, then new projects and new types of data become very easy. You don't need to restructure tables; you don't need to redesign forms; you just add new data to your tables and the forms reflect that data. John W. Vinson[MVP] |
#27
|
|||
|
|||
Table problem
Arbitrarily splitting fields of one large table into several smaller tables
isn't the road to normalization and the whole process really has nothing to do with Access. It is how you would proceed with any relational database. There is a great deal of information available on the Web on database design (some good and some not so good) and a lot on the Microsoft sites. Start with searching the MSDN library for articles on database design and normalization. There are also some excellent articles at www.fmsinc.com (you'll need to drill down to the technical papers pages. I don't have a direct link) At the FMS site, you'll find an especially interesting article because it explains normalization from the point of view of a spreadsheet. "Redwood" wrote in message ... You are right. The database is like you said. I already have patient table and visit table. The big table is for all the question answer's form. If spliting the big table to the ten small tables is a good way to design and use the access in my case. I may try it, because I still didn't figure out how to design the screen form which should macth with paper form by using the normalized table. "mnature" wrote: It sounds as if you are building tables to match your forms. That way, all of the information in a particular table matches the information in a particular form. Some of your forms have a lot of information in them. If you start using a different form, you build a new table to match that form. However, it also sounds as if you are using a field for each entry in your form. So your table might look like this: tbl_Form1 Form1ID (PK) PatientName Question1 Question2 Question3 This would be good way to organize your data, if you were using a spreadsheet. However, Access is not a spreadsheet, and cannot be used efficiently in this way. For instance, there is a definite small limit to how many fields you can put into a table. If, for instance, you have a form with questions, and those questions refer to a particular patient, for a particular visit, you could make some tables that look like this: tbl_Form1 [This table indicates that a form was filled out by a particular patient on a particular day] Form1ID (PK) PatientID Form1Date tbl_Patients [This table is for listing patients] PatientID (PK) PatientInfo tbl_Form1Questions [This table is for listing all of the questions on your form] Form1QuestionID (PK) Form1QuestionText tbl_Form1Answers [This table is for listing all of the answers from the form, using relationships to indicate which question the answer is for, and which patient, through the Form1ID, the form was filled out for] Form1AnswerID (PK) Form1ID Form1QuestionID Form1Answer "Redwood" wrote: Soryy for bothering you again. 1. I am not sure the mean "In the subform, key entry person would enter as many parameters as needed". I have a form for only enter visit information and the data will enter to visit table. I have ten form because I need the screen form to macth with paper form. Each screen form only enter part of fields data to this big table. e.g. 10 fields data from form1 and 15 fields data from form2....., add all these forms together, the fields will be over 200. I did not design one form for one visit. I would like to learn how can I enter as many parameters as needed. It will help me work out my problem. 2. I create the queries using patients table, visit table and this bigtable. patID field for join patient table and visit table. VisitID for join visit table and the big table. there is only one row for each patient in patient table and more then one row in visit table for each patient. The big table may have same or less rows then visit table for each patient. The visit table is for all projecta and the big table is only for a new project. I am not sure what's worng about queries, would you please let know? I used to have one database for one project and have a data management database to manage all the projects database. Here they use to put all projects in on database. "Dejan" wrote: It looks you already have troubles. Big table = 10 data entry forms. With proposed normalization, you would need only one data entry form. Or, better to say, set of forms. You would need a master form for each visit and subform for parameters. In the subform, key entry person would enter as many parameters as needed. From experience, it is much easier to query normalized than non normalized tables. Now, if you want to do simple counts for each parameter, you need about 200 queries, one for each parameter. With proposed normalized structure, you would have to create and run only one query. Seems like a quite diferrence to me. And yes, by adding something like PROJECTS table, you can keep multiple projects in the same Access database. In any case, good luck -- :-) "Dejan" wrote: It looks you are keeping track of some medical exams or so. For each visit you collect data on some or all of the following: MedicalHEENT, MedicalHEENTComm, NicotineUseY, LLateralFlexionLumbarDegree, ........ You can reduce number of fields from 245 to less by splitting data in 3 (three) tables: Visits, VisitParameters, VisitParameters Visits = info on visit only VisitParameters = all possible parameters you may expect to see in a visit VisitParameters = actual parameters found in particular visit Table structures: VISITS (VisitID, VistDate, patientID) etc -- all about visit, nothing about parameters PARAMETERS (ParamID, Description) = list of all parameters that you may collect on any given visit. Most of your fields from the original table will be stored here, as records. Example: ParamID, Description -------------------- "MedicalHEENT", "Medical HEENT" "MedicalHEENTComm", "Comment for Medical HEENT" "NicotineUseY", "Yes if the patient is nicotine user" "LLateralFlexionLumbarDegree","Degree of Lateral Lumbar Flexion" "LLateralFlexionLumbarPain","Presence of pain with LLFL" You got the idea? VISIT_PARAMETERS (VisitID, ParamID, Value) = this is the key piece in the story. For each visit, you enter as many records as you need. Like this: VisitID, ParamID, Value ----------------- 1, "MedicalHEENT", "37" 1, "MedicalHEENTComm", "very bad, but not life threatening" 1, "LLateralFlexionLumbarDegree","High" 1, "LLateralFlexionLumbarPain", "No" 1, "NicotineUseY","No" 2, "LLateralFlexionLumbarDegree","Medium" 2, "LLateralFlexionLumbarPain", "No" 2, "NicotineUseY","Yes" So, in Visit=1 we collected 5 parameters and recorded their values Visit 2 was not so rich, only 3 parameters. Of course, you may expand this, so for each Parameter in VISIT_PARAMETERS you may want to provide a lookup (combo box) list of valid values and so on. In any case, instead of one very wide table, you will have at least three narrow but long tables. Wide table means many fields, long table means many records. There is more room for design improvement, but this should give you an idea. -- :-) "Redwood" wrote: Here are some fields name in this table: BoneGraftEvalSFID(AutoNumber, Long Integer, Increment, No duplicateds), VisitID(Number,Long Integer), GraftEvalDate(Date/Time), DateOfMedicalEval(Date/Time), MedicalHEENT(Text,15, Combo box), MedicalHEENTComm(Text,200), NicotineUseY(Yes/No), NicotineUseN(Yes/No), LLateralFlexionLumbarDegree(Number, long Integer),LLateralFlexionLumbarPain(Text,15, Combo Box)), BridgingAcrossR(text,10,combo box), BridgingAcrossL(Text,10,combo box), Comments(Memo). The table has a relationship with Visit table by VisitID, Join type is 1. Enforce Referential Integrity box has been checked, Relationship type show one-to-many. The table structure should be right. I can change some check box fields to combo box fields to reduce number of fields, but there is not much room for it (about ten fields). Thank you very much for help! "John Vinson" wrote: On Wed, 29 Mar 2006 13:33:03 -0800, Redwood wrote: The database handles several projects. this table is for new project. the table based on the visitID which from visit table. The visitID is no duplicate. It's not that VisitID is a duplicate; the problem is that you are almost certainly storing a one-to-many relationship IN EVERY RECORD, by storing data (of some sort, we cannot see your structure so we don't know the details) IN FIELDNAMES. If you have fields named (for example) Checkpoint1Date, Checkpoint1Attained, Checkpoint1Comments, Checkpoint2Date, Checkpoint2Attained, Checkpoint2Comments, etc.etc., then your table structure *is simply wrong*. Could you post the names of ten or twelve of these far-too-many fields? We can very likely get you into a new mindset which will make Access work much more easily for you! John W. Vinson[MVP] |
#28
|
|||
|
|||
Table problem
Thank your very much. I found the articles in www.fmsinc.com and it is
helpful. I am trying to open the sample database form the web on my computer because some vision prorblems. Thank you again! "Pat Hartman(MVP)" wrote: Arbitrarily splitting fields of one large table into several smaller tables isn't the road to normalization and the whole process really has nothing to do with Access. It is how you would proceed with any relational database. There is a great deal of information available on the Web on database design (some good and some not so good) and a lot on the Microsoft sites. Start with searching the MSDN library for articles on database design and normalization. There are also some excellent articles at www.fmsinc.com (you'll need to drill down to the technical papers pages. I don't have a direct link) At the FMS site, you'll find an especially interesting article because it explains normalization from the point of view of a spreadsheet. "Redwood" wrote in message ... You are right. The database is like you said. I already have patient table and visit table. The big table is for all the question answer's form. If spliting the big table to the ten small tables is a good way to design and use the access in my case. I may try it, because I still didn't figure out how to design the screen form which should macth with paper form by using the normalized table. "mnature" wrote: It sounds as if you are building tables to match your forms. That way, all of the information in a particular table matches the information in a particular form. Some of your forms have a lot of information in them. If you start using a different form, you build a new table to match that form. However, it also sounds as if you are using a field for each entry in your form. So your table might look like this: tbl_Form1 Form1ID (PK) PatientName Question1 Question2 Question3 This would be good way to organize your data, if you were using a spreadsheet. However, Access is not a spreadsheet, and cannot be used efficiently in this way. For instance, there is a definite small limit to how many fields you can put into a table. If, for instance, you have a form with questions, and those questions refer to a particular patient, for a particular visit, you could make some tables that look like this: tbl_Form1 [This table indicates that a form was filled out by a particular patient on a particular day] Form1ID (PK) PatientID Form1Date tbl_Patients [This table is for listing patients] PatientID (PK) PatientInfo tbl_Form1Questions [This table is for listing all of the questions on your form] Form1QuestionID (PK) Form1QuestionText tbl_Form1Answers [This table is for listing all of the answers from the form, using relationships to indicate which question the answer is for, and which patient, through the Form1ID, the form was filled out for] Form1AnswerID (PK) Form1ID Form1QuestionID Form1Answer "Redwood" wrote: Soryy for bothering you again. 1. I am not sure the mean "In the subform, key entry person would enter as many parameters as needed". I have a form for only enter visit information and the data will enter to visit table. I have ten form because I need the screen form to macth with paper form. Each screen form only enter part of fields data to this big table. e.g. 10 fields data from form1 and 15 fields data from form2....., add all these forms together, the fields will be over 200. I did not design one form for one visit. I would like to learn how can I enter as many parameters as needed. It will help me work out my problem. 2. I create the queries using patients table, visit table and this bigtable. patID field for join patient table and visit table. VisitID for join visit table and the big table. there is only one row for each patient in patient table and more then one row in visit table for each patient. The big table may have same or less rows then visit table for each patient. The visit table is for all projecta and the big table is only for a new project. I am not sure what's worng about queries, would you please let know? I used to have one database for one project and have a data management database to manage all the projects database. Here they use to put all projects in on database. "Dejan" wrote: It looks you already have troubles. Big table = 10 data entry forms. With proposed normalization, you would need only one data entry form. Or, better to say, set of forms. You would need a master form for each visit and subform for parameters. In the subform, key entry person would enter as many parameters as needed. From experience, it is much easier to query normalized than non normalized tables. Now, if you want to do simple counts for each parameter, you need about 200 queries, one for each parameter. With proposed normalized structure, you would have to create and run only one query. Seems like a quite diferrence to me. And yes, by adding something like PROJECTS table, you can keep multiple projects in the same Access database. In any case, good luck -- :-) "Dejan" wrote: It looks you are keeping track of some medical exams or so. For each visit you collect data on some or all of the following: MedicalHEENT, MedicalHEENTComm, NicotineUseY, LLateralFlexionLumbarDegree, ........ You can reduce number of fields from 245 to less by splitting data in 3 (three) tables: Visits, VisitParameters, VisitParameters Visits = info on visit only VisitParameters = all possible parameters you may expect to see in a visit VisitParameters = actual parameters found in particular visit Table structures: VISITS (VisitID, VistDate, patientID) etc -- all about visit, nothing about parameters PARAMETERS (ParamID, Description) = list of all parameters that you may collect on any given visit. Most of your fields from the original table will be stored here, as records. Example: ParamID, Description -------------------- "MedicalHEENT", "Medical HEENT" "MedicalHEENTComm", "Comment for Medical HEENT" "NicotineUseY", "Yes if the patient is nicotine user" "LLateralFlexionLumbarDegree","Degree of Lateral Lumbar Flexion" "LLateralFlexionLumbarPain","Presence of pain with LLFL" You got the idea? VISIT_PARAMETERS (VisitID, ParamID, Value) = this is the key piece in the story. For each visit, you enter as many records as you need. Like this: VisitID, ParamID, Value ----------------- 1, "MedicalHEENT", "37" 1, "MedicalHEENTComm", "very bad, but not life threatening" 1, "LLateralFlexionLumbarDegree","High" 1, "LLateralFlexionLumbarPain", "No" 1, "NicotineUseY","No" 2, "LLateralFlexionLumbarDegree","Medium" 2, "LLateralFlexionLumbarPain", "No" 2, "NicotineUseY","Yes" So, in Visit=1 we collected 5 parameters and recorded their values Visit 2 was not so rich, only 3 parameters. Of course, you may expand this, so for each Parameter in VISIT_PARAMETERS you may want to provide a lookup (combo box) list of valid values and so on. In any case, instead of one very wide table, you will have at least three narrow but long tables. Wide table means many fields, long table means many records. There is more room for design improvement, but this should give you an idea. -- :-) "Redwood" wrote: Here are some fields name in this table: BoneGraftEvalSFID(AutoNumber, Long Integer, Increment, No duplicateds), VisitID(Number,Long Integer), GraftEvalDate(Date/Time), DateOfMedicalEval(Date/Time), MedicalHEENT(Text,15, Combo box), MedicalHEENTComm(Text,200), NicotineUseY(Yes/No), NicotineUseN(Yes/No), LLateralFlexionLumbarDegree(Number, long Integer),LLateralFlexionLumbarPain(Text,15, Combo Box)), BridgingAcrossR(text,10,combo box), BridgingAcrossL(Text,10,combo box), Comments(Memo). The table has a relationship with Visit table by VisitID, Join type is 1. Enforce Referential Integrity box has been checked, Relationship type show one-to-many. The table structure should be right. I can change some check box fields to combo box fields to reduce number of fields, but there is not much room for it (about ten fields). Thank you very much for help! "John Vinson" wrote: On Wed, 29 Mar 2006 13:33:03 -0800, Redwood wrote: The database handles several projects. this table is for new project. the table based on the visitID which from visit table. The visitID is no duplicate. It's not that VisitID is a duplicate; the problem is that you are almost certainly storing a one-to-many relationship IN EVERY RECORD, by storing data (of some sort, we cannot see your structure so we don't know the details) IN FIELDNAMES. If you have fields named (for example) Checkpoint1Date, Checkpoint1Attained, Checkpoint1Comments, Checkpoint2Date, Checkpoint2Attained, Checkpoint2Comments, etc.etc., then your table structure *is simply wrong*. Could you post the names of ten or twelve of these far-too-many fields? We can very likely get you into a new mindset which will make Access work much more easily for you! John W. Vinson[MVP] |
#29
|
|||
|
|||
Table problem
Hi, need your help. I am trying to become expensive database designer and
make more money in future . I have a Table like following: TreatmentVisit TestName TestDate Results 1 Sodium 5/3/05 141 1 Patassium 5/3/05 41 1 Chloride 5/3/05 103 1 . . . 1 . . . 2 Sodium 6/6/05 135 2 Patassium 6/6/05 36 .. . . . 3 . . . .. . . . .. . . . I use the countiune form view for form screen. How can it automaticly enter the test name and testdate when people enter a new treatment visit number. Do you have any sample code for it. Also how to code only show one treatment visit on the form screen when treatment visit number already in the table. Any tip, suggestion, sample code? Thank you "John Vinson" wrote: On Thu, 30 Mar 2006 12:20:03 -0800, Redwood wrote: You are right. The database is like you said. I already have patient table and visit table. The big table is for all the question answer's form. If spliting the big table to the ten small tables is a good way to design and use the access in my case. I may try it, because I still didn't figure out how to design the screen form which should macth with paper form by using the normalized table. Using a paper form as the design of a relational database table is almost GUARANTEED to give you a horribly badly denormalized structure. Don't confuse data STORAGE with data PRESENTATION. They are two quite different tasks with different requirements. Data storage is best done in a normalized structure - usually tall/thin tables. Admittedly, giving a user access to table datasheets of properly normalized tables would be an *extremely* frustrating experience - they'd see long lists of meaningless numbers with a few sort-of meaningful field values. That's why one generally will NOT do so. It can be difficult to design a good Form to make the transition from a paper data source to the normalized tables easy for the user. That's why good database designers don't come cheap! It's a lot of work! However, you'll find that once you've invested the work once, then new projects and new types of data become very easy. You don't need to restructure tables; you don't need to redesign forms; you just add new data to your tables and the forms reflect that data. John W. Vinson[MVP] |
#30
|
|||
|
|||
Table problem
When reading through the Normalization Tips at FMS, please ignore this page
http://www.fmsinc.com/tpapers/datanorm/index.html. Luke missed the boat on this article. His "normalized data" isn't much better than the "non-normalized spreadsheet data". Using budget categories for field names is not acceptable in a normalized application. I don't know why that page hasn't been updated or scrapped. -- Duane Hookom MS Access MVP -- "Redwood" wrote in message news Thank your very much. I found the articles in www.fmsinc.com and it is helpful. I am trying to open the sample database form the web on my computer because some vision prorblems. Thank you again! "Pat Hartman(MVP)" wrote: Arbitrarily splitting fields of one large table into several smaller tables isn't the road to normalization and the whole process really has nothing to do with Access. It is how you would proceed with any relational database. There is a great deal of information available on the Web on database design (some good and some not so good) and a lot on the Microsoft sites. Start with searching the MSDN library for articles on database design and normalization. There are also some excellent articles at www.fmsinc.com (you'll need to drill down to the technical papers pages. I don't have a direct link) At the FMS site, you'll find an especially interesting article because it explains normalization from the point of view of a spreadsheet. "Redwood" wrote in message ... You are right. The database is like you said. I already have patient table and visit table. The big table is for all the question answer's form. If spliting the big table to the ten small tables is a good way to design and use the access in my case. I may try it, because I still didn't figure out how to design the screen form which should macth with paper form by using the normalized table. "mnature" wrote: It sounds as if you are building tables to match your forms. That way, all of the information in a particular table matches the information in a particular form. Some of your forms have a lot of information in them. If you start using a different form, you build a new table to match that form. However, it also sounds as if you are using a field for each entry in your form. So your table might look like this: tbl_Form1 Form1ID (PK) PatientName Question1 Question2 Question3 This would be good way to organize your data, if you were using a spreadsheet. However, Access is not a spreadsheet, and cannot be used efficiently in this way. For instance, there is a definite small limit to how many fields you can put into a table. If, for instance, you have a form with questions, and those questions refer to a particular patient, for a particular visit, you could make some tables that look like this: tbl_Form1 [This table indicates that a form was filled out by a particular patient on a particular day] Form1ID (PK) PatientID Form1Date tbl_Patients [This table is for listing patients] PatientID (PK) PatientInfo tbl_Form1Questions [This table is for listing all of the questions on your form] Form1QuestionID (PK) Form1QuestionText tbl_Form1Answers [This table is for listing all of the answers from the form, using relationships to indicate which question the answer is for, and which patient, through the Form1ID, the form was filled out for] Form1AnswerID (PK) Form1ID Form1QuestionID Form1Answer "Redwood" wrote: Soryy for bothering you again. 1. I am not sure the mean "In the subform, key entry person would enter as many parameters as needed". I have a form for only enter visit information and the data will enter to visit table. I have ten form because I need the screen form to macth with paper form. Each screen form only enter part of fields data to this big table. e.g. 10 fields data from form1 and 15 fields data from form2....., add all these forms together, the fields will be over 200. I did not design one form for one visit. I would like to learn how can I enter as many parameters as needed. It will help me work out my problem. 2. I create the queries using patients table, visit table and this bigtable. patID field for join patient table and visit table. VisitID for join visit table and the big table. there is only one row for each patient in patient table and more then one row in visit table for each patient. The big table may have same or less rows then visit table for each patient. The visit table is for all projecta and the big table is only for a new project. I am not sure what's worng about queries, would you please let know? I used to have one database for one project and have a data management database to manage all the projects database. Here they use to put all projects in on database. "Dejan" wrote: It looks you already have troubles. Big table = 10 data entry forms. With proposed normalization, you would need only one data entry form. Or, better to say, set of forms. You would need a master form for each visit and subform for parameters. In the subform, key entry person would enter as many parameters as needed. From experience, it is much easier to query normalized than non normalized tables. Now, if you want to do simple counts for each parameter, you need about 200 queries, one for each parameter. With proposed normalized structure, you would have to create and run only one query. Seems like a quite diferrence to me. And yes, by adding something like PROJECTS table, you can keep multiple projects in the same Access database. In any case, good luck -- :-) "Dejan" wrote: It looks you are keeping track of some medical exams or so. For each visit you collect data on some or all of the following: MedicalHEENT, MedicalHEENTComm, NicotineUseY, LLateralFlexionLumbarDegree, ........ You can reduce number of fields from 245 to less by splitting data in 3 (three) tables: Visits, VisitParameters, VisitParameters Visits = info on visit only VisitParameters = all possible parameters you may expect to see in a visit VisitParameters = actual parameters found in particular visit Table structures: VISITS (VisitID, VistDate, patientID) etc -- all about visit, nothing about parameters PARAMETERS (ParamID, Description) = list of all parameters that you may collect on any given visit. Most of your fields from the original table will be stored here, as records. Example: ParamID, Description -------------------- "MedicalHEENT", "Medical HEENT" "MedicalHEENTComm", "Comment for Medical HEENT" "NicotineUseY", "Yes if the patient is nicotine user" "LLateralFlexionLumbarDegree","Degree of Lateral Lumbar Flexion" "LLateralFlexionLumbarPain","Presence of pain with LLFL" You got the idea? VISIT_PARAMETERS (VisitID, ParamID, Value) = this is the key piece in the story. For each visit, you enter as many records as you need. Like this: VisitID, ParamID, Value ----------------- 1, "MedicalHEENT", "37" 1, "MedicalHEENTComm", "very bad, but not life threatening" 1, "LLateralFlexionLumbarDegree","High" 1, "LLateralFlexionLumbarPain", "No" 1, "NicotineUseY","No" 2, "LLateralFlexionLumbarDegree","Medium" 2, "LLateralFlexionLumbarPain", "No" 2, "NicotineUseY","Yes" So, in Visit=1 we collected 5 parameters and recorded their values Visit 2 was not so rich, only 3 parameters. Of course, you may expand this, so for each Parameter in VISIT_PARAMETERS you may want to provide a lookup (combo box) list of valid values and so on. In any case, instead of one very wide table, you will have at least three narrow but long tables. Wide table means many fields, long table means many records. There is more room for design improvement, but this should give you an idea. -- :-) "Redwood" wrote: Here are some fields name in this table: BoneGraftEvalSFID(AutoNumber, Long Integer, Increment, No duplicateds), VisitID(Number,Long Integer), GraftEvalDate(Date/Time), DateOfMedicalEval(Date/Time), MedicalHEENT(Text,15, Combo box), MedicalHEENTComm(Text,200), NicotineUseY(Yes/No), NicotineUseN(Yes/No), LLateralFlexionLumbarDegree(Number, long Integer),LLateralFlexionLumbarPain(Text,15, Combo Box)), BridgingAcrossR(text,10,combo box), BridgingAcrossL(Text,10,combo box), Comments(Memo). The table has a relationship with Visit table by VisitID, Join type is 1. Enforce Referential Integrity box has been checked, Relationship type show one-to-many. The table structure should be right. I can change some check box fields to combo box fields to reduce number of fields, but there is not much room for it (about ten fields). Thank you very much for help! "John Vinson" wrote: On Wed, 29 Mar 2006 13:33:03 -0800, Redwood wrote: The database handles several projects. this table is for new project. the table based on the visitID which from visit table. The visitID is no duplicate. It's not that VisitID is a duplicate; the problem is that you are almost certainly storing a one-to-many relationship IN EVERY RECORD, by storing data (of some sort, we cannot see your structure so we don't know the details) IN FIELDNAMES. If you have fields named (for example) Checkpoint1Date, Checkpoint1Attained, Checkpoint1Comments, Checkpoint2Date, Checkpoint2Attained, Checkpoint2Comments, etc.etc., then your table structure *is simply wrong*. Could you post the names of ten or twelve of these far-too-many fields? We can very likely get you into a new mindset which will make Access work much more easily for you! John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query is not updatable - | Doug Johnson via AccessMonster.com | Running & Setting Up Queries | 3 | January 21st, 2006 12:36 AM |
Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
Row height problem with table in table | Stephanie T. | Tables | 0 | November 23rd, 2004 03:35 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |