Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Another .adp issue (Access2K/SQL)

    I'm trying to do a little update query where I only want a number field to increment on one table, if the order number matches in another table. Since the update stored procedure only lets you call one table, I understand that you have to use a TRIGGER function but I don't understand how to do this. This is what I'm trying to do...

    UPDATE T_Print2 INNER JOIN tblCoNumberShipLabel ON T_Print2.CO_NUMBER = tblCoNumberShipLabel.[Company Number]
    SET T_Print2.[ShipLabelPrint#] = CASE WHEN IsNull([ShipLabelPrint#]) THEN 1 ELSE [ShipLabelPrint#]+1 END

    When I run this, it errors out on the INNER. Any suggestions? <img src=/S/please.gif border=0 alt=please width=31 height=23>
    Carpy Diem, it&#39;s .

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Another .adp issue (Access2K/SQL)

    Aren't sprocs fun????? Actually, I think you may want to use an outer (LEFT?) join to determine whether or not the order number matches by using the IF NOT NULL condition, and then doing the increment when that is true. Also, you shouldn't need a trigger to do that - triggers are usually run on the table itself, and could actually do what you suggest, but you should be able to do it in a stored procedure using a join between the two tables. If none of this makes sense, give us a more complete description of your table structure, and what the increment process is about and we'll see if we can construct something.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another .adp issue (Access2K/SQL)

    Wendel,

    Fortunately I don't need the case logic any more, and I'll try the outer left join and let you know how it goes. Ya, we're havin' fun here, doncha know... <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Carpy Diem, it&#39;s .

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another .adp issue (Access2K/SQL)

    Wendell,

    I finally got the "update blah with blah" to work and here's what I did. First I set up a view that joined the two tables. Then I set up an update stored procedure on the view. The SQL script is being "fed" in by a VB6 pogram so the outer join didn't work for some undocumented-feature reason. <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

    Thanks!
    Carpy Diem, it&#39;s .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •