Using Inventory transactions to create a history of inventory levels

Posted on 2004-03-30
Last Modified: 2008-02-01
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
Question by:VinceBurgess
  • 6
  • 5
  • 2
LVL 77

Expert Comment

Comment Utility
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.


Author Comment

Comment Utility
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.


LVL 77

Expert Comment

Comment Utility
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.

LVL 32

Expert Comment

Comment Utility
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???

Author Comment

Comment Utility
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?
LVL 32

Accepted Solution

jadedata earned 250 total points
Comment Utility
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...)


Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!


Author Comment

Comment Utility
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
LVL 32

Expert Comment

Comment Utility
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

Author Comment

Comment Utility
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.

LVL 32

Expert Comment

Comment Utility
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)

Author Comment

Comment Utility
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.

LVL 32

Expert Comment

Comment Utility
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?

Author Comment

Comment Utility
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.

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now