Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Why won't Access Run this Query?

Discussion in 'Information Technology' started by meyvn77@yahoo.com, Nov 12, 2005.

  1. meyvn77@yahoo.com

    meyvn77@yahoo.com
    Expand Collapse
    Guest

    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]));
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

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


    <meyvn77@yahoo.com> wrote in message
    news:1131736690.677749.64370@g49g2000cwa.googlegroups.com...
    > 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]));
    >
     
  4. meyvn77@yahoo.com

    meyvn77@yahoo.com
    Expand Collapse
    Guest

    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?
     
  5. Smartin

    Smartin
    Expand Collapse
    Guest

    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)

    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
     
  6. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

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

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

Share This Page