Calculate difference from last value

[dilbert]

I am working on a Biggest Loser competition at work. I'm entering the date and weight in a form and saving it to it's own table. Then I query that data and print it on a report. It was pretty easy to get the date in the right order and output the weights. What I would also like is to display the change since their last weight. I am not storing this in the database, I suppose I could, but it seemed better to calculate it when needed, i.e. in case I made a data entry error and needed to edit a weight. I suspect this is not easy.

This is how I'd like it to look.

Date	Weight
11/1/2015	220
11/5/2015	218	-2
12/1/2015	218	0
12/22/2015	219	1
1/5/2016	215	-4

This is what the (early draft) report looks like. It's in Access 2016.



Any guidance is appreciated! And please be gentle, I'm an Access beginner.

[June7]

You need to pull value from previous record. Review: http://allenbrowne.com/subquery-01.html#AnotherRecord

[dilbert]

Wow, thank you for the fast reply!

I'm close, but clearly did something wrong.

Here's the query:

SELECT Weights.Weight, Weights.DateOfWeight, (SELECT TOP 1 Dupe.Weight
FROM Weights AS Dupe
WHERE Dupe.Contacts_ID = Weights.Contacts_ID
AND Dupe.DateOfWeight < Weights.DateOfWeight
ORDER BY Dupe.DateOfWeight DESC, Dupe.Contacts_ID) AS Change
FROM Weights
WHERE (((Weights.Contacts_ID)=[Forms]![Contact Details]![ID]))
ORDER BY Weights.DateOfWeight;

Here's the output.

Weight	DateOfWeight	Change
112	11/11/2015
155	11/20/2015	112
212	12/21/2015	155
210	12/28/2015	212
208	1/1/2016	210
205	1/8/2016	208
202	1/16/2016	205

[June7]

No, it is correct. All the example does is pull the prior value, doesn't subtract it. Now that you have the value from previous record, subtract it. Do it in query or in textbox on report.

SELECT Weights.Weight, Weights.DateOfWeight, Weight - (SELECT TOP 1 Dupe.Weight
FROM Weights AS Dupe
WHERE Dupe.Contacts_ID = Weights.Contacts_ID
AND Dupe.DateOfWeight < Weights.DateOfWeight
ORDER BY Dupe.DateOfWeight DESC, Dupe.Contacts_ID) AS Change
FROM Weights
WHERE (((Weights.Contacts_ID)=[Forms]![Contact Details]![ID]))
ORDER BY Weights.DateOfWeight;

[dilbert]

Thank you so very much! I really appreciate your help, this is a great addition to the report.