Calculate from two records

H

Harley Feldman

I have an Access table with the following structure and some sample values:

PatientID Date Description Value
1 1/2/05 Grip Right 45
1 1/2/05 Grip Left 32
1 1/4/05 Grip Right 45
1 1/4/05 Grip Left 35
1 1/8/05 Grip Right 45
1 1/8/05 Grip Left 40

It shows a patient's grip strength improving on the left hand after surgery moving toward matching the right hand. I would like to calculate Opposite (Grip Left/Grip Right) for each date and wind up with the following results query without writing VBA code if possible:

PatientID Date Description Value
1 1/2/05 Opposite .71
1 1/4/05 Opposite .78
1 1/8/05 Opposite .88

Thanks for the help,

Harley
 
R

Rick B

It seems to me that your data structure is wrong. You should have...

PatientID
Date
GripRight
GripLeft


This would allow you to compare the two values and store a single record for each day.

You may be able to rig something up with your structure, but I don't think it follows normalization rules for your data.

Rick B


I have an Access table with the following structure and some sample values:

PatientID Date Description Value
1 1/2/05 Grip Right 45
1 1/2/05 Grip Left 32
1 1/4/05 Grip Right 45
1 1/4/05 Grip Left 35
1 1/8/05 Grip Right 45
1 1/8/05 Grip Left 40

It shows a patient's grip strength improving on the left hand after surgery moving toward matching the right hand. I would like to calculate Opposite (Grip Left/Grip Right) for each date and wind up with the following results query without writing VBA code if possible:

PatientID Date Description Value
1 1/2/05 Opposite .71
1 1/4/05 Opposite .78
1 1/8/05 Opposite .88

Thanks for the help,

Harley
 
D

Douglas J. Steele

What you're suggesting isn't normalized, Rick. You're hiding data in the
field names, specifically the Right and Left part.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Rick B" <Anonymous> wrote in message
It seems to me that your data structure is wrong. You should have...

PatientID
Date
GripRight
GripLeft


This would allow you to compare the two values and store a single record
for each day.

You may be able to rig something up with your structure, but I don't think
it follows normalization rules for your data.

Rick B


I have an Access table with the following structure and some sample
values:

PatientID Date Description Value
1 1/2/05 Grip Right 45
1 1/2/05 Grip Left 32
1 1/4/05 Grip Right 45
1 1/4/05 Grip Left 35
1 1/8/05 Grip Right 45
1 1/8/05 Grip Left 40

It shows a patient's grip strength improving on the left hand after
surgery moving toward matching the right hand. I would like to calculate
Opposite (Grip Left/Grip Right) for each date and wind up with the following
results query without writing VBA code if possible:

PatientID Date Description Value
1 1/2/05 Opposite .71
1 1/4/05 Opposite .78
1 1/8/05 Opposite .88

Thanks for the help,

Harley
 
R

Rick B

I guess. I could see it either way. I just figured if you had two values that you track (start date/stop date, for example) it would be normal to create two fields for them. Another example might be birthday, anniv. You could build a date 'type' field and store them as separate entries, but typically you would just make two fields.

Not trying to be argumentative, you are the expert, but my original post just seemed more appropriate for the data mentioned.

Assuming his design is normalized, how could he get the report he is asking for? His request seems like a logical use of the data, but his current structure makes it hard to work with.

Rick B



What you're suggesting isn't normalized, Rick. You're hiding data in the field names, specifically the Right and Left part.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Rick B" <Anonymous> wrote in message It seems to me that your data structure is wrong. You should have...

PatientID
Date
GripRight
GripLeft


This would allow you to compare the two values and store a single record for each day.

You may be able to rig something up with your structure, but I don't think it follows normalization rules for your data.

Rick B


I have an Access table with the following structure and some sample values:

PatientID Date Description Value
1 1/2/05 Grip Right 45
1 1/2/05 Grip Left 32
1 1/4/05 Grip Right 45
1 1/4/05 Grip Left 35
1 1/8/05 Grip Right 45
1 1/8/05 Grip Left 40

It shows a patient's grip strength improving on the left hand after surgery moving toward matching the right hand. I would like to calculate Opposite (Grip Left/Grip Right) for each date and wind up with the following results query without writing VBA code if possible:

PatientID Date Description Value
1 1/2/05 Opposite .71
1 1/4/05 Opposite .78
1 1/8/05 Opposite .88

Thanks for the help,

Harley
 
J

James Hahn

Why not? One test is identified by the patient and the date of the test.
The test has two values as its data. Nothing is being hidden, nothing is
redundant. There is no repeating group.

Or are you suggesting the database has to allow for a patient with three
hands?
 
H

Harley Feldman

The data structure that I talked about is a very small subset of a larger database. The field showing Grip Right and Grip Left are two of more than 50 types of data described by name, and the Value field represents the actual value associated with the name in the description field. For each type of procedure in the database, there is a variable combination of data types and values. The database is fully normalized. I was just making the example easy to explain.

Harley
 
D

DebbieG

Harley,

Here's a start anyway:

Create a query (like qryGripLeft) (I didn't know your field names):

SELECT Table1.PatientID, Table1.Date, Table1.Description, Table1.Value
FROM Table1
WHERE (((Table1.Description)="grip left"));

Create another query (like qryGripRight):

SELECT Table1.PatientID, Table1.Date, Table1.Description, Table1.Value
FROM Table1
WHERE (((Table1.Description)="grip right"));

Create another query (like qryOpposite) that uses the above queries:

Query Properties, Unique Values = Yes

SELECT DISTINCT qryGripLeft.PatientID, qryGripLeft.Date,
IIf([qryGripLeft].[Value]<[qryGripRight].[Value],[qryGripLeft].[Value]/[qryGripRight].[Value],0)
AS Opposite
FROM qryGripRight INNER JOIN qryGripLeft ON qryGripRight.PatientID =
qryGripLeft.PatientID
ORDER BY qryGripLeft.Date;

Someone else may have a better solution.

HTH,
Debbie


I have an Access table with the following structure and some sample values:

PatientID Date Description Value
1 1/2/05 Grip Right 45
1 1/2/05 Grip Left 32
1 1/4/05 Grip Right 45
1 1/4/05 Grip Left 35
1 1/8/05 Grip Right 45
1 1/8/05 Grip Left 40

It shows a patient's grip strength improving on the left hand after surgery
moving toward matching the right hand. I would like to calculate Opposite (Grip
Left/Grip Right) for each date and wind up with the following results query
without writing VBA code if possible:

PatientID Date Description Value
1 1/2/05 Opposite .71
1 1/4/05 Opposite .78
1 1/8/05 Opposite .88

Thanks for the help,

Harley
 
D

Douglas J. Steele

Somehow I inadvertently emailed Harley the following, rather than posting
here:

One approach might be a variation on what Rick's suggesting. You can write a
query that takes your data and presents it in the format Rick suggests
without having to denormalize your data.

Something along the lines of the following untested air SQL:

SELECT PatientID, [Date], Sum(
) AS GripRight, Sum(
) AS GripLeft
FROM
(SELECT PatientID, [Date], Value AS Right, 0 AS Left FROM MyTable WHERE
Description = 'Grip Right'
UNION
SELECT PatientID, [Date], 0 AS Right, Value AS Left FROM MyTable WHERE
Description = 'Grip Left')
GROUP BY PatientID, [Date]

(BTW, you should rename the field from Date. That's a reserved word, and
using it for your own purposes can lead to problems)

Doug


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Rick B" <Anonymous> wrote in message
I guess. I could see it either way. I just figured if you had two values
that you track (start date/stop date, for example) it would be normal to
create two fields for them. Another example might be birthday, anniv. You
could build a date 'type' field and store them as separate entries, but
typically you would just make two fields.

Not trying to be argumentative, you are the expert, but my original post
just seemed more appropriate for the data mentioned.

Assuming his design is normalized, how could he get the report he is asking
for? His request seems like a logical use of the data, but his current
structure makes it hard to work with.

Rick B



What you're suggesting isn't normalized, Rick. You're hiding data in the
field names, specifically the Right and Left part.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Rick B" <Anonymous> wrote in message
It seems to me that your data structure is wrong. You should have...

PatientID
Date
GripRight
GripLeft


This would allow you to compare the two values and store a single record for
each day.

You may be able to rig something up with your structure, but I don't think
it follows normalization rules for your data.

Rick B


I have an Access table with the following structure and some sample values:

PatientID Date Description Value
1 1/2/05 Grip Right 45
1 1/2/05 Grip Left 32
1 1/4/05 Grip Right 45
1 1/4/05 Grip Left 35
1 1/8/05 Grip Right 45
1 1/8/05 Grip Left 40

It shows a patient's grip strength improving on the left hand after surgery
moving toward matching the right hand. I would like to calculate Opposite
(Grip Left/Grip Right) for each date and wind up with the following results
query without writing VBA code if possible:

PatientID Date Description Value
1 1/2/05 Opposite .71
1 1/4/05 Opposite .78
1 1/8/05 Opposite .88

Thanks for the help,

Harley​
 
J

James Hahn

You have assumed that the two values are members of a group. They aren't.
What you are suggesting makes as much sense as recording a blood pressure
test with systolic and diastolic readings treated as separate tests.
 
H

Harley Feldman

Debbie,

It took me a little while to get it right as the real query is more complex. However, it is working now.

Thanks for your help,

Harley
 
Top