Most Recent Date

[mike_corrente]

I have one linked table (tblSchedule) and one local table (tblTemp) in
my database. I don't want to add any data to tblSchedule at all. Each
client is given a unique ClientID (tblSchedule). Each client has
several HearingDates. Each of these HearingDates is given a
HearingType (a number from 1-30). I want to update a field in tblTemp
to show the most recent HearingDate of either a type "1" HearingType or
a type "5" HearingType. I'd probably get it if I kept screwing around,
but my head hurts.

 

[Van T. Dinh]

Try a Query with an SQL String like (***untested***):

1. Using TOP predicate in SubQuery:

UPDATE tblTemp AS T
SET HearingDate15 =
(
SELECT TOP 1 HearingDate
FROM tblSchedule AS S
WHERE (S.frg_ClientID = T.frg_ClientID)
AND ((S.frg_HearingTypeID = 1) OR (S.frg_HearingType = 5))
ORDER BY S.HearingDate
)


2. Using Inner Join to a SubQuery

UPDATE tblTemp AS T
INNER JOIN
(
SELECT DISTINCT frg_ClientID, Max(HearingDate) AS SrcHearingDate15
FROM tblSchedule AS S
WHERE (S.frg_HearingTypeID = 1)
OR (S.frg_HearingType = 5)
GROUP BY S.frg_ClientID
) As Src
ON T.frg_ClientID = Src.frg_ClientID
SET frg_HearingTypeID = Src.SrcHearingDate15

The second method is probably more efficient but I am not too certain that I
got it right, especially that the SubQuery is not updateable, so use
whichever works for you.

--
HTH
Van T. Dinh
MVP (Access)