Results 1 to 6 of 6
  1. #1
    dilbert is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Aug 2010
    Posts
    21

    Calculate difference in weight between first and last date?

    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!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't think will be simple.

    Here is example of pulling value from another record to do a calculation: http://allenbrowne.com/subquery-01.html#AnotherRecord

    Your requirement is complicated because you don't want the preceding value of raw data. You want to pull weights based on criteria of another field - the max and min dates.

    First need a query that pulls the records where DateOfWeight matches the oldest and newest dates for each person http://allenbrowne.com/subquery-01.html#TopN. Probably need a couple of TOP N queries then UNION those queries.

    Then a third query using the UNION that pulls value from preceding record (the first Allen Browne example).

    Separate set of queries for the group calcs.


    Or maybe domain aggregates could do the same as nested subqueries but they can be slow performers in query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    dilbert is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Aug 2010
    Posts
    21
    Quote Originally Posted by June7 View Post
    Don't think will be simple.
    Nothing worth doing ever is.

    I did a little searching and modified a query I found for the individual losses.
    SELECT Weights.Contacts_ID, Min(Weights.Weight) AS MinWeight, Max(Weights.Weight) AS MaxWeight
    FROM Weights
    GROUP BY Weights.Contacts_ID;
    Then on the report I just subtract the min from the max.

    Now I need to figure out the group weight. So a total of each first weight and a total of each last weight.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Pulling Max and Min does not guaranty you get you the first and last by date.

    You might hope the Max weight to be from the earliest date and the Min weight from the latest date but that might not always be true. Do you want to assume it will be?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    dilbert is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Aug 2010
    Posts
    21
    Quote Originally Posted by June7 View Post
    Pulling Max and Min does not guaranty you get you the first and last by date.

    You might hope the Max weight to be from the earliest date and the Min weight from the latest date but that might not always be true. Do you want to assume it will be?
    So close... No, a person could gain weight, so clearly my calculations would be wrong.

    Can I modify that query to get the min and max date's weight values? It seemed too easy to be true.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you study the Allen Browne links?

    Read post 2 again. It roughly outlines what I think needs to be done.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculate difference from last value
    By dilbert in forum Reports
    Replies: 4
    Last Post: 01-17-2016, 08:06 AM
  2. Calculate time difference
    By desireemm1 in forum Access
    Replies: 19
    Last Post: 10-27-2014, 12:06 PM
  3. calculate total weight from subform
    By msasan1367 in forum Access
    Replies: 1
    Last Post: 08-05-2013, 12:06 AM
  4. Replies: 1
    Last Post: 02-12-2013, 03:48 AM
  5. Replies: 0
    Last Post: 08-07-2008, 07:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums