Interactive Form to display calculations from background query

[Fergge]

I have a database that is used to track health/performance factors of high school football players. During the summer months we are concerned about proper hydration recovery from day to day and thus weigh in our players every day. What I would like to have is a function to notify me instantly via message if someone has not properly recovered. I am including snips of the database forms, tables and queries that I am currently using to perform this function. The problem is that I have to enter all the data, then open up the query that contains the calculation formula and then see who has failed to recover. What I would like is for the calculation to be performed in the background when I enter the weight into the form, then if the player has recovered, turn a field green for instance, but if he has not recovered, put a message up on the screen with his name and his calculation. All attempts at this have failed as it appears that the data is not updated to the table until the form is closed out completely. I have tried several macros but so far have had no luck. One area that I am fearful of is that I am using yesterdays weighIn and the most "Recent" weighIn to perform the calculation. When we are off for the weekend this will not work out correctly, also if they miss a day then there will be no yesterday data. I tried setting this up using "last" record and "previous" record but after you update the weighIn today form, today's weight becomes both the previous and the last. Any assistance on this is greatly appreciated.

 

[jdraw]

You might consider using weighinDate, and use the most recent(A) and the second most recent(B) to do your calculation. Using today and yesterday requires that those dates exist. I may not have understood your intent.

It could be that it isn't the database that is at issue, but the procedures for updating/recording various facts.

In general:
For the latest weighindate, you could use top 1 or Max weighinDate, for second most recent

select max weighin date for playerX where WeighinDate not =(select top 1 weighindate for playerX)