4 Replies Latest reply on May 7, 2009 12:06 PM by donjuancarlos_1

    Report that does inventory as of a date?

    donjuancarlos_1

      Title

      Report that does inventory as of a date?

      Post

      I have a database that tracks our inventory of steel coils.  I have another database that keeps track of inventory transactions to those coils.  When a part of the coil gets used, a user creates a transaction entry to show how much metal was used and when it was used.

       

      I want to create a report that shows how much inventory I had at any given date.  This means I would somehow have to take the original weight of the steel coil and subtract only selected transactions from it.  Does anyone know how I might do this?  And how would I prompt a user for a date?

        • 1. Re: Report that does inventory as of a date?
          philmodjunk
            

          I'd set this up much like a check book ledger. I'd create a new record each time I needed to log a change to the amount on hand much like a ledger keeps track of deposits, withdrawals and the current balance. Since each of these entries is logged by date, using a find to pull up all transactions that took place prior or on a given date, and scrolling to the most recent of these transactions would give me the inventory as of that date.

           

          You might have fields such as the following in your inventory log:

          TransDate (Date)

          Material (text--this would let you track more than just one material in the same table)

          Added (number)

          Removed (number)

          Balance (Added + Removed)

          Total (Summary, running total of balance)

           

          I'd put these fields in a list or table view layout and keep it sorted by TransDate. Then, for example, if I wanted to know the inventory as of June 1, 2008, I'd enter find mode, type in < 6/1/2008, (If I have more than one material, I'd enter/select a material in the Material field), click find and then sort the records by TransDate (ascending). The last record shown will give me my inventory.

          • 2. Re: Report that does inventory as of a date?
            donjuancarlos_1
              

            Aw, shoulda thought of that before we used the database to track approx. 200 coils for months and months.  I see now that my database is fine, I just should have made the original entry of the transaction database the acquisition of the steel coil, instead of keeping it in the main steel coil inventory table.

             

            I guess what you are saying is that there is no way to temporarily monkey with the relationship, for example throw a little SQL in there:

             

            Coil Inventory::CoilNum=Coil transactions::CoilNum  Where Coil Transactions::date < June 1st... ?

            • 3. Re: Report that does inventory as of a date?
              philmodjunk
                

              donjuancarlos wrote:

               

              I guess what you are saying is that there is no way to temporarily monkey with the relationship, for example throw a little SQL in there:

               

              Coil Inventory::CoilNum=Coil transactions::CoilNum  Where Coil Transactions::date < June 1st... ?


              You can use an inequality in a relationship. You'd just need to put "june 1st" into a date field.


              • 4. Re: Report that does inventory as of a date?
                donjuancarlos_1
                   Ah yes.  That would be a lot easier than revamping at this point.