Inventory Receipt To/From Stock (Access 97)

Topic

New Reply

My question is, I’m looking to track Raw Material Inventory. I have tree tables one is the starting inventory with all Stocked raw material it description, material type, UOM, and QTY. The second table is Receipt To Stock with all items as they come in according to date, the item , and QTY. Final the third table is Receipt From Stock, which would contain information as Receipt To Stock except the Date the material was removed from stock. I’m haven trouble trying to make a queries that would take the number of pipe that was on receipt to Stock and add it to the Total inventory , and subtract the number of pipe that was removed from stock. Example
Present we have 5 sticks of 8″ pipe in stock , 3 came in on 2-9-03 for stock, but we pull 4 to cut on 2-11-03cut 4. Then on a report i would print up at the end of the week it would say we have 4 sticks of 8″ pipe in stock.

Reply | Quote

Replies

Instead of 3 tables I would use 1 or 2 and use some columns to specify the types of transactions. This way you could set rules that a to stocj transaction uses positive number for a to stock transaction and a negative number for a from stock transaction. Then you could build a totals query and use sum to get your quantity in stock. Unless there’s something I’m missing?

Reply | Quote

In a private message you wrote:

“I have one question how do i get a running tally of my Inventory. I have the number for example one item 3, 9, -3. How do I get the totals for that item to come out 9 and not show all three entries. I tried SUM but of course that wouldn’t work cause it tries to find same entries. Could you help me out. Is it a D-Sum?? I’m not too sure.”

Which column describes or identifies the item you need to sum? You should be able to create a query where you group by this column and sum on the amount column.

Reply | Quote