MultiValed Fields

[Guest]

I could be way off here, but I have over 60 fields (numeric fields that are
scored 1-100) which represent observations of specific items that occur (are
repeated) at different date-times for multiple subjects (SubID). The numeric
fields are defaulted to be zero and I am having diffculty, for example, with
selecting all fields >1 or >30 or >60 without querying them one at a time.

Does it make sense for me to make a table and place all of these fields in a
multi-valued field so I can then query the field for those criteria?

Thanks

 

[Guest]

No: don't use MVF for that.

Whatever entity you are observing, it seems that you make several
observations over time. There is therefore a one-to-many relation between
your subjects and your observations. The observations should therefore be
stored in a related table, so one subject can have many observations.

The Observation table would have fields like this:
ObservationID AutoNumber primary key
SubID Number refers to Subject.SubID
ObsDateTime Date/Time when the observation was taken
ObsValue Number? what the reading was.
There may also be other fields. For example the might be an ObsTypeID field
if you have different types of observations to make.

 

[Guest]

Hi Allen,
I just realized something that may be causing me a problem. In my search for
info I just read an Access Web article "The Ten Commandments of Access and
realized that I have broken a commandment--I used lookup table in my
observation table for the number values (1-100) that users (Observers) select
when they make an observation measurement for each of the symptoms being
observed. Is that a problem?

 

[Guest]

As you suggest my Observation Table has the following fields but instead of
an obsValue field I have each of the symptoms as (60+) number fields that are
rated 1-100.

ObservationID AutoNumber primary key
SubID Number refers to Subject.SubID
ObsDateTime Date/Time when the observation was taken

the I have, for example:
Allergy Number 1-100
Tics Number 1-100
etc.......so I can't seem to query them as a group.

Thanks

 

[John W. Vinson]

As you suggest my Observation Table has the following fields but instead of
an obsValue field I have each of the symptoms as (60+) number fields that are
rated 1-100.
the I have, for example:
Allergy Number 1-100
Tics Number 1-100
etc.......so I can't seem to query them as a group.

Thanks

You have a perfectly classical many to many relationship.

Each Subject has many Conditions.
Each Condition may affect many Subjects.

The proper table structure is:

Subject
SubID
<biographical data>

Conditions
CondID
Condition <e.g. "Tics", "Allergy">

SubjectConditions
SubID <link to Subject>
CondID <link to Conditions
ObsDateTime <when this subject was observed with this condition>
Intensity <1-100>


John W. Vinson [MVP]

 

[Allen Browne]

Bill, there is no question about it: the structure John has suggested will
be the way to go.

 

[Guest]

Thanks!

Bill, there is no question about it: the structure John has suggested will
be the way to go.