query to compare data with previous record 2

[markbrum]

Hi,

I'm putting together a simple project which records peoples weight loss/gain, they are measured from time to time but I need a query to compare with the previous value in a report and don't know how to calculate the loss/gain field, as below.

id name date waist loss/gain chest loss/gain
1 dave april 36" 42"
2 dave may 37" +1" 41" -1"
3 dave june 36" -1" 41" 0
4 dave july 35" -1" 40" -1"

I can't use the date, other than to say the previous record by date, as measurements could be taken randomly.

Thanks for looking,
Mark.

 

[ByteMyzer]

Assuming the table name Table1, try the following query:
[tt]
SELECT T1.ID, T1.NAME, T1.DATE, T1.WAIST & '"' AS WAIST, Format(T1.WAIST-(SELECT T2.WAIST FROM Table1 AS T2 WHERE T2.ID=(SELECT MAX(T3.ID) FROM Table1 AS T3 WHERE T3.ID<T1.ID AND T3.NAME=T1.NAME,"+0'';-0'';0''") AS [WAIST LOSS/GAIN], T1.CHEST & '"' AS CHEST, Format(T1.CHEST-(SELECT T4.CHEST FROM Table1 AS T4 WHERE T4.ID=(SELECT MAX(T5.ID) FROM Table1 AS T5 WHERE T5.ID<T1.ID AND T5.NAME=T1.NAME,"+0'';-0'';0''") AS [CHEST LOSS/GAIN]
FROM Table1 AS T1
ORDER BY T1.ID;
[/tt]

You can use a search-and-replace to replace all instances of Table1 in this query with your actual table name, and as long as the table in question has the fields and data as you outlined in your example, this query should work.

Note: the above query assumes that the WAIST and CHEST fields are numeric. If, however, they are actually text fields that store data like 36", then the query will need to be modified thus:
[tt]
SELECT T1.ID, T1.NAME, T1.DATE, T1.WAIST, Format(Val(T1.WAIST)-(SELECT Val(T2.WAIST) FROM Table1 AS T2 WHERE T2.ID=(SELECT MAX(T3.ID) FROM Table1 AS T3 WHERE T3.ID<T1.ID AND T3.NAME=T1.NAME,"+0'';-0'';0''") AS [WAIST LOSS/GAIN], T1.CHEST, Format(Val(T1.CHEST)-(SELECT Val(T4.CHEST) FROM Table1 AS T4 WHERE T4.ID=(SELECT MAX(T5.ID) FROM Table1 AS T5 WHERE T5.ID<T1.ID AND T5.NAME=T1.NAME,"+0'';-0'';0''") AS [CHEST LOSS/GAIN]
FROM Table1 AS T1
ORDER BY T1.ID;
[/tt]

 

[markbrum]

Thanks v much,

That works great, they are stored as values as in the first example, only one small thing, the weights could have decimal places ie 15.5 or 16.75 etc. this query ignores anything past the decimal point. I can't work out how to change it, can you help please.

Thanks again, Mark.

 

[ByteMyzer]

Piece of cake. In the Format criteria in both places in the query, change:
[tt]
"+0'';-0'';0''"
[/tt]

...to:
[tt]
"+0.##########'';-0.##########'';0.##########''"
[/tt]

This will give you data up to 10 decimal places.

 

[markbrum]

Again, perfect.

Thanks!