Subtracting weights (97)

Topic

New Reply

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.

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote