Re: Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
- From: Tom van Stiphout <no.spam.tom7744@xxxxxxx>
- Date: Wed, 31 May 2006 10:06:43 -0700
On 31 May 2006 09:52:12 -0700, "Bob" <bobg.rjservices@xxxxxxxxx>
wrote:
If DB bloat is a serious concern, you could create the "temp" table in
a separate MDB you create on the fly, use for your query, and delete.
Or upsize to SQL Server Express Edition?
-Tom.
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" <bobg.rjservices@xxxxxxxxx> wrote in message
news:1149083725.479802.283110@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Prev by Date: Re: Adding decimals to duplicate values
- Next by Date: Re: Subreport or DLookup?
- Previous by thread: Re: Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
- Next by thread: Re: Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
- Index(es):
Relevant Pages
|
