Complex Operation

[benkingery]

Not sure if this needs to go in queries or in Modules & VBA. I have an inventory database that tracks inventory for 8 online trading partners. Each trading partner has an inventory account where various transactions are entered in order to arrive at an account balance (onhand inventory). Notoriously, we end up with negative onhand inventory on certain partners that allow for products to periodically oversell. To account for these types of "oversells" I would like to be able to automate taking inventory from a trading partner that DOES have inventory in the positive.

We are currently exporting a report of those negative onhand balances and then manually applying the changes.

A little bit about the database: Each partner has a transaction table. Call them XX_Transactions, YY_Transactions, etc. Also, I have a table where all allocations are summarized so you can see a side-by-side view of those transaction. Within this summarized view, the numbers are stored not calculated. These numbers are updated every 5 minutes with a SQL Server job that sums the transaction tables and then writes the current onhand back to the summary table, so these numbers are always accurate. (Also, tables are on SQL Server 2005 if you haven't already figured that out).

Let me know if anyone has any suggestions.

 

[benkingery]

Lots of readers, no takers on this one? I'm a little at a loss.