I am building a Biggest Loser database for a competition at work using Access 2016. I am getting close to being done. One element I'd like to have is to show the current weight loss / gain by an individual and total for the whole group. I am storing the date, weight and ID in one table.
Part 1
How can I query to difference between the first weight and the most recent weight and display that on the report. For their first weigh-in, the first and last will be the same and therefore the difference will be 0. That's fine.
ID Contacts_ID Weight DateOfWeight 25 6 205 1/20/2016 27 6 204 1/27/2016 28 6 202 1/31/2016 29 6 200 2/5/2016 30 6 195 2/12/2016 31 7 165 1/20/2016 32 7 163 1/27/2016 33 7 160 1/31/2016 34 7 158 2/5/2016 35 7 155 2/12/2016 36 6 170 3/1/2016 37 6 160 3/4/2016
Part 2
I'd also like to get the groups starting weight and the groups ending weight and display that on the report too.
In the example below there are only 2 users, I anticipate about 20 - 25 users when I go live.
I want to add rows 25 and 31 and subtract the sum of rows 36 and 37.
ID Contacts_ID Weight DateOfWeight 25 6 205 1/20/2016 27 6 204 1/27/2016 28 6 202 1/31/2016 29 6 200 2/5/2016 30 6 195 2/12/2016 31 7 165 1/20/2016 32 7 163 1/27/2016 33 7 160 1/31/2016 34 7 158 2/5/2016 35 7 155 2/12/2016 36 6 170 3/1/2016 37 6 160 3/4/2016
I am an Access novice, so explanations are appreciated.
Thank you!