finding a difference of list of numbers

[LTOSH]

I have a report that is a list of measurements that are taken from measuring
different areas of the body...ex...shoulder width, bicep, chest etc. I am
wanting to list each measurement then at bottom of report show the
difference...whether the measurement indicates loss or gain. how do i do
this?

Thanks!

 

[Steve]

By loss or gain, do you mean from the last time the measurements were taken?

Steve
(e-mail address removed)

 

[LTOSH]

say for example on 06/05 i measured your bicep at 14 inches...on 07/05 i
measured your bicep at 13.5...on 08/05 i measured your bicep at 14.5.
i want to see a total that shows me the loss or gain each time a new
measurement is documented...so to answer your question i guess it would be
the last measurement compared to the original/base measurement.

could there also be the ability to show the loss or gain between each month
of measurements in a seperate column??...for example from 06/05 - 07/05
there was a loss of .5 however from 07/05-08/05 there was a gain of 1.

 

[Steve]

I would say you need to design crosstab queries to retrieve the data in the
way you want and a crosstab report to display the data. Shoulder width,
bicep, chest, etc need to go down the left side and 06/05 to 07/05, 07/05 to
08/05, etc need to go across the top. The first step is to create a proper
set of tables. I recommend:
TblBodyPart
BodyPartID
BodyPart

TblBodyPartMeasurement
BodyPartMeasurementID
BodyPartID
MeasurementDate
Measurement

To get the data for the crosstab query you need two queries that include
both of the above tables. Yes, two queries that look alike so you can set
different criteria for the measurement date. In the first query you need to
set the criteria for the MeasurementDate as 06/05. At the same time, in the
second query you need to set the criteria for the MeasurementDate as 07/05.
You need to design a dynamic way to input these dates. In the next iteration
the criteria in the first query needs to be 07/05 and at the same time the
criteria in the second query needs to be 08/05. Succeding iterations need to
work the same way. The two queries need to be joined in a union to get the
measurement for 07/05 minus the measurement for 06/05, 08/05 minus 07/05,
etc. The union query needs to be transformed into a crosstab query to serve
as the recordsource for the crosstab report.

Steve
(e-mail address removed)