Update tbl based on join tbl data

[zrobinso]

I have 3 tables, one to many join.

TblEmployer,(Main Table)
EmployerID
EmployerName
Address
etc.....

TblServices
EmployerID
ContactDate
ServiceType
ServiceCode

TblStatus
EmployerID
Status(Active or Inactive)
DateEstablished
DateClosed
ClosureReason

If an Employer has not had any services in 12 months I need the [Status] changed to Inactive ,[DateClosed]to current date and [ClosureReason] to say "No Activity for 1 year".

If possible, Executed through a command button.

 

[cmmrfrds]

Not sure if I understand but the update could look something like the following. Use a subquery to determine the records to update.

Update
Set Status(Active or Inactive) = "I"
Set DateClosed = Now()
Set ClosureReason = "No Activity for 1 year"
From TblStatus
Where EmployeeID in (Select EmployeeID From TblServices
Where ContactDate < (&quot;yy&quot;,Now()) -1 )