Simple inventory question...

[MsqedMan]

I must be brain dead today.

I am throwing together a simple inventory program. I need to track how many of each item I have on hand. In one table, I have the items. In another table, I have the stock in/stock out numbers. I am going item by item, if you give out 3 of an item, you stock out 3, if you order 20 more in, you stock in 20. On the Items layout, I have a portal of the Stock In/Stock Out activity. I am looking to put in a summary field that tells me how many of that item I have on hand. In the Stock In/Stock Out table I have fields for Qty.In and Qty.Out. The Qty.Out is a calculated field which just *-1. Can't I just take and make a calculation field which is Sum(Qty.In) + (Qty.Out)? This doesn't seem to be working.

And as usual, I'm sure once someone points in a direction, it will come flooding back to my brain.

Thank you all for your wisdom,

tony

[Brent Durland]

I'm not sure why your Qty.Out field is a calculation. Shouldn't it store the number that you "stock out", like 3, in your example? If you have a field in the stock table for in and one for out, then you'd just need a calculation in the items table that looks at all the related stock records, like this: Sum ( stock::Qty.In) - stock::Qty.Out )

Make sense?

Another way to do this is to have a simple field in the items table for the quantity in stock, and you could simply modify this amount each time you subtract or add. You could still have a separate table for stock ins and outs, but this method becomes sluggish pretty quickly if you have a lot of inventory turnaround, since ventually the summary fields will be looking at lots and lots of stock records.

[MsqedMan]

Hello Brent,

This does make sense, and I understand it. What I don't think I have a grasp of is how to sum the Quantity.In and the Quantity.Out fields of the related records. How do you summarize the in and out records for item number 12, say? All I can get is the initial stock in number to show up in my summary. If I add or subtract any number it does not reflect in the On.Hand number.

Thanks for your help!

Tony

[Donkick]

What if you just used a single "Quantity" field?

Inventory in would be entered as "3"

Inventory out would be entered as "-3"

Then the Sum calculation works fine.

Otherwise you could create a "Net quantity" Calculation field that would take the number entered and make it a negative or positve and then Sum that field to get your answer.