I’m trying to do something that I thought would be very simple, and, of course, it’s harder than I thought it was. I want to set up and inventory locator database. I have a table with products, locations and Inventory transactions. This will be a very basic system. What I have in the transaction table is the transaction ID, Date, Order #, product ID, location, transaction type (shipping or receiving) and quantity. What I want to do, if it is shipping, subtract the amount from the total that is in the location and if it is receiving, add it to the total. I would rather not have seperate fields for in and out, rather make it so that the system checks as to the type of transaction and then sets the quantity accordingly, plus or minus. I think I can do that with beforeupdate on the quantity field, although I haven’t tried it yet. Does that sound llike it will work?
The other question I have is how do I set up the math, so that it will compute the quantity in each location and am I asking for problems if I put 2 different products in the same location? Thanks in advance for any help.
