Return Most Recent Value

[Sheri Emery]

I am wondering if there is a way to write an expression
that would cause the most recent update to be plugged into
one of the fields on my form/table. I have a Client table
that includes demographic data and other types of
information about each participant that we see. I also
have another table (Supervision Level) table that records
Supervision Level for each client (a client may go through
several levels of supervision before completing the
program). I would like the Supervision Level on the
Client table to be filled with the most recent supervision
level from the Supervision Level table. Basically, if the
client moved from a Level 3 to a Level 4, I would like the
Client table to indicate that the client is on level 4.
This is important because several reports are grouped
based on the level of supervision. A few weeks ago, a
couple of individuals gave me some advice about this thing
regarding DMAX and MAX, but I must have missed something
as I attempted to work through it. Any help is greatly
appreciated.
Thanks,
Sheri

 

[PC Datasheet]

The highest Level is a calculated value and as such should NOT be stored in
a table. When you need to display it in a form or report, you should
calculate it at that point in a query or with the DMax function.

 

[Sheri Emery]

I'm sorry - I don't quite understand what you are saying.
I want my table to be filled with the most recent value.
Clients are assigned a supervision level of 1-4, which can
change often during their stay in the program. We need to
track the supervision level (as well as dates of change,
reason for change, etc.) I would like the most recent
supervision level to appear in my reports though. Thanks
for any help.
Sheri

 

[PC Datasheet]

You need three tables:
TblSupervisionLevel
SupervisionLevelID
SupervisionLevel
Rank '1 being lowest, 4 being highest

TblClient
ClientID
etc

TblClientSupervisionLevel
SupervisionLevelID
ClientID
SupervisionLevelID
DateAssignedLevel
ReasonAssignedLevel

For your report, create a query based on the three tables. Include all the
fields you need for the report and be sure to include Rank. Change the query
to a Totals query by clicking on the Sigma (looks like E) button on the
toolbar at the top of the screen. In the query grid, change Group By under
Rank to Max.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com