Database Help - I have a question for the experts!

[Monster Dave]

Hey guys,

I have built an inventory system for my office and as with any tool, I am developing new components for it. One component that I currently have in place (and is working optimally) is the automatic generation of an order which is sent out to the responsible parties (for approval) when supplies need to be ordered. It recently came to my attention that it would be a value to have access the data for query/reporting purposes in a "Replenishment" table.

Here's my question. I have a form which contains details for a selected/searched supply item and within that form I have setup withdrawal and replenishment fields. Upon clicking the button to input the quantity replenished to stock, I'd like to have  the replenished amount data also put into a "Replenishment" table that includes - item name, brand, SKU, and the quantity replenished as well as a date stamp.

Obviously the reason to do this is because the moment something is replenished the order clears itself out of the system and there is no history of the $$ spent on supplies.

I'm trying (at the moment) to use an SQL insert query, but I've run into a wall. Does anyone have any better ideas of how to approach this?

Thanks! I appreciate your input!

Dave.

[ab]

Have you considered using trigger on the table?  (assuming you are using example SQL Server ). 

[Monster Dave]

The good news is that I got it to work!  Thanks for the input. I just rewrote my SQL statement from scratch and triple checked everything - it's nice to hit this sort of project right when you're fresh in the morning! Thanks! 

[duccarlos]

I was going to ask you to PM me your insert statement, but I guess you had it covered.

[Monster Dave]

Yeah thanks. I was really burned out mentally on Friday but the weekend seemed to refresh my focus and I got it figured out this morning! 

[yotogi]

SQL will do that to you. Never a bad idea to take a break and come back with a fresh brain.

Glad you got it sorted!