Table problem

[Redwood]

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.

 

[mnature]

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]

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.

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

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.

 

[mnature]

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.

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.

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

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.


:

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
--



:

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.





--



:

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!

:

On Wed, 29 Mar 2006 13:33:03 -0800, Redwood

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]

 

[Redwood]

Thank you very much. I will try it and be back with question about normalized
table next week.

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.

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.

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

:

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.


:

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
--



:

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.





--



:

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!

:

On Wed, 29 Mar 2006 13:33:03 -0800, Redwood

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]

 

[John Vinson]

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]

 

[Pat Hartman\(MVP\)]

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.

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.

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

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.


:

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
--



:

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.





--



:

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!

:

On Wed, 29 Mar 2006 13:33:03 -0800, Redwood

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]

 

[Redwood]

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!

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.

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.

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

:

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.


:

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
--



:

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.





--



:

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!

:

On Wed, 29 Mar 2006 13:33:03 -0800, Redwood

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]

 

[Redwood]

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

 

[Duane Hookom]

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
--

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!

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.

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.

:

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

:

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.


:

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
--



:

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.





--



:

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!

:

On Wed, 29 Mar 2006 13:33:03 -0800, Redwood

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]