Operation must use an updateable query??

[Guest]

I have a subquery that ranks test scores correctly until there is a
duplicate.

Table1 Results What I want
Score Rank Score rank Score Rank
68.4 78.4 1 78.4 1
46.6 73.4 2 73.4 2
34.0 70.0 3 70.0 3
78.4 68.4 5 68.4 4 <-- Want 4 twice
68.4 68.4 5 68.4 4 <-- and skip 5
70.0 57.8 6 57.8 6
57.8 46.6 7 46.6 7
73.4 34.0 8 34.0 8

I got the subquery code from a post last October and Marshall Barton
replied to my request for help with the correct SQL. I now get the desired
results in a Select Query.

Now the problem is that I need to update the source table (Table1). I used
the sql below but get an error msg: "Operation must use an updateable query".
I have read the HELP screen on this error but can not figure out how to code
it correctly.

UPDATE Table1 SET Table1.Rank = (select count(*)+1 from [table1] as x where
x.score > [table1].[score]);

The "(select count(*).... " sql code works as expected when used in a
SELECT, MAKE TABLE, or APPEND TABLE query but not an UPDATE query. I would
like to not use temp tables to get the updating done.

Thanks for any help

 

[Ken Snell [MVP]]

Because you're using a "totals" query as part of the data source, Jet
considers the query to be nonupdatable (totals queries cannot be updated).

A workaround would be to use the DCount function in place of the subquery:


UPDATE Table1
SET Table1.Rank =
DCount("*", "table1", "[score] > " &
[Table1].[score]) +1;

 

[Guest]

That seems to work, at least on my simple test table. I think I need to
learn more about the Domain Agg Functions.

thanks much

Steve

Because you're using a "totals" query as part of the data source, Jet
considers the query to be nonupdatable (totals queries cannot be updated).

A workaround would be to use the DCount function in place of the subquery:


UPDATE Table1
SET Table1.Rank =
DCount("*", "table1", "[score] > " &
[Table1].[score]) +1;


--

Ken Snell
<MS ACCESS MVP>

I have a subquery that ranks test scores correctly until there is a
duplicate.

Table1 Results What I want
Score Rank Score rank Score Rank
68.4 78.4 1 78.4 1
46.6 73.4 2 73.4 2
34.0 70.0 3 70.0 3
78.4 68.4 5 68.4 4 <-- Want 4
twice
68.4 68.4 5 68.4 4 <-- and skip
5
70.0 57.8 6 57.8 6
57.8 46.6 7 46.6 7
73.4 34.0 8 34.0 8

I got the subquery code from a post last October and Marshall Barton
replied to my request for help with the correct SQL. I now get the
desired
results in a Select Query.

Now the problem is that I need to update the source table (Table1). I
used
the sql below but get an error msg: "Operation must use an updateable
query".
I have read the HELP screen on this error but can not figure out how to
code
it correctly.

UPDATE Table1 SET Table1.Rank = (select count(*)+1 from [table1] as x
where
x.score > [table1].[score]);

The "(select count(*).... " sql code works as expected when used in a
SELECT, MAKE TABLE, or APPEND TABLE query but not an UPDATE query. I
would
like to not use temp tables to get the updating done.

Thanks for any help