Check out some similar questions!

jduke44 · May 15, 2006, 02:09 PM

I have built my database pretty much how I want it. Now what I want to be able to do is when my wife inputs the info in the form of what people have bought, I was hoping to have it subtract it out of her inventory automatically or right away. Can this be done without have to run a query each time? From what I have been reading I am not seeing where this is possible but I thought I'd ask.

She would already have the inventory in stock for the most part, she just needs to subtract it out when someone buys it.

LTheobald · May 16, 2006, 05:07 AM

Is this an Access database? I'm not too hot with database but I can tell you what to search for on Google. What you want is called a "trigger". So basically try searching for "Access trigger" in Google.

ScottGem · May 16, 2006, 05:43 AM

Sounds like you aren't doing inventory correctly. Stock on Hand is a calculation, not a stored amount. An inventory tracking app generally centers around a transactions table that records ALL movement of stock in and out. Stock on hand is then calculated by adding the INs and subtracting the OUTs. The form you describe that records purchases should be inputting to this transactions table.

For more on this check out this article:

Allen Browne - Inventory Control: Quantity on Hand

jduke44 · May 16, 2006, 02:29 PM

Scott, after posting this I found the other Access forums that said the same thing (not to me). I will see if I can figure this out and ask more questions as I go along. Thanks Scott for your input.

jduke44 · May 17, 2006, 02:43 PM

Scott, I was checking out the templates that Microsoft provides online and I noticed the inventory management template. I am not sure how familiar you are with what it looks like (I attached it just in case). There is a form called Products which shows the on hand count there. I played with it a little and every time I put in a transaction it did update it. Is this a proper way of doing it? Is this part of the transaction table they you said I need to do? The reason I ask is cause it seems that Microsoft doesn't go by naming conventions so if they aren't doing that write how do I know if the rest is right. This, forums, and a library book on Access is what I have been learning from.

Also, they do have an inventory transaction table. Is this what you are referring to to calculate the on hand counts?

Bare with me as I am trying to follow what they are doing here and I am just not getting it. I think my lack of accounting skills is hindering me from understanding this. Sometimes I think I am getting in over my head but seeing this isn't a huge company I am working with just my wife's small inventory of cosmetics, hopefully I should understand it soon.

Thanks.

ScottGem · May 17, 2006, 04:45 PM

If it's the Inventory template I think it is (no attachment), then its correct. I think you find that the Counts on the Product form are calculations (check the Control Source).

jduke44 · May 17, 2006, 05:04 PM

Yes they are calculations. Sorry I thought I attached it. I think I am starting to see the picture now. One more question, from the inventory table, if I wanted to get a list of all the products and see what the current on hand counts are, I would have to run a query? If so, I can run this as many times as I want?

Thanks a lot for your help Scott, I really appreciate it.

ScottGem · May 17, 2006, 06:58 PM

You can do it as a query or report. Just group by product.

jduke44 · May 23, 2006, 05:28 AM

Scott, I have another question about the transaction table. How would I make the relationship for the tables. I have the transaction , products, order, orderdetails tables. As far as forms, I already had my Customer order form then I made a purchasing form for when my wife orders her products. I am having a hard time linking these so when she places an order it places the order in the transaction table. I am also having a hard time when she puts a customer order. I have tried to connect the relationships together but I can't seem to get it right.
I have my tables setup how you told me to do it in a previous post:

https://www.askmehelpdesk.com/databa...ess-20225.html

If you point me in the right direction, I a would be grateful. If I need to be more clear let me know.

Thanks Scott.

ScottGem · May 23, 2006, 05:54 AM

Your OrderDetails table should be the transaction table. That table will have ProductID as a foreign key. It will include a quantity field. It should also include a transaction type field to identify whether the entry is an order, a sale, shrinkage (demos, damage) or whatever. Transactions fall into 2 categories; Ins and Outs.

When entering Customer Orders the transaction type can be defaulted to an Out. When entering Purchase orders you link to the same table but the type is an In.