Why won't Access Run this Query?

[meyvn77]

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

 

[Douglas J Steele]

You can't combine multiple WHERE clauses like that.

Separate them into two different queries, and run them separately.

 

[meyvn77]

Please tell me that there is a way around this I have a SQL server
query thats like 80 to these.
Your telling me Im going to have to break them up into 80 Queries?

 

[Douglas J Steele]

Afraid so.

Unlike SQL Server, Access doesn't allow you to run multiple queries at once.

 

[Smartin]

Please tell me that there is a way around this I have a SQL server
query thats like 80 to these.
Your telling me Im going to have to break them up into 80 Queries?

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

If all the queries follow this pattern, i.e.,

((HarmfulEvent.UnitId)= N ) AND ((HarmfulEvent.ListOrder)= N - 1)

you could write some vb code to loop from N=0 to N=80 or whatever and
either build the query on the fly or set up your UPDATE as a parameter
query.

 

[Albert D.Kallal]

hum, why not change that "," to a and to join the where stuff together?

I never seen that syntax of using a "," to separate where clauses.

It should also be noted that you 2nd where clause is course a implied join,
and this is legal in msaccess.

However, I see a LOT of different sql dialect, and this is the first time
seen (or know) that you could simply separate "where" clauses by a ",".
(it might be a common place thing..but this is first time I noticed that you
could do this!! - so, I guess I learn something new every day).

Perhaps changing the "," to a "and", and also adding () around the 2nd set
of where will work?

Something like:

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]))
and
(

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

)

Give the above a try, and note how I just added a "and" and a set of ()
around the sql...