Formula for daily weight gain or loss

[JimO]

I am tracking body weight and wish to record weight gain or loss on a dailt basis as well as weekly. My table lists the date a weight was posted and the weight. Not sure of the best way to set it up for daily and weekly changes. Any help or direction would be welcome.
Thanks, Jim O

[Cottonshirt]

Not sure of the best way to set it up for daily and weekly changes.

there is a DLookup() function that allows you to look up a value in a table, and return the value of another field in the same record. since each record in your table will have the date, you can use DLookup() to look up the previous day's date, which would simply be [todaydate]-1

this would give you something like this:

DLookup("weight" , "TableName" , "date = [todaydate]-1")


for weekly changes, you can just change the -1 to -7, which would give you the weight one week before.



good luck with your project,


Cottonshirt

[JimO]

Using this in a uery returns blank values

YesterdayWt: DLookUp("Dressed","WeightT","WeightDate = [WeightDate]-1")

The field names are as I have them in the table.

[Cottonshirt]

yes, well, I was kind of expecting you might do a little thinking for yourself.

I would have thought it obvious that there is not going to be a record where the date is equal to the day before. because the date in any one record can only be equal to itself.

what you will probably have to do is run a query that gives you the date in one field, and date-1 in another field, with a different name.

so you have something like this: Dressed, WeightT, WeightDate, Yesterday

then run your Dlookup on that, and ask for:

DLookUp("Dressed","WeightT","WeightDate = Yesterday")



good luck with your project,



Cottonshirt

[June7]

Cottonshirt, did you try that? It won't work. For one thing DLookup is referencing WeightT instead of query.

Have to concatenate the variable input in the WHERE argument.

Intermediate query is not needed:

YesterdayWt: DLookUp("Dressed", "WeightT", "WeightDate = #" & [WeightDate]-1 & "#")

Domain aggregate functions sometimes slow performance. So a query with table self-join might be faster.

SELECT WeightT.WeightDate, WeightT.Dressed, P.PrevDressed
FROM WeightT INNER JOIN (SELECT Dressed AS PrevDressed, WeightDate+1 AS WD FROM WeightT) AS P ON WeightT.WeightDate = P.WD;

Both of those depend on a record for EVERY day, no gaps.

This can also be done with a nested TOP N query. Review http://allenbrowne.com/subquery-01.html#AnotherRecord

[JimO]

Thanks very much for your input. I can fine tune it from that. I do have some gaps but for the majority, every day has a value and I can ignore the few that do not. It is for a daily record so the rare missing data is not an issue.

Thanks again, Jim O

[accesstos]

A solution that covers the gaps:

SELECT 
Min(q.WeightDate) AS CurrDate, 
Min(q.Dressed) AS CurrDressed, 
q.PreDate, q.PreDressed 
FROM (SELECT 
WeightT.WeightDate, 
WeightT.Dressed, 
P.WeightDate AS PreDate, 
P.Dressed AS PreDressed 
FROM WeightT INNER JOIN WeightT AS P 
ON WeightT.WeightDate>P.WeightDate)  AS q 
GROUP BY q.PreDate, q.PreDressed;