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 |
#11
|
|||
|
|||
Table problem
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] |
#12
|
|||
|
|||
Table problem
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] |
#13
|
|||
|
|||
Table problem
I need to add this new project into a existing database and have to keep the
structure of this new project is consistent with other project in the database for future analysis and maintaince. The access designed to allowed have 255 fields in one table for maximum. If it only can handle 20 to 30 fields, people will think about it very carefully before using Access. "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). |
#14
|
|||
|
|||
Table problem
If you truly have fields like "BoneGraftEvalSFID" then you are storing data
(BoneGraft) in a field name. BoneGraft should be a value in a field that stores procedures. -- Duane Hookom MS Access MVP -- "Redwood" wrote in message ... I need to add this new project into a existing database and have to keep the structure of this new project is consistent with other project in the database for future analysis and maintaince. The access designed to allowed have 255 fields in one table for maximum. If it only can handle 20 to 30 fields, people will think about it very carefully before using Access. "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). |
#15
|
|||
|
|||
Table problem
Thank you so much for replying my posts.
I understand what is your mean. Splitting data in two or more table is my last choise. I have thinked about it before I posts the question. In the database, I already have patient's table and visit table which store the information for all projects. This table is only for a new project and all the fields in this table are parameters on visit for this project. Each patient has several rows in this table depending on visit times. the visitID is field to link to visit table and I can use it for creating queries. The reason I did not try to split the table is that I not sure if it would cause more trubles in future analysis. I need to think about it. I have ten forms based on this table. if I split it two tables: table1 and table2. then form 1-5 would use table1 and form 6-10 would use table2. if a patient have 5 visits, the visitID should be 1,2, 3, 4, 5, then both table shoud have 5 rows. but if the paitent only have the informtion in form 1-5 and nothing for form 6-10 for a visit, the data entry persion will only use form 1-5. so the table2 will nerve have this visitID. that means table1 will have 5 rows and table2 only has four rows. I am not sure if there is any truble for creating queries when I need the information from both table1 and table2 plus patient table and visit table. I had such kind problem before. Thank you very much for your time and help. "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] |
#16
|
|||
|
|||
Table problem
I use the "BoneGraftEvalSFID" field for audit trial. In other table also have
a field like this, e.g. "LabEvalID". We have a code to send the information of change to the audit trial database. "Duane Hookom" wrote: If you truly have fields like "BoneGraftEvalSFID" then you are storing data (BoneGraft) in a field name. BoneGraft should be a value in a field that stores procedures. -- Duane Hookom MS Access MVP -- "Redwood" wrote in message ... I need to add this new project into a existing database and have to keep the structure of this new project is consistent with other project in the database for future analysis and maintaince. The access designed to allowed have 255 fields in one table for maximum. If it only can handle 20 to 30 fields, people will think about it very carefully before using Access. "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). |
#17
|
|||
|
|||
Table problem
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] |
#18
|
|||
|
|||
Table problem
I think we are having a communication problem. Access doesn't limit you to
20-30 fields in a normalized table. Good database design simply results in many fewer columns per table than you have; 20-30 being a suggested maximum. There are of course exceptions. Your table is not an exception however. You have created a table that looks like a spreadsheet - wide (many columns) and short (few rows) whereas a good relational table will be narrow (few columns) and long (many rows). If you do some reading on normalization you may be able to understand what we are all talking about. In your case, I can understand why you are having difficulty recognizing the changes we are suggesting. Your data isn't an obvious repeating group. Those are easily identified because they include suffixed or prefixed names such as JanAmt, FebAmt or Contact1, Contact2. Yours is more subtle. For example, if you had a rental property and you needed to track expenses, you would most likely create a column for each expense type - insurance, property tax, oil, gas, water, trash removal, lawn maintenance, etc. In reality, this is a repeating group because all items are expenses. The data should be stored in a table with only four columns - PropertyID, TransactionDate, ExpenseType, ExpenseAmount. That structure would allow you to store an infinite number of different expenses. It would also allow you to add new ExpenseTypes without modifications to your forms/reports/queries/or tables. So, if you acquired a new property that had a swimming pool, you could now add pool maintenance, and pool chemicals as expense types without changing any structure. With the spreadsheet technique, you would need to add two new columns to the table and change ALL your reports, queries, and forms. That's the difference between normalized and unnormalized. Since you already have data in your database, you would need to create a number of queries to "normalize" it should you decide to take the offered advice and normalize. We can offer suggestions on that also. "Redwood" wrote in message ... I use the "BoneGraftEvalSFID" field for audit trial. In other table also have a field like this, e.g. "LabEvalID". We have a code to send the information of change to the audit trial database. "Duane Hookom" wrote: If you truly have fields like "BoneGraftEvalSFID" then you are storing data (BoneGraft) in a field name. BoneGraft should be a value in a field that stores procedures. -- Duane Hookom MS Access MVP -- "Redwood" wrote in message ... I need to add this new project into a existing database and have to keep the structure of this new project is consistent with other project in the database for future analysis and maintaince. The access designed to allowed have 255 fields in one table for maximum. If it only can handle 20 to 30 fields, people will think about it very carefully before using Access. "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). |
#19
|
|||
|
|||
Table problem
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] |
#20
|
|||
|
|||
Table problem
As others have pointed out, you are experiencing difficulties getting Access
to do what you want because of the way the data is structured. The suggestions about normalizing your data are intended to point the way to getting better use (and easier use) out of Access. Access limits you to 255 fields per table, but normalization rarely requires more than 20-30 per table. If you'd rather stick with the data the way it is now organized, that's your choice. I would offer an analogy ... while it is possible to use a table saw to drive nails, it isn't a very good idea. You can find a way to use Access against the data as you now have it organized, but it isn't a very good idea either. As Pat pointed out, you can go through all the work to get your queries and forms and reports and code built to handle the current data structure, but you will have to re-do everything when a new test is added, given your current data structure. Best of luck on your project Jeff Boyce Microsoft Office/Access MVP "Redwood" wrote in message ... I need to add this new project into a existing database and have to keep the structure of this new project is consistent with other project in the database for future analysis and maintaince. The access designed to allowed have 255 fields in one table for maximum. If it only can handle 20 to 30 fields, people will think about it very carefully before using Access. "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). |
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 |