B
Bill
I appreciate the value of this helping community!
I have a question that follows an earlier response that helped me in dealing
with a many to many relationship but has me currently tangled up in my
re-design.
I have a medical observation database for many subjects, with specific
symptoms that are repeatedly measured, sometimes by different observers. A
previously created FrontPage form exists wherein specific medical history is
obtained (will be phased out and replaced with an Access form) and the 60
plus symptoms are first presented showing a default value of zero. The
observer identifies the symptoms present and selects a value from 0-100,
representing the percentage of time the symptom is present. I have imported
that data into my current database and each of the values is a field.
As a result, when I first created my Access 2007 database I imported this
data into a table (tblHistory). I then queried the tblHistory selecting the
symptoms (really the symptom values) to create an observation table
(tblObservation) that then received subsequent repeated symptom observations.
In attempting to correct the design of my database I decided I needed a
Symptom field and created TblSymptoms—here is where I stumble- My imported
fields are numeric (although they each represent a specific symptom). Would
the following table design make sense?
TblSymptoms
SymptomID
Symptoms Text
?SymptomValue? Numeric
When I treat each symptom value as a field, as I have, I have been able to
create a form that displays each of the symptoms labeled with a combo box
offering the default of zero or any value to 100. But, with this design I ’m
unable to query Symptoms as a field. If I use the TblSymptom design is there
a proper way to link the value selected to the textually defined symptom ?
My form, with each symptom an individual field, of course, doesn’t return
the textual symptom description in a single symptom field. Should intensity
of the symptom be its own field? If so would I place the intensity field as a
field in the symptom table so they are related or would the intensity field
be in the TblSubjectSymptoms as shown below.
Should the symptoms be individual fields and if not should intensity be a
field in a Symptoms table or in the SubjectSymptoms table? Thanks for any
thoughts.
TblSubjects
SubID
<biographical data>
TblObservers
ObserverID
<biographical data>
TblSymptoms
SymptomID
Symptoms <e.g. "Tics", "Allergy">
TblSubjectSymptoms
SubID <link to Subject>
ObserverID<link to Observer
ObsDateTime <when this subject was observed with this condition>
SymptomID <link to Symptoms ----??
Intensity <1-100>??
I have a question that follows an earlier response that helped me in dealing
with a many to many relationship but has me currently tangled up in my
re-design.
I have a medical observation database for many subjects, with specific
symptoms that are repeatedly measured, sometimes by different observers. A
previously created FrontPage form exists wherein specific medical history is
obtained (will be phased out and replaced with an Access form) and the 60
plus symptoms are first presented showing a default value of zero. The
observer identifies the symptoms present and selects a value from 0-100,
representing the percentage of time the symptom is present. I have imported
that data into my current database and each of the values is a field.
As a result, when I first created my Access 2007 database I imported this
data into a table (tblHistory). I then queried the tblHistory selecting the
symptoms (really the symptom values) to create an observation table
(tblObservation) that then received subsequent repeated symptom observations.
In attempting to correct the design of my database I decided I needed a
Symptom field and created TblSymptoms—here is where I stumble- My imported
fields are numeric (although they each represent a specific symptom). Would
the following table design make sense?
TblSymptoms
SymptomID
Symptoms Text
?SymptomValue? Numeric
When I treat each symptom value as a field, as I have, I have been able to
create a form that displays each of the symptoms labeled with a combo box
offering the default of zero or any value to 100. But, with this design I ’m
unable to query Symptoms as a field. If I use the TblSymptom design is there
a proper way to link the value selected to the textually defined symptom ?
My form, with each symptom an individual field, of course, doesn’t return
the textual symptom description in a single symptom field. Should intensity
of the symptom be its own field? If so would I place the intensity field as a
field in the symptom table so they are related or would the intensity field
be in the TblSubjectSymptoms as shown below.
Should the symptoms be individual fields and if not should intensity be a
field in a Symptoms table or in the SubjectSymptoms table? Thanks for any
thoughts.
TblSubjects
SubID
<biographical data>
TblObservers
ObserverID
<biographical data>
TblSymptoms
SymptomID
Symptoms <e.g. "Tics", "Allergy">
TblSubjectSymptoms
SubID <link to Subject>
ObserverID<link to Observer
ObsDateTime <when this subject was observed with this condition>
SymptomID <link to Symptoms ----??
Intensity <1-100>??