Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Ravenna, Ohio, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inventory Receipt To/From Stock (Access 97)

    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.

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Receipt To/From Stock (Access 97)

    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?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inventory Receipt To/From Stock (Access 97)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •