Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1105
  • Last Modified:

Using Inventory transactions to create a history of inventory levels

I have a table (Table 1) of Inventory Transactions containing fields :
Inventory Name; SubInventory Code; Item Number; Transaction Date; Transaction Quantity.

I have created a query to show stock levels following a transaction by using
Stock Level: DSum("[Table1]![Transaction Quantity]","[Table1]","[Table1]![Inventory Name] ='" & [Inventory Name] & "' AND [Table1]![SubInventory Code] ='" & [SubInventory Code] & "' AND [Table1]![Item Number] =" & [Item Number] & "’ AND  ([Table1]![Transaction Date]) <= #" & Format([Transaction Date]," mm/dd/yyyy") & "# ")

I want to graph a history of stock levels vs time.

I used a crosstab to generate the source data for the graph.

BUT,  If there are no transactions on a given day for a given item in a given inventory,  there are no records in Table 1 and so the crosstab shows no data and the graph shows no stock.

I want the graph to show a straight line until the next transaction shows a change to the  stock level.

Any ideas
  • 6
  • 5
  • 2
1 Solution
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.

VinceBurgessAuthor Commented:
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.

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

jadedataMS Access Systems CreatorCommented:
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:

  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???
VinceBurgessAuthor Commented:
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?
jadedataMS Access Systems CreatorCommented:
Perfect!!!!!  You want EXACTLY from you inventory system what I wanted from mine apparently.  The ability to say where all my stuff was at any given minute!!

BUT FIRST!!!  Change the field name "date" from that reserved built-in function keyword to "InvDate"  And if you are not thoroughly entrenched in the field name, remove spaces (replace with underbar "_") or other special characters or numbers.
Transaction Type   (Just the date,  not the time)       {TransType}
Transaction Date                                                   {TransDate}
Transaction Quantity  (Receipts are +, issues are -)  {TransQuan}

Each "Item" in inventory needs an "Opening Balance" entry to start the process off

For ease of graphing now consider a table that has been built to house interim periods of activity (regardless of length of period

Period:  (text,25)  Q4h,  day, week, month, 10day, 15day, bimonthly
PeriodID:    20040401, (date formatted to sort correctly for day)
                 200404010000 (date/time formatted to sort correctly for q4h 00:00am)
                 200404010400 (date/time formatted to sort correctly for q4h for 04:00am)

Clear your balancing Table above
INSERT INTO ( Period, PeriodID InvID, Credits, Debits )
  "day" as Period,
  format([InvDate],"yyyymmdd") as PeriodID,
  Sum(iif([TransQuan]>=0,[TransQuan],0)) as Credits,
  Sum(iif([TransQuan]<0,abs([TransQuan]),0)) as Debits,
FROM InvTransactions
GROUP BY "day", format([InvDate],"yyyymmdd"), InvID;

then you can interate down thru the record in a routine that will "current balance record in each inventory and set the Balance field.

side note:  In queries where you want zero's to represent null stock or no record use the Nz() function to translate nulls to zeros

You should be able to build your graph off of the above filled table (the tables can be archived to support each graph.....hint hint...)


VinceBurgessAuthor Commented:
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
jadedataMS Access Systems CreatorCommented:
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

This will replace the dsum() and can be linked in my group fields to the main query
VinceBurgessAuthor Commented:
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.

jadedataMS Access Systems CreatorCommented:
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)
VinceBurgessAuthor Commented:
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
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.

jadedataMS Access Systems CreatorCommented:
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.


  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?
VinceBurgessAuthor Commented:
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 put in plenty of useful tips and helped me with some good practice.  You should have the points. Thanks for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now