updating multiple tables

[salim]

HI all !

I have a stock control database, I want

first the item sold and recorded as sold in the sale table
secondly the item will be deleted from the stock table
third the item wil be automatically reordered in the order table.

is this possible please. I am using Dreamweaver/access and some of ASP that is generated, could you help me please.
Regards.

[Lafinboy]

Yes it certainly is possible. Just write and process the sql scripts in order:

sql = "insert into salestable your sales data"
'// process the sales sql
sql = "update stock table to adjust stock level of the individual items "
'// process the stock update sql
sql = "update/insert into reorder table with item details"
'// process the insert/update of the reorder table

You can use the same product details through each of the sql statements as required. You should also allow for error checking and rollback of updates/inserts on failure.

[salim]

thanks for the reply, but is it possible in only one command?

[terrencetaylor7]

no, sorry. With SQL you can only perform one main thing per command. So you could select from several tables or update several tables in one command, but not update and select in the same command. At least not in Access, SQL Server or Oracle that i am aware of...