One to One relationships

[Pandora]

I work at a health center where there are numerous research studies
done. Part of my job is software support, and while I keep reading
everywhere that one-to-one relationships are rare, I keep encountering
needs for them here.

For example, the project I'm currently trying to help with is for a
study where a patient comes in once a month and takes the same series
of exams for a year. We want to keep each exam in its own table, but
we'd also like to be able pull out the results by time period (all 3
month results).

I have the tables set up:
- Patient (Demographics)
- Evaluation (Date of Eval, Patient, Eval(0w,4w,8w...))
- Exam 1
- Exam 2
- Exam 3... Exam 18

One Patient has Many Evaluations
One Evaluation Leads to One Exam 1 (join type-all eval)
One Evaluation Leads to One Exam 2...

This logically makes sense to me, but I cannot get it dang thing to
cooperate with me. When I pull in Patients, Evals and an exam into a
query, it will let me update the data. However, as soon as I add a
second exam, i start getting the "You cannot add or change a record
because a related record is required in table 'evaluation'".

I've even included the eval keys from the exam tables, I'm seeing them
automatically fill in as I change the data, but I guess it's not
happening fast enough...?

Suggestions?

 

[Douglas J. Steele]

I don't see the purpose of separate tables for Exam1 through Exam18. Have a
single Exam table, with an additional column that indicates which exam it
is.

I also don't understand what you mean by

I've even included the eval keys from the exam tables, I'm seeing them
automatically fill in as I change the data, but I guess it's not
happening fast enough...?

I'm assuming (from the error) that the Exam table(s) need to point to an
existing Evaluation.

To give more advice, you'll have to provide more details as to what the
fields are in each of the tables.

 

[Pandora]

I'm sorry, you are right, I wasn't very clear. The exams are each going
in their own table because most of them have over 150 questions. Since
Access has a limit of 255 fields we decided to keep each exam in its
own table.

I have worked on several similar databases, and we've been able to pull
everything together into beautiful working harmony. What I was looking
for advice on was the ability to have mutliple one on one realted
tables pull together in an updateable query.

I am pretty sure it's not possible.

Against my advice this customer wants to have a totals column in the
tables. The study is complete and they are trying to compile the data.
Right now, his boss's are mostly interested in the totals, so he
wanted to be able to enter the totals across all the tables at once.

I am meeting with him this afternoon. I am going to suggest that we
make a new table for just the totals. When he finally enters in his
true data points we can then use his "totals" table to error check the
data entry.

If anyone out there has better advice, I'm open to it.

Pandora Cowart
Training Specialist
University of Florida

 

[Amy Blankenship]

Wow, it sounds like you need to read up on normalization.
http://support.microsoft.com/kb/q100139/
http://webmonkey.wired.com/webmonkey/99/13/index1a_page2.html?tw=backend

Each question should be its own record, not a column in a table. The
responses should have their own table. The totals should be aggregated from
the data entered in the tables. They should not need to be entered at all.
I'm not sure how entering hand-calculated totals will check against data
entry errors in the individual data points, since an error could be made on
either side. But I guess you could create a structure that does that.

You can download a sample database design that's heading in the direction
you want from here http://www.eurotaac.com/2005_files.asp (see the database
basics presentation) or you could look at At your Survey
http://www.rogersaccesslibrary.com/OtherLibraries.asp here for an example of
a fully working survey application (I haven't actually looked at the second
one, but it is often held up as a good example of how to do this sort of
thing).

HTH;

Amy

 

[Pandora]

Thank you, that is a much more elegant solution.

 

[Amy Blankenship]

You're welcome

-Amy