Using Inventory transactions to create a history of inventory levels

Hello VinceBurgess,

This is not going to be quick and easy, I fear.

The starting point has to be a table that contains an entry for every point you wish to chart.
This sounds like it will be a table of dates, on record per day.
I think there are several options from here.

My approach (beacause I'm simple) would be to have a set of such records for each inventory item with a stock field added.
Then use an update query to post all the transactions to this table.
Then do a pass through the file to calculate the day-by-day running totals of stock for each item.

Then you can use this as the source for a query for your chart.

Pete

I wanted to avoid this so that I don't have to create a large table with the running totals for all combinations of inventory items, locations etc.


   

If a point is to be plotted it must have a value to plot.  So you have to derive the stock level for every plottable point.

It should be possible to expand your query to use a primary table of all plottable dates and use the same mechanism for calculatiing the running totals but it's a bit late in the day for me to get my head round the syntax.

I'll see if anyone else can help.


Pete

Peter in defininitely on the right track,...
To report something, that something must be there to report...

Show the structure of your Inventory Tracking table... does it look like this:

  InvID
  Date
  Time (optional)
  TransType (receipt, sales, adjustment, inhouse use,... etc)
  Units (+ or - units)
  TransBy  (who made the transaction)

If your table looks like that, you can do inventory levels to the minute.....

Running totals on Report formats can handle the "balance as of" points.

shall I continue???
 

Structure is like this

Item Number
Item Description
Inventory Organisation Name  (Usually a physical location such as a warehouse)
Subinventory Code    (A logial distinction within an inventory organisation such as 'Finished goods' or 'damaged' )
UOM (Unit of Measure eg Kg or Tonnes)
Transaction Type   (Just the date,  not the time)
Transaction Date
Transaction Quantity  (Receipts are +, issues are -)

Lets say that for item number 1 there was a receipt into inventory "Warehouse1" subinventory "Finished Goods" of 100Kg on 1st January and another on 9th January.

Id like a graph to show 100 Kg in stock between 1 st and 9th January and then 200Kg until the next transaction.

The graph would be in the detail section of a report or subreport specific to an item number ( or possibly an item number and inventory Organisation Name)

There are many Inventory names and subinventories so I don't want to set up a dummy table of all possible combinations,  just fill in the gaps between transactions.

Does this make sense ?

Maybe I want too much?

I  didn’t want to create another table.

I’ve found a way to do what I want using a fairly small table and a query.

1st I’ve created a table containing one date field with all the dates I’m interested in. Currently 1 per week but could be one per day or month.

2nd I’ve used a query to find which inventories and subinventories have transactions for which items and combine these with the dates table without joining them.  This gives me a recordset with all the rows I need but without the key data (inventory level)

SELECT DISTINCT Dates.Dateref, Transactions.[Inventory Organisation Name], Transactions.[Subinventory Code], Transactions.[Item Number], Transactions.[Item Description], Transactions.UOM
FROM Transactions, Dates;

Finally, I used the above query as my record source for the graph, and added a DSUM field as the Value for the graph.  This way the calculation is only done for the records that are needed for the graph.

TRANSFORM DSum("[Transactions]![Transaction Quantity]","[Transactions]","[Transactions]![Inventory Organisation Name] ='" & [Inventory Organisation Name] & "' AND [Transactions]![SubInventory Code] ='" & [SubInventory Code] & "' AND [Transactions]![Item Number] =" & [Item Number] & " AND [Transactions]![UOM] ='" & [UOM] & "' AND  ([Transactions]![Transaction Date]) <= #" & Format([Dateref]," mm/dd/yyyy") & "# ") AS [Stock Level]
SELECT [source fields for inventory graph].Dateref
FROM [source fields for inventory graph]
GROUP BY [source fields for inventory graph].Dateref, [source fields for inventory graph].[Inventory Organisation Name], [source fields for inventory graph].[Item Number], [source fields for inventory graph].[Item Description], [source fields for inventory graph].UOM
PIVOT [source fields for inventory graph].[Subinventory Code];

Each graph is specific to an item and an Inventroy Organisation name so these are passed to the graph as the parent / child fields.

It works OK.  Any suggestions to make it a bit faster

Replace the Dsum() function.  Query that part separately and link it into a query that will be the source of the Crosstab

It's the part about "without joining them" that is costing you the speed in this query.  That Dsum() function has to run twice for every row of output in the query.
Joining property built tables is not a liability and will out-speed the use of functions for these purposes every time.

SELECT {GROUP FIELDS}, Sum([Transactions]![Transaction Quantity]) as SumQuan
FROM [source fields for inventory graph].Dateref
GROUP BY {GROUP FIELDS};

This will replace the dsum() and can be linked in my group fields to the main query

I'm struggling to find a way of getting rid of the dsum without having to create a new table.  In fact,  if I do create a new table,  I'm using a dsum to calculate the daily stock levels in that table.

My Transactions table doesn't have a record for all dates,  only the dates with transactions.  Many of the products may go several days or weeks without a single transaction.

Just to check the speed, I did create this recordset and sure enough the speed improved.  But a table containing about 2500 records of transactions (3 month's worth) becomes a table of about 42000 records of daily stock levels.  

I think I've missed where you calculale your daily stocks.  If you're not using a dsum,  are you looping through VB code calculating the running totals as you go ?  Looking at your example,  I cant see how you create a balance for a day where there isn't a record in the Inventory Transactions Table.

Every entry in an inventory system must have at least an opening balance entry, even if the balance is zero.

Running totals are a matter for the report engine.  It has a running sum total function built into it, or you can define a function to do this on forms or reports (not easy, requires coding skills.)  The problem is that a running sum must only add the item total from a given group of items before resulting a number.  When you have to define this function you will see what I mean.

In a transactional table the current balance on an item is the aggregate total of the entries for that item.

This can be a dsum() or an sql sum (as above) for that Item (my choice, for speed)
 

Thanks for your persistance.

Taking each para of your last comment.

1)  Opening Balance -   I agree.  All my opening balances were zero this year due to a change over of item codes so my current stock is just the sum of this year's transactions.

2)  I can get the running totals onto a report, but I want to plot them as a graph so my record source has to have the totals or a function to calculate the totals. I know what you mean about the complexity as there are 4 or 5 fields needed to properly define the group of items.

3)  I agree.

4)  This is where I'm struggling.
Lets say I want to plot the data for item "X" in Location "France" in Subinventory "Finished Goods"
I started the year with 0
I added 200 Kg on 2nd jan and 400Kg on 3rd Jan and sold 100Kg on 6th Jan.
I want to plot the following data as a graph.
1 Jan   0
2 Jan   200
3 Jan   600
4 Jan   600
5 Jan   600
6 Jan   500
7 Jan   500
8 Jan   500
9 Jan   500
etc
My transaction table only has records for the 2nd,3rd and 6th Jan.  The only way I can find to populate my data set is to use the DSUM function.


 

You're welcome.

You will need to create a table(s) to hold data to be used on a graph.  This table will be generic enough to use on any generic graph
You can empty and fill this table as needed to supply a graph object with data.

Table:
    SeriesName
    SeriesPosition
    XAxisName
    XAxisPosition
    XAxisValue

VinceBurgess:
  Everything about your database table schema so far tells my you have a better then average concept of how an inventory system should function (or at least it agrees with my concepts...)

  We are about to move into the very complex and possibly time consuming phase of building a routine that will take your data, and your parameters and fill the table I have described above.  From there you should be able to use the table in any number of standard graphing objects.  Nothing I have said so far is "etched in stone" as far as object name, you can decide/change these as we go, but they must meet my conventions.  This routine must be built by BOTH of us.  We may even discover some new stuff along the way, and you will be helping me with the next inventory system question that comes through EE.

  If you agree to this I will be happy to continue.  If not, I will acquire other Experts to assist you on this matter or I can ask the kind Moderator in Community Support to close the question and refund your points.

What say you VinceBurgess?
-j-

In slightly different circumstances, I'd love to accept your offer.

But I'm sorry,  I'm going to have to cut and run with the working solution.  As the lesser of two evils I've decided to run with the additional table of stock levels but to run on a weekly rather than monthly stock level.  The SQL to create the table still uses the unjoined product of the transactions table and the table of dates and still uses the dsum to populate the stock level field.

Although this didn't take me where I wanted to go....you put in plenty of useful tips and helped me with some good practice.  You should have the points. Thanks for your help.