Max date query and go direct to record for edit

[Hugh self taught]

Hi clever people,

I have a table History with fields Hist_Idx, Employ, Compan, StartDate,
FinishDate

HistIdx is the pk for this table & the fk in table Candidates.

I need to find the latest StartDate for Employ so that I can update
FinishDate to Date() & then I'll add a new record with StartDate = Date()

I've tried manipulating the examples I've seen on the forum but alas my
brian is not functioning anymore with all the data it's tried to consume, so
if someone can help me with this bit of coding, I can put this database to
bed so to speak.

 

[John Spencer]

You can try the following as the update query.
Back up your data first
Back up your data first

If this does not work as you want, your backup will be the only way you
can recover

UPDATE History
Set FinishDate = Date()
WHERE StartDate =
(SELECT Max(StartDate) FROM History as H1
WHERE H1.Employ = History.Employ)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Hugh self taught]

John, You're my new best friend..!! Works like a charm...Many thanks