By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Macro Update Function To Table

P: 11
I have a dilemma. I created a query named QP Test, the second query I created is named QP Test Update. My table I am trying to flag with 1 is all_hx4.qp Flag. I am not sure what I have done wrong, but could use some help or an alternate method. My first query QP Test does what it should, it is the second one I am having difficulty with. Any help is greatly appreciated in advance.

>SQL BELOW FOR QP Test Update<

UPDATE ALL_HX4 INNER JOIN [QP TEST]
ON (ALL_HX4.tDATE=[QP TEST].tDATE)
AND (ALL_HX4.tTRK=[QP TEST].tTRK)
AND (ALL_HX4.nRACE=[QP TEST].nRACE)
SET ALL_HX4.[QP Flag] = 1
WHERE (((ALL_HX4.nQP)>=6));
Feb 14 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
Almost certainly your QP Test query is non-updatable, which results in the update query also becoming non-updatable (see MS Knowledge base item http://support.microsoft.com/kb/328828/en-us ).

As a quick work-around setup a version of QP test as a make-table query outputting to a temporary table, tmpQp Test say, then after creating this temporary table substitute the temp table name for Qp Test in your update query. As both ends of the joined tables are updatable the update should work OK.

If you need to do this regularly you could set up a simple macro to run the two queries one after the other in one operation.

-Stewart
Feb 14 '08 #2

Scott Price
Expert 100+
P: 1,384
Hi Stewart,

We appreciate very much your help and knowledgeable answers here! One quick request: When posting code or url tags, please place them inside the appropriate tags. Code by selecting the code and then clicking the # button on the top of the reply window. URL by first clicking the Globe button, entering whatever text you wish to display and then pasting in the URL.

As you can see, I've edited your last post to include the hyperlink URL.

Thanks!

Regards,
Scott
Feb 14 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.