Why won't Access Run this Query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

meyvn77@yahoo.com - 11 Nov 2005 19:17 GMT

UPDATE GIS_EVENTS_TEMP SET GIS_EVENTS_TEMP.FSTHARM1 =
HarmfulEvent.HarmfulEvent
WHERE (((HarmfulEvent.UnitId)=1) AND ((HarmfulEvent.ListOrder)=0) AND
((GIS_EVENTS_TEMP.CASEID)=[harmfulevent].[crashnumber])),

GIS_EVENTS_TEMP.FSTHARM1 = HarmfulEvent.HarmfulEvent
WHERE (((HarmfulEvent.UnitId)=2) AND ((HarmfulEvent.ListOrder)=1) AND
((GIS_EVENTS_TEMP.CASEID)=[harmfulevent].[crashnumber]));

Reply to this Message

Amy Blankenship - 11 Nov 2005 19:41 GMT

You can use

SELECT foo FROM Table1, Table2 WHERE Table1.Foreignkey=Table2.ID AND yadda=1

But you could never use

SELECT foo FROM Table1 WHERE Table1.Foreignkey=Table2.ID, Table2 WHERE
yadda=1

even in a select query, so of course it won't work in an UPDATE query.

HTH;

Amy

> UPDATE GIS_EVENTS_TEMP SET GIS_EVENTS_TEMP.FSTHARM1 =
> HarmfulEvent.HarmfulEvent
[quoted text clipped - 4 lines]
> WHERE (((HarmfulEvent.UnitId)=2) AND ((HarmfulEvent.ListOrder)=1) AND
> ((GIS_EVENTS_TEMP.CASEID)=[harmfulevent].[crashnumber]));

Reply to this Message

John Vinson - 11 Nov 2005 20:53 GMT

>UPDATE GIS_EVENTS_TEMP SET GIS_EVENTS_TEMP.FSTHARM1 =
>HarmfulEvent.HarmfulEvent
[quoted text clipped - 4 lines]
>WHERE (((HarmfulEvent.UnitId)=2) AND ((HarmfulEvent.ListOrder)=1) AND
>((GIS_EVENTS_TEMP.CASEID)=[harmfulevent].[crashnumber]));

Because the query has two Update clauses and two Where clauses. It can
only have one, in JET SQL (and for that matter in any dialect of SQL
I've ever used).

In addition, you're updating to a value in the HarmfulEvent table but
you're not referencing it in your UPDATE clause to include it in the
table. How are GIS_EVENTS_TEMP and HarmfulEvent related? By UnitID? By
UnitID being equal to ListOrder plus 1? or what?

John W. Vinson[MVP]

Reply to this Message