Macro Update Function To Table

[topthebookie]

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));

[Stewart Ross]

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

[Scott Price]

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