Max date query and go direct to record for edit

  • Thread starter Thread starter Hugh self taught
  • Start date Start date
H

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.
 
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
'====================================================
 
Back
Top