Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??

running access 2k; mdb w/ linked tables to another mdb
(front/back-end);

trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!

Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
update!?!?!?!? (I can clearly update it without the XT)

What the ---- good is a crosstab query, if I can't USE the information
within it for something other than reporting?!?!?

Here's my query (the "XT" table is my crosstab):
I'm guessing that maybe I need to use a sub-query to work around this,
but am not sure how, nor why I should have to go this route....

UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
Q_answers_XT.id = T_final_answers.id
SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
[Q_answers_XT]![a];


any help appreciated...
TIA

By design you can't use a crosstab or any aggregate query in an update query
(except as a subquery in the where clause).

You may have to resort to using the cross-tab to create (or populate) a
temporary work table. Then you can use the work table in a query to update
your fields in T_Final_Answers.

You might be able to use the VBA function - DLookup - to find the value in
the crosstab and use that in your update query. Something like the
following UNTESTED query.

UPDATE T_Final_Answers
SET q = DLookup("q","q_answers_xt","id=" & T_final_answers.Id)
WHERE ID in (SELECT ID FROM q_answers_Xt)

"Bob" wrote in message
oups.com...
running access 2k; mdb w/ linked tables to another mdb
(front/back-end);

trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!

Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
update!?!?!?!? (I can clearly update it without the XT)

What the ---- good is a crosstab query, if I can't USE the information
within it for something other than reporting?!?!?

Here's my query (the "XT" table is my crosstab):
I'm guessing that maybe I need to use a sub-query to work around this,
but am not sure how, nor why I should have to go this route....

UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
Q_answers_XT.id = T_final_answers.id
SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
[Q_answers_XT]![a];


any help appreciated...
TIA

Tom, & John;

I'm very appreciative for your replies.

John; I'll try your suggestion, and see how it goes - I REALLY don't
want to have to create a temp table everytime this needs to happen -
just becomes a compaction nightmare.

This is about as brain-dead, as anything microsoft has ever done; and
they've sure done plenty. Can't tell you how many times their idiosy
has ticked me off.

When the ---- are people going to STOP paying microsoft for BS
marketing improvements, and force them to write good code? (that is
retorical, of course, because I know this will never happen - other
companies follow microsoft's example because people are stupid, or
ignorant enough to let it happen).

ok, ok - close the vents, I'm done blowing steam into the empty vacuum
of microsoft customer awareness...... :-)

T & J - again, I'm very appreciative for your suggestions; clearly, if
John's suggestion fails, I have no choice but to create a temporary
table - again & again & again & again, and compact again & again &
again .....;-O

TX - Bob

John Spencer wrote:
By design you can't use a crosstab or any aggregate query in an update query
(except as a subquery in the where clause).

You may have to resort to using the cross-tab to create (or populate) a
temporary work table. Then you can use the work table in a query to update
your fields in T_Final_Answers.

You might be able to use the VBA function - DLookup - to find the value in
the crosstab and use that in your update query. Something like the
following UNTESTED query.

UPDATE T_Final_Answers
SET q = DLookup("q","q_answers_xt","id=" & T_final_answers.Id)
WHERE ID in (SELECT ID FROM q_answers_Xt)

"Bob" wrote in message
oups.com...
running access 2k; mdb w/ linked tables to another mdb
(front/back-end);

trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!

Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
update!?!?!?!? (I can clearly update it without the XT)

What the ---- good is a crosstab query, if I can't USE the information
within it for something other than reporting?!?!?

Here's my query (the "XT" table is my crosstab):
I'm guessing that maybe I need to use a sub-query to work around this,
but am not sure how, nor why I should have to go this route....

UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
Q_answers_XT.id = T_final_answers.id
SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
[Q_answers_XT]![a];


any help appreciated...
TIA

Don't forget that you can also use a pair of recordsets and use one for the
crosstab and one for the update.

BTW, John;

Just wanted to let you know, that your suggestion did work...

Although dlookup is about as cycle intensive as it gets, I think it's
still better than constantly re-creating, and compacting huge tables...

TX again! -
Bob

John Spencer wrote:
By design you can't use a crosstab or any aggregate query in an update query
(except as a subquery in the where clause).

You may have to resort to using the cross-tab to create (or populate) a
temporary work table. Then you can use the work table in a query to update
your fields in T_Final_Answers.

You might be able to use the VBA function - DLookup - to find the value in
the crosstab and use that in your update query. Something like the
following UNTESTED query.

UPDATE T_Final_Answers
SET q = DLookup("q","q_answers_xt","id=" & T_final_answers.Id)
WHERE ID in (SELECT ID FROM q_answers_Xt)

"Bob" wrote in message
oups.com...
running access 2k; mdb w/ linked tables to another mdb
(front/back-end);

trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!

Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
update!?!?!?!? (I can clearly update it without the XT)

What the ---- good is a crosstab query, if I can't USE the information
within it for something other than reporting?!?!?

Here's my query (the "XT" table is my crosstab):
I'm guessing that maybe I need to use a sub-query to work around this,
but am not sure how, nor why I should have to go this route....

UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
Q_answers_XT.id = T_final_answers.id
SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
[Q_answers_XT]![a];


any help appreciated...
TIA

Rick;

TX also, for your reply -

when you talk about a pair of recordsets, you don't mean a sub-query -
right?
Could you ellaborate / illustrate please?

TIA! - Bob

Rick Wannall wrote:
Don't forget that you can also use a pair of recordsets and use one for the
crosstab and one for the update.

Sure. This example assumes that you want to open the xtab and then go find
rows in the other recordset (query) to update. Reversing this is no real
challenge.

dim strWhere as string
dim rXT as ado.recordset
dim rUpdate as ado.recordset
.... (get connection(s) ...

set rXT = new ado.recordset
set rUpdate = new ado.recordset

rXT.open "Select xt.* From MyCrosstabQuery As xt" , myconnection1...
if rXT .bof=true and rXT.eof = true endif
'put cleanup code here and exit. There are no records in the xtab query.
endif
rXT.MoveFirst
do until rxt.eof = true
strWhere = "criterionfield1 = " & rXT.Fields("xtabcriterionfield1")
rUpdate.Open "Select updatefield1, updatefield2,... from MyOtherQuery
Where " & strWhere, myconnection2
if rUPdate.bof = true and rupdate.eof = true then
'close rupdate and continue to the next row of xtab
rupdate.close
else
rupdate.movefirst
do until rupdate.eof = true
rupdate.fields("updatefield1") = rxt.fields("xtabvaluefield1")
rupdate.fields("updatefield2") = rxt.fields("xtabvaluefield2")
rupdate.update
rupdate.movenext
loop
endif
rxt.MoveNext
loop

rxt.close
set rxt = nothing
set rupdate = nothing
set myconnection1 = nothing
set myconnection2=nothing

This is of course pseudocode, not based on an actual table, but it should
give you the structure you need for the task.

criterionfield1 is the name of a field in your table to update, a field you
would use to locate rows to update.

xtabcriterionfield1 would be the field in your crosstab query that you would
use to locate a row in the table to update.

updatefield and xtabvalue field are respectively the destination and source
fields for the udpate.

Rick-

I understand now, and am ever-appreciative for your time!!

TX again - Bob

Rick Wannall wrote:
Sure. This example assumes that you want to open the xtab and then go find
rows in the other recordset (query) to update. Reversing this is no real
challenge.

dim strWhere as string
dim rXT as ado.recordset
dim rUpdate as ado.recordset
... (get connection(s) ...

set rXT = new ado.recordset
set rUpdate = new ado.recordset

rXT.open "Select xt.* From MyCrosstabQuery As xt" , myconnection1...
if rXT .bof=true and rXT.eof = true endif
'put cleanup code here and exit. There are no records in the xtab query.
endif
rXT.MoveFirst
do until rxt.eof = true
strWhere = "criterionfield1 = " & rXT.Fields("xtabcriterionfield1")
rUpdate.Open "Select updatefield1, updatefield2,... from MyOtherQuery
Where " & strWhere, myconnection2
if rUPdate.bof = true and rupdate.eof = true then
'close rupdate and continue to the next row of xtab
rupdate.close
else
rupdate.movefirst
do until rupdate.eof = true
rupdate.fields("updatefield1") = rxt.fields("xtabvaluefield1")
rupdate.fields("updatefield2") = rxt.fields("xtabvaluefield2")
rupdate.update
rupdate.movenext
loop
endif
rxt.MoveNext
loop

rxt.close
set rxt = nothing
set rupdate = nothing
set myconnection1 = nothing
set myconnection2=nothing

This is of course pseudocode, not based on an actual table, but it should
give you the structure you need for the task.

criterionfield1 is the name of a field in your table to update, a field you
would use to locate rows to update.

xtabcriterionfield1 would be the field in your crosstab query that you would
use to locate a row in the table to update.

updatefield and xtabvalue field are respectively the destination and source
fields for the udpate.

"Bob" wrote in
ups.com:

This is about as brain-dead, as anything microsoft has ever done;
and they've sure done plenty. Can't tell you how many times their
idiosy has ticked me off.

Well, pre-Access 97, a lot of these things worked, but were
completely inconsistent with every other SQL dialect. With A97/Jet
3.5, Microsoft tightened up the join and updatability rules to be
more consistent with other flavors of SQL, which is probably
something that was required in the push to make Access interoperable
with SQL Server.

Then there's SQL 92 support in A2K and later. I doubt it would solve
the problem easily, but it might provide other solutions to the
problem of unupdatability.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Bob" wrote in
oups.com:

Although dlookup is about as cycle intensive as it gets, I think
it's still better than constantly re-creating, and compacting huge
tables...

It's not.

Temp tables are going to be *much* faster. If it's a regular
operation, just keep a copy of the empty temp database and copy over
it when your app exits. That way you have a clean copy of the temp
database every time you start your app, and never have to compact
it.

Others say it's quick to recreate the temp table in code, once the
code is written. Me, I've never taken the time to write such code,
so find it easier to run a MakeTable query, then edit the resulting
table to have appropriate indexing, then empty it out, copy it into
the temp database and compact it.

I was surprised to discover that the time it takes to write to the
temp table is not greater than the alternatives that use all memory.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/