Update with subquery[complex]

[Joost de Vries]

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
"expired registrant" from their own team.

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?

 

[John Spencer]

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",
"Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """")

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

DLookup("Employee_ID", "Employees",
"Last_Name = ""** Expired **"" and Team= " & EmpIt.Team)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Joost de Vries]

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

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",
"Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """")

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

DLookup("Employee_ID", "Employees",
"Last_Name = ""** Expired **"" and Team= " & EmpIt.Team)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

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",
"Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

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 Carter]

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:

"Operation must use an updatable query"

This clearly illustrates a bug in Access.



John Spencer wrote:

You could probably speed it up significantly by applying some criteria to the
17-Mar-08

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 sample
INNER JOIN employees As emplT O
samples.employee=emplT.employee_i
SET samples.registrant
DLookup("Employee_ID", "Employees"
"Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """"

 

[John Carter]

The error message in Access is:

Operation must use an updatable query




John Carter wrote:

Bug in Access - Update with subquery
24-Feb-10

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:

"Operation must use an updatable query"

This clearly illustrates a bug in Access.

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint List Usage and Statistics
http://www.eggheadcafe.com/tutorial...d-9723a79fdb14/sharepoint-list-usage-and.aspx

 

[Duane Hookom]

It's just SQL that Access/JET doesn't support. You can use the much slower
DSum():

UPDATE Customer SET Balance = DSum("Amount","Invoice","C_No=" & C_No)

If C_No is text:

UPDATE Customer SET Balance = DSum("Amount","Invoice","C_No=""" & C_No &
"""")

--
Duane Hookom
MS Access MVP

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:

"Operation must use an updatable query"

This clearly illustrates a bug in Access.



John Spencer wrote:

You could probably speed it up significantly by applying some criteria to
the
17-Mar-08

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",
"Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.


Previous Posts In This Thread:

Update with subquery[complex]
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
"expired registrant" from their own team.

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

Try using DLookup function.
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",
"Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """")

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

DLookup("Employee_ID", "Employees",
"Last_Name = ""** Expired **"" and Team= " & EmpIt.Team)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.


Thanks John!
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


:

You could probably speed it up significantly by applying some criteria to
the
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",
"Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.



Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint - Managing Unused or Archive sites automatically
http://www.eggheadcafe.com/tutorial...5b-a2a8deb60cad/sharepoint--managing-unu.aspx

 

[John W. Vinson]

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:

"Operation must use an updatable query"

This clearly illustrates a bug in Access.

A friend of mine had a Volkswagen Beetle (back in the 70's) with a
personalized license plate: "FEATURE"

Yes, SQL/Server supports the above query. Access/JET (aka Access/ACE) does
not. They are different dialects of SQL with different restrictions and
different syntax. One of the differences is that JET considers any query
containing a TOTALS operation to be non-updateable.

Call it a bug if you like, but it's well and clearly documented.

 

[david]

Yes, it used to be that only Access supported modern SQL, and
features like replication and long table names and alternate character
sets and declarative referential integrity.

Gradually, all the other products, like Oracle and SQL Server and
MySQL, caught up with, and then passed Access/Jet. Sadly,
Access/Jet never got features from the other products: the influence
and the feature copying was all the other way.

(david)

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:

"Operation must use an updatable query"

This clearly illustrates a bug in Access.



John Spencer wrote:

You could probably speed it up significantly by applying some criteria to
the
17-Mar-08

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",
"Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.


Previous Posts In This Thread:

Update with subquery[complex]
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
"expired registrant" from their own team.

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

Try using DLookup function.
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",
"Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """")

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

DLookup("Employee_ID", "Employees",
"Last_Name = ""** Expired **"" and Team= " & EmpIt.Team)
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.


Thanks John!
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


:

You could probably speed it up significantly by applying some criteria to
the
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",
"Last_Name = ""** Expired **"" and Team= """ & EmpIt.Team & """")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.



Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint - Managing Unused or Archive sites automatically
http://www.eggheadcafe.com/tutorial...5b-a2a8deb60cad/sharepoint--managing-unu.aspx