Update Query - Operation must use an updateable query

[Mark]

Hi,

I am having problems with a simple query I am trying to put together.
I have one table (called tblHost) and a query called
qryCountStudentsHosted. Within tblHost I have a field called
intCurrentNumberOfStudents. Each host has a number of students and
the qryCountStudentsHosted counts the number of students a host
currently has. I then want to update the field
intCurrentNumberOfStudents with this count of students using an update
query. The SQL I am using for this is as follow:

UPDATE qryCountStudentsHosted INNER JOIN tblHost ON
qryCountStudentsHosted.idsHost = tblHost.idsHost SET
tblHost.intCurrentNumberOfStudents = [qryCountStudentsHosted].
[CountOfidsStudent];

When I run this query I get the error message "Operation must use an
updateable query". It seems that it trying to update a field with
qryCountStudentsHosted, but I want to update the field
(intCurrentNumberOfStudents) within tblHost.

Any suggestions?

 

[John Spencer]

Access won't allow you to use the results of an aggregate query to
update any field.

You have at least three choices.
First (and usually best) is not to store the calculated data at all but
use a query to get it when it is needed. This means the data is always
correct.

SELECT Host.*
, DCOUNT("*","qryCountStudentsHosted","idsHost=" & tblHost.idsHost)
FROM Host

Or using the Host table and your query
SELECT Host.*, CountOfIdsStudents
FROM Host INNER JOIN qryCountStudentsHosted
ON tblHost.idsHost = qryCountStudentsHosted.idsHost

If for some reason, you really, really, really need to store the
information in the Host table then try on of the following methods.

Second, use the DCount function to get the data. Which you could use as
one way to get the information for the first solution

UPDATE tblHost
SET intCurrentNumberOfStudents =
DCOUNT("*","qryCountStudentsHosted","idsHost=" & tblHost.idsHost)

If idsHost is a text field then you will need to add in text delimiters
to properly build the function call

UPDATE tblHost
SET intCurrentNumberOfStudents =
DCOUNT("*","qryCountStudentsHosted","idsHost=""" & tblHost.idsHost &
"""")

Third, (and I use this only if you have a really large number of
records) use qryCountStudentsHosted as the source for a make table
query. Then join the table to tblHost.

By the way, if you are using the DCount method, you can probably be more
efficient by skipping qryCountStudentsHosted and using something like
the following in your query
DCount("*","StudentTable","HostID=" & Host.HostID) as StudentCount


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

 

[Lord Kelvan]

in the end result you are not supose to store calculated values in a
table that si what a query is for so rather than storing the number of
students per host use a query to get that information. for whatever
you want it for.

 

[Mark]

Access won't allow you to use the results of an aggregate query to
update any field.

You have at least three choices.
First (and usually best) is not to store the calculated data at all but
use a query to get it when it is needed.  This means the data is always
correct.

SELECT Host.*
, DCOUNT("*","qryCountStudentsHosted","idsHost=" & tblHost.idsHost)
FROM Host

Or using the Host table and your query
SELECT Host.*, CountOfIdsStudents
FROM Host INNER JOIN qryCountStudentsHosted
ON tblHost.idsHost = qryCountStudentsHosted.idsHost

If for some reason, you really, really, really need to store the
information in the Host table then try on of the following methods.

Second, use the DCount function to get the data. Which you could use as
one way to get the information for the first solution

UPDATE tblHost
SET intCurrentNumberOfStudents =
   DCOUNT("*","qryCountStudentsHosted","idsHost=" & tblHost.idsHost)

If idsHost is a text field then you will need to add in text delimiters
to properly build the function call

UPDATE tblHost
SET intCurrentNumberOfStudents =
   DCOUNT("*","qryCountStudentsHosted","idsHost=""" & tblHost.idsHost &
"""")

Third, (and I use this only if you have a really large number of
records) use qryCountStudentsHosted as the source for a make table
query.  Then join the table to tblHost.

By the way, if you are using the DCount method, you can probably be more
efficient by skipping qryCountStudentsHosted and using something like
the following in your query
    DCount("*","StudentTable","HostID=" & Host.HostID) as StudentCount

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

Hi,

I am having problems with a simple query I am trying to put together.
I have one table (called tblHost) and a query called
qryCountStudentsHosted.  Within tblHost I have a field called
intCurrentNumberOfStudents.  Each host has a number of students and
the qryCountStudentsHosted counts the number of students a host
currently has.  I then want to update the field
intCurrentNumberOfStudents with this count of students using an update
query.  The SQL I am using for this is as follow:

UPDATE qryCountStudentsHosted INNER JOIN tblHost ON
qryCountStudentsHosted.idsHost = tblHost.idsHost SET
tblHost.intCurrentNumberOfStudents = [qryCountStudentsHosted].
[CountOfidsStudent];

When I run this query I get the error message "Operation must use an
updateable query".  It seems that it trying to update a field with
qryCountStudentsHosted, but I want to update the field
(intCurrentNumberOfStudents) within tblHost.

Any suggestions?- Hide quoted text -

- Show quoted text -

You are right I will calculate it on the fly, rather than storing the
count in a table.

Cheers