Another .adp issue (Access2K/SQL)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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…

Reply | Quote

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…

Reply | Quote

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.

Thanks!

Reply | Quote

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.

Thanks!

Reply | Quote

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.

Reply | Quote