Why won't Access Run this Query?

#1 (**permalink**) 12-Nov-2005, 22:39 PM

UPDATE GIS_EVENTS_TEMP SET GIS_EVENTS_TEMP.FSTHARM1 =
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/7227-why-wont-access-run-this-query.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7227
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]));

*

#2 (**permalink**) 12-Nov-2005, 22:39 PM

You can't combine multiple WHERE clauses like that.

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

wrote in message
news:1131736690.677749.64370@g49g2000cwa.googlegro ups.com...
> UPDATE GIS_EVENTS_TEMP SET GIS_EVENTS_TEMP.FSTHARM1 =
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7227
> HarmfulEvent.HarmfulEvent
> WHERE (((HarmfulEvent.UnitId)=1) AND ((HarmfulEvent.ListOrder)=0) AND
> ((GIS_EVENTS_TEMP.CASEID)=[harmfulevent].[crashnumber])),
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7227
>
> GIS_EVENTS_TEMP.FSTHARM1 = HarmfulEvent.HarmfulEvent
> WHERE (((HarmfulEvent.UnitId)=2) AND ((HarmfulEvent.ListOrder)=1) AND
> ((GIS_EVENTS_TEMP.CASEID)=[harmfulevent].[crashnumber]));
>

#3 (**permalink**) 12-Nov-2005, 22:39 PM

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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7227

#4 (**permalink**) 12-Nov-2005, 22:39 PM

meyvn77@yahoo.com wrote:
> 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)

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7227
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7227
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.

--
Smartin

#5 (**permalink**) 13-Nov-2005, 17:33 PM

hum, why not change that "," to a and to join the where stuff together?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7227
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=7227

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...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal