Scoring a complicated validated tool for outcomes research

[Sydneyej]

Hello! I'm a n00b at Access and trying to learn by doing. I have a
very specific task I'm trying to accomplish and it's very complicated
(to me, at least). I'm hoping this is one of those "oh, but the
solution is so simple!" things that you just can't know as a
neophyte.

I'm trying to build an outcomes research database that uses the scores
from validated severity measurement tools at different points in care
(pre-treatment, post-surgery, post-PT). One of the tools uses a matrix
with two different values for each of 20 squares, with no pattern to
the values. I will try to draw a picture here to help. I'll call the
symptoms (sx) A, B, C and D. The frequencies will be 0, 1, 2, 3 and 4.
Each cell contains 2 values from the two different weighting systems.
We'll call them "Layperson Weights" and "Expert Weights," and they're
represented in each cell as Layperson/Expert.

Sx | 1 | 2 | 3 | 4 | 0

-----------------------------------------------------------------------
A | 10/9 | 8/7 | 5/9 | 6/6 | 0/0
B | 11/13 | 10/15 | 7/12 | 7/8 | 0/0
C | 18/19 | 16/18 | 17/13 | 16/15 | 0/0
D | 19/18 | 17/17 | 15/10 | 12/10 | 0/0

The data is entered as a frequency for each symptom by patient. It's a
self-report survey, so nulls (represented as "x" below) are allowed as
some patients just don't fill them out very completely.

PtID | A | B | C | D
-------------------------------------------------
1 | 4 | 3 | x | 0
2 | 4 | 2 | 1 | 2
3 | 1 | 0 | 1 | 0

The severity index is calculated as a sum of the weighted scores for
each symptom. As there are two weighting systems, there are (in the
end) two scores. So "PtID 2" would have the following score:

Sx A, Freq 4 = 6/6
Sx B, Freq 2 = 10/15
Sx C, Freq 1 = 18/19
Sx D, Freq 2 = 17/17

"Layperson Weighting": 6+10+18+17=51
"Expert Weighting": 6+15+19+17=57

How do I get Access to calculate this for me? Right now I have the
following table structure:

Patient Table
PtID, Name, Demographics, Medical Record Number

Patient Score Table
PtScoreID, PtID, Sx A, Sx B, Sx C, Sx D (where the data for each
sx is the frequency)

Scoring Table
ScoreID, Sx, Freq, Layperson Weight, Expert Weight

Am I even at the right starting point? I'm very interested in learning
how to think about this as well as how to actually do it - so far it's
pretty much just crushed my brain. Any help would be greatly
appreciated!

Thank you!
Sydney

 

[John Spencer]

I would certainly set up the tables a bit differently. For one thing you
should never store two pieces of information in one field.
For another, you should not have the same type of information stored in
multiple fields Sx1, Sx 2, etc..

Patients table - no change

Scores Table
--PatientID
--SXType
--RaterType (Patient or Expert)
--Score

Scoring Table
-- SXType
-- RaterType
-- Score
-- Weight

--

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michel Walsh]

The "Patient Score Table" seems suspicious.

Rather than:

PtScoreID, PtID, Sx A, Sx B, Sx C, Sx D


I would examine the possibility to use

PtScoreID, PtID, Type, Sx



and get FOUR rows (one for Type = "A", the Sx value being your original Sx A
value; one for type="B", and so on).

Note that the type can, may be, two fields, one for the A, B, C, D ... and,
a second field, to differentiate if it is about the "Layperson Weights" or
about the "Expert Weights".


Note that this VERTICAL representation of the data, while fine for
computation, is not too user friendly for human reading, but a TABLE is not
for human reading! You can transform the vertical representation into your
horizontal representation with a special query, a CROSSTAB query, and then,
you can built a FORM to expose that query for human reading!



Hoping it may help,
Vanderghast, Access MVP

 

[Sydneyej]

Thank you! So when I set up the cross tabs query, what do I put for
the rows and what for the columns? The last x-tab query I tried with
this data had PtID as the rows and Sx as the column, so I got SxA1,
SxA2, SxA3... as my column headings, which was not helpful as there
was only one SxA.

I think easily the hardest thing for me here is that this is
essentially like learning a foreign language: I'm learning the
language at the same time as learning *how* to learn a language. It's
great to get advice that helps me learn what questions I should really
be asking.

 

[Michel Walsh]

The rows are given in the GROUP, and the columns are the PIVOT. I would use
the 'type' I described earlier as Pivot to have A, B, C, ... as new
columns. You can use the query wizard to help you.


Hoping it may help,
Vanderghast, Access MVP

 

[Sydneyej]

Thanks for the suggestions! I will give them a try!