Re: Query not updatable - advise on how to change it

Tech-Archive recommends: Speed Up your PC by fixing your registry

SELECT tblCoursesTaken.ID, Max(tblCoursesTaken.CompletionDate) AS
MaxOfCompletionDate
FROM tblEmployees INNER JOIN tblCoursesTaken ON tblEmployees.ID =
tblCoursesTaken.ID
WHERE (((DateAdd("yyyy",1,[completiondate])-Date())<0) AND
((tblCoursesTaken.CourseNumber)=30000) AND ((tblEmployees.SafetyActive)=True))
GROUP BY tblCoursesTaken.ID;

The second query links the first query to the employee table where I can
change the Safety Flag to False:
UPDATE qrySafetyExpired INNER JOIN tblEmployees ON qrySafetyExpired.IP_ID =
tblEmployees.IP_ID SET tblEmployees.SafetyActive = False;
but it doesn't work because the query is not updatable.

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

Angela wrote:
I know that there are many posts regarding non-updatable queries and I've read thru most of them but still don't quite understand what I need to do to get this query to be updatable, or perhaps it's just not possible. Sorry if this is a newbie question.

I have two tables, one contains employee data and the second contains a list of courses that they have taken which includes dates. There is one safety class that must be taken every year. From the table containing courses taken joined to the employee table, I used a group query to find the last date on which each employee took the mandatory course for those employees that currently have SafetyActive=True. If the completion date was over a year ago, I want to go into the employee table and change the "SafetyActive" flag to False.

My first query to find the list of employees with expired courses is a group query:
SELECT tblCoursesTaken.ID, Max(tblCoursesTaken.CompletionDate) AS MaxOfCompletionDate
FROM tblEmployees INNER JOIN tblCoursesTaken ON tblEmployees.ID = tblCoursesTaken.ID
WHERE (((DateAdd("yyyy",1,[completiondate])-Date())<0) AND ((tblCoursesTaken.CourseNumber)=30000) AND ((tblEmployees.SafetyActive)=True))
GROUP BY tblCoursesTaken.ID;

The second query links the first query to the employee table where I can change the Safety Flag to False:
UPDATE qrySafetyExpired INNER JOIN tblEmployees ON qrySafetyExpired.IP_ID = tblEmployees.IP_ID SET tblEmployees.SafetyActive = False;
but it doesn't work because the query is not updatable.

I understand from previous posts that the first query makes this non-updatable because of the grouping. Is there another way to accomplish this task, or another way to design these queries so it will work?
.

Relevant Pages

  • Re: Record listed multiple times
    ... that query is cuerrently pulling the EmpDept field from teh Employee table. ... The entries in question in my query are Kasey Brooker-sessionID 900, ... FROM (tblParticipants INNER JOIN tblParticipantTrx ON (tblParticipants.EmpID ...
    (microsoft.public.access.queries)
  • RE: Sub report message
    ... performs the first join and then include that query in your SQL statement." ... Left join employee table to issue table. ... 1.I checked the main report query to see if there was a problem. ... All site staff are listed including all of the Food Service ...
    (microsoft.public.access.reports)
  • RE: How to code/perform a search
    ... You really don't want to use a query for this. ... It will locate the employee id in the form's recrdsetclone and make the ... use a text box to capture search criteria from the user. ... The After Update event is to ...
    (microsoft.public.access.formscoding)
  • RE: How to code/perform a search
    ... You really don't want to use a query for this. ... It will locate the employee id in the form's recrdsetclone and make the ... Dave Hargis, Microsoft Access MVP ... use a text box to capture search criteria from the user. ...
    (microsoft.public.access.formscoding)
  • RE: How to code/perform a search
    ... You really don't want to use a query for this. ... It will locate the employee id in the form's recrdsetclone and make the ... Initially, the form was unbound, but then I could not enter the ... use a text box to capture search criteria from the user. ...
    (microsoft.public.access.formscoding)