Query not updatable - advise on how to change it

  • Thread starter Thread starter Angela
  • Start date Start date
A

Angela

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?
 
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
 
I believe using a sub query would resolve your problem. The basic update
quer nees to be:

UPDATE tblEmployees SET tblEmployees.SafetyActive = False WHERE(SELECT....)

The Where subquery should be constructed to determine which employee records
to update. That would be based on your first query. Since it is a subquery,
it will be okay for it to be a group by.
 
DANG I hate when I hit the wrong key stroke like that.

YOU can NEVER update in a query if there is an aggregate query in the FROM
clause - which qrySafety is. You need to use a SUBQUERY in the WHERE clause
to get the results you want. You can do this using your current query -
qrySafetyExpired.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.


UPDATE tblEmployees as E
SET E.SafetyActive = False
WHERE E.EmployeeID in
(SELECT tblCoursesTaken.IP_ID
FROM qrySafety_Expired)

I am a bit confused how your queries can work at all since you refer to
qrySafetyExpired.IP_ID in the second query and I don't see that field in the
first query's Select clause.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you both. I have no experience with subqueries so this is new. (The
IP_ID thing was a typo, should just have been ID)

John Spencer said:
DANG I hate when I hit the wrong key stroke like that.

YOU can NEVER update in a query if there is an aggregate query in the FROM
clause - which qrySafety is. You need to use a SUBQUERY in the WHERE clause
to get the results you want. You can do this using your current query -
qrySafetyExpired.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.


UPDATE tblEmployees as E
SET E.SafetyActive = False
WHERE E.EmployeeID in
(SELECT tblCoursesTaken.IP_ID
FROM qrySafety_Expired)

I am a bit confused how your queries can work at all since you refer to
qrySafetyExpired.IP_ID in the second query and I don't see that field in the
first query's Select clause.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
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?
Click to expand...
Click to expand...
 
Back
Top