Formula to identify a record based on first of the day but not

[Fergge]

I am in need of a formula or expression to run a comparison of a field in different records but from the same table. I want to select a record from my "WeighIn" table and then have its "weight" column compared to the "weight" column in the most recent record that is also the first record recorded that day that is not also the record initially selected.

If players only weighed in once a day then it would be easy, but some players weigh in twice a day and some 3 times a day. That is where my problem arises.
For example, Joe weighs in 3 times each day. Yesterday he weighed in at 8am 1030am and 4pm. Today he weighed in once at 8am, once at 11am and once at 2pm. In the "weighIn" table I have recorded his PlayerID, his Weight and TheDate. The comparison formula would then take today's 8am and compare it to yesterdays 8am, today's 11am would be compared to today's 8am, today's 2pm would also be compared to today's 8am.

 

[MarkK]

In the "weighIn" table I have recorded his PlayerID, his Weight and TheDate.

Does the "TheDate" field contain a time? If not there is not enough info to compare times. If there is a time included, I would change the name of the field from "TheDate."

The comparison formula would then take today's 8am and compare it to yesterdays 8am, today's 11am would be compared to today's 8am, today's 2pm would also be compared to today's 8am.

What are the rules here? Why compare 8am to yesterday's, and today's 11am to todays 8? These rules need to be very explicit. And then what if there is no 8am weigh-in yesterday? So there need to be rules for how this works, and then fallback rules for if the preferred rules fail.

 

[Fergge]

Mark,

I have posted this discussion in multiple places due to not knowing if it was a Forms question or a query, and it appears you are helping me in both sections. Please let me know which thread would be the best to continue in and I will be glad to do so.

 

[MarkK]

It seems to me there are different topics on the different threads, so it is your call. They are your threads.

 

[isladogs]

First of all, I'd change the date field name to e.g. WeighDate
Next I think you need an extra integer field WeighInNumber so for the first weigh in, that field = 1 etc.
You also need a field which I'll call WeightChange to record the difference

Then something like this should work though its not particularly tidy...

Select Case WeighInNumber

Case 1
	WeightChange = DLookup("Weight","YourTableName","PlayerID = " & Me.PlayerID & " And WeighDate= #" & Date & "# And WeighInNumber=1") - 
		DLookup("Weight","YourTableName","PlayerID = " & Me.PlayerID And WeighDate= #" & (Date-1) & "# And WeighInNumber=1") 

Case 2
	WeightChange = DLookup("Weight","YourTableName","PlayerID = " & Me.PlayerID & " And WeighDate= #" & Date & "# And WeighInNumber=2") - 
		DLookup("Weight","YourTableName","PlayerID = " & Me.PlayerID And WeighDate= #" & Date & "# And WeighInNumber=1") 

Case 3
	WeightChange = DLookup("Weight","YourTableName","PlayerID = " & Me.PlayerID & " And WeighDate= #" & Date & "# And WeighInNumber=3") - 
		DLookup("Weight","YourTableName","PlayerID = " & Me.PlayerID And WeighDate= #" & Date & "# And WeighInNumber=1") 
End Select

NOTE:
a) I've assumed your PlayerID is a number - otherwise replace with - "PlayerID = '" & Me.PlayerID & "'
b) You could probably combine cases 2 & 3 using a Case Else statement
c) If you just want to know if the value has gone up/down or unchanged, then change the above to use IIf statements e.g. something like:

Case 1
	WeightChange = IIf(DLookup("Weight","YourTableName","PlayerID = " & Me.PlayerID & " And WeighDate= #" & Date & "# And WeighInNumber=1") - 
		DLookup("Weight","YourTableName","PlayerID = " & Me.PlayerID & " And WeighDate= #" & (Date-1) & "# And WeighInNumber=1")>0,"More", _
		IIf(DLookup("Weight","YourTableName","PlayerID = " & Me.PlayerID & " And WeighDate= #" & Date & "# And WeighInNumber=1") - 
		DLookup("Weight","YourTableName","PlayerID = " & Me.PlayerID & "And WeighDate= #" & (Date-1) & "# And WeighInNumber=1")<0,"Less","Same")

etc for other cases.

d) You will also need to add error trapping to handle situations where no weigh in was done earlier or on previous day....

Colin

 

[Fergge]

We can stay on this thread for now as I think you have got me headed in a better direction.

TheDate is a time and date field but I have been using the record ID to identify the first record of the day. I am running a Max on Date and a Min on Record ID. The problem is that the most recent weight today is also the result returned from my query.

The reason for the comparison needing to be against the "First of a Day" is because we are looking to determine if a player is recovering/re-hydrating. Comparing to a weigh in other than the first one of a day gives misleading information because the players second weigh in is always lower than his initial. The first weigh in of the day is the benchmark that we expect a player to be recovering to.

 

[isladogs]

I hadn't seen the other thread or replies when I wrote the above.
So for example, I haven't included dealing with no weigh in on Sat/Sun
However that's one example of the need for error trapping

I agree with all of MarkK's comments

 

[Fergge]

Ridders,

Thank you for the information. I had began this quest looking for a way to capture data in a form and then make it static, but it looks like the best option is to create a formula or expression to run the comparison on request.

 

[isladogs]

You're welcome.
If you found that info useful, please click the Thumbs Up button or 'tip the scales'

Colin

 

[MarkK]

cross posted: http://www.utteraccess.com/forum/index.php?showtopic=2043210&hl=