Updating Table with Most Recent Option Data

  • Thread starter Thread starter Sheri Emery
  • Start date Start date
S

Sheri Emery

Hi,
I have another question regarding program specific to the
Option Buttons. I would like my report to return the most
recent value from the Option button selected. The options
are set as 1, 2, 3, and 4, which designate Levels of
Supervision. These levels of Supervision change
periodically, so I need to be able to keep a running
history, which I am able to do by inputting the data into
a separate form (table) that can be linked by Client ID to
the master Client table. I now would like to put together
a report that only shows the participant's current level
of supervision, and I seem to be stuck. Is there something
I can do to make the report field only return the most
recent value?
Thanks
 
Sheri,

You don't indicate your table structure, so I will assume that you have a
table (tbl_Emp_Super_Level) that contains the EmployeeID, SupervisionLevel,
and EffectiveDate. To get the most recent supervision level for each
employee, you can write your query in a couple of ways, you might want to
try both of these to determine which is faster.

SELECT ESL.EmployeeID, ESL.SupervisionLevel
FROM tbl_Emp_Super_Level ESL
WHERE ESL.EffectiveDate = DMAX("EffectiveDate", "tbl_Emp_Super_Level",
"EmployeeID =" & ELS.EmployeeID)

The other way:

SELECT ESL.EmployeeID, ESL.SuperVisionLevel
FROM tbl_Emp_Super_Level ESL
INNER JOIN (SELECT T.EmployeeID, MAX(T.EffectiveDate) as MostRecent
FROM tbl_Emp_Super_Level T
GROUP BY T.EmployeeID) as T1
ON ESL.EmployeeID = T1.EmployeeID
AND ESL.EffectiveDate = T1.EffectiveDate

With a small data set, I would expect the first one of these to be faster.
With a large number of employees, or in cases where the employees may have
changed their supervisory level numerous times, I think that latter will be
faster.

HTH
Dale
 
Back
Top