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]

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]

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

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

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]

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

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]

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

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]

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