3 Replies Latest reply on Jun 28, 2011 8:33 AM by philmodjunk

    Sum of total inventory

    MartijnvR

      Title

      Sum of total inventory

      Post

      Hi there,

      Im developing a simple inventory database. I thought of a setup like this;

      3 tables: - Used materials, - Bought materials and -Status.

      In the table Used materials the amount of materials used will be imported from Excel files.

      In the table Bought materials I will manually put in the materials bought.

      And in the table Status the information about the current invenotry status. For example if the filters are almoste all used, adn we have only 1 box left, a script will make sure a message will pop up like 'Buy new filters'. Those scripts and layouts are all fixed, but here is the thing.

      I need to make a Total (sum) of all the productes used in the Used materials table. The import process will make a record for each project and the materials used on that project. But we have a shitload of projects so alot of records aswell. But the only info I need is the sum of all those materials used, export those in the 'Status table' and make a Calc like 'Bought filters - Used filters' which will give a number.

      This number will be used in the status calc; if filters left < 10 ; Order new filters!

      So the main problem is the sum of all those used materials.

      Anyone a solution?

      Thanks in advance.

        • 1. Re: Sum of total inventory
          philmodjunk

          You might use two tables instead of three. The Bought and Used tables could be a single table with separate fields for Bought and Used Quantities. This would allow you to set up a kind of "bookkeeping ledger" where "Bought" quantities are your debits and "Used" are your Credits. Calculation and Summary fields could then compute running balances to not only show quantities on hand, but how levels have changed over time (a useful way to see if re-order levels need adjusting).

          Working from your existing setup, you can relate your tables by an ItemID field defined in all three tables but generated as a serial number in Status. In Status, define one record for each item in your inventory. In the other two fields, create a new record each time you record an update entering the amount sold or used for a given item each in different records in these tables.

          Your relationships would look like this:

          Bought::ItemID = Status::ItemID
          Used::ItemID = Status::ItemID

          In status, you can compute amount on hand with this expression in a calculation field: Sum (Bought::Qty) - Sum (Used::Qty)

          An expression such as: If ( Sum (Bought::Qty) - Sum (Used::Qty) < ReorderPoint ; "Re-order this item" )

          can be used to identify all products in Status that have a current amount on hand less than their specified re-order levels.

          • 2. Re: Sum of total inventory
            MartijnvR

            Hi there Phil,

            Sorry for the late replay. I started working on the database again today and I still cant figure it out.

            There is a record made for each new project with diffrent materials used, and I still cant get a sum for 1 field in all records.

            Lets say my database looks a bit like this

            Projectname, ID, Tape used, Filters used

            X, 1, 5, 5

            X, 2, 10, 10

            And then another 2 fields  Tape bought and Filters bought, lets say both 20

            I need a sum field where it will do bought tape (20) - Sum of all tape used in all records.

            Because if I use the calc Sum it will only sum up the tape used in that record.

            Thanks in advance

            • 3. Re: Sum of total inventory
              philmodjunk

              You need more tables for this. In Manufacturing systems a Bill of Materials table (BOM) is used to list the materials ID's and Quantities required to build one item. Then a Production table records how many items were produced during each production run. A script can use the information in the BOM multiplied by the production numbers recorded in the production table to compute the amount of each material used and can then update an invenory log to document the decrease in materials and also the increase in product.

              Production>-----Projects---<BOM
                                          |
                                          ^
                                      InventoryLog

              Projects::ID = BOM::ID
              Projects::ID = Production::ID
              Projects::ID = InventoryLog::ID

              This is just the bare skeleton of the basic tables.