Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtracting weights (97)

    I am working with an office to design some reports.They have a database to track employees visits for improving their health. They have a table that tracks their weight . Each enty is by visit number and there is a date and their current weight. I need to produce a report that will show the beginning weight and at each visit the amount lost or gained since the previous visit (running total with amount decrease or increase). Each person has a different number of visits.

    It is Sat and my mind is not working. Please help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtracting weights (97)

    Do you mean that the table contains a visit number that starts at 1 for each employee? Could there ever be gaps in the sequence of visit numbers for a particular employee?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtracting weights (97)

    No gaps. Every employee starts with 1. Some have more and some had less visits. The data entry clerk manual enters the visit number and it is sequential. All visits are display on screen so she can see the next number.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtracting weights (97)

    You can do this in a query using DLookup. I have attached a simple example that demonstrates how. First look at the table, then open the query in design view to see how the two calculated columns are defined.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtracting weights (97)

    I have attached a version with another query that uses two copies of the table with an outer join. This one should work faster than the one using DLookup, especially for large tables, but unlike that one, it is not updateable. That shouldn't be a problem if you want to use the query for a report, though.
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtracting weights (97)

    Thanks for your help. The Dlookup worked perfectly and since the database is small, I think I will stick with it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •