Windows 7 - Update with subquery[complex]

Asked By JoostdeVrie on 17-Mar-08 08:00 AM
Hi,

I'm currently working on a script that will be executed when someone deletes
a "team" entry from a quality control database. The database contains teams,
employees and samples (each employee belongs to a team and for each employee
samples can be registered). Each sample has two references to the empolyees
table: "employee" and "registrant" (always two different id's).

What I want to achieve now is that when a team is deleted, all samples that
have a registrant of the deleted team linked will now link to a so-called

My query now looks like this:

UPDATE samples
INNER JOIN employees As emplT ON
samples.employee=emplT.empolyee_id
SET samples.registrant=(SELECT TOP 1
employee_id FROM employees
WHERE last_name="** EXPIRED **"
AND team=emplT.team)

When I try to execute this I get the error that "The query must be
updatable"... Any ideas how to sove this?

----------------
Joost de Vries
R&D Student




John Spencer replied on 17-Mar-08 08:43 AM
Try using DLookup function.

Assumption: Team is a text field
UPDATE samples
INNER JOIN employees As emplT ON
samples.employee=emplT.employee_id
SET samples.registrant =
DLookup("Employee_ID", "Employees",

If Team is a number field then change the DLookup to eliminate the text
delimiters

DLookup("Employee_ID", "Employees",
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.
JoostdeVrie replied on 17-Mar-08 08:59 AM
Thanks John! I was just investigating exactly the same solution an while it
is a bit slow, it does the job!

----------------
Joost de Vries
R&D Student
John Spencer replied on 17-Mar-08 11:47 AM
You could probably speed it up significantly by applying some criteria to
the query so that only selected records get updated.  I would suggest
something for the criteria, but I don't understand your data structure
fully.

UPDATE samples
INNER JOIN employees As emplT ON
samples.employee=emplT.employee_id
SET samples.registrant =
DLookup("Employee_ID", "Employees",

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.
John Carter replied to John Spencer on 24-Feb-10 03:21 AM
In SQL*Server, this works:



use jc1accts

update customer

set balance =

(select sum(amount)

from invoice

where customer.c_no = invoice.c_no)



In Access, you get this error:






This clearly illustrates a bug in Access.
John Carter replied to John Carter on 24-Feb-10 03:23 AM
The error message in Access is:



Operation must use an updatable query