This note is a tutorial in how to construct a dynamic simulation of a periodic review inventory policy when given a history in tabular form of daily demands for a very large set of part numbers. The methodology is to exploit both the SQL and procedural language features of Microsoft Access. The note assumes familiarility with Microsoft Access but it could also be used to motivate the use of MS Access. The note stops short of solving all the problems associated with large scale database manipulations but uses Assignments at the end to suggest the direction to take.
If the note is used to motivate the use of MS Access, consider the keywords that were used in creating the simulation. Keywords, such as SELECT, FROM, AS, SUM, GROUP BY, and CVDATE, are indicated by uppercase type. Use these keywords and the Microsoft Access help files to decipher the details of the approach.
Given a history of customers orders, by part number, simulate the behavior of a simple inventory policy over the same period of time using different policy parameters.
100,000 part numbers, 1000 customers, 100 days. This is too large to be handled in memory. The simulation will have to make extensive use of file or database processing. The approach taken here is to exploit the relational database commands of SQL.
A table of time-dated customer orders:
TableDemandHistory:
PartNumber Text
CustomerNum Text
DateOrdered Date
Quantity Single
We decide we are not going to simulate at the customer level of detail. We only need to know the total demand for each part by day. We create a query to aggregate the demand history for us:
QueryDailyDemand:
SELECT PartNumber,DateOrdered, SUM(Quantity) AS TotalQuantity FROM TableDemandHistory GROUP BY PartNumber,DateOrdered
We will want to have a query that results in just the demands for the current day of the simulation. If the current day were fixed at, say, Nov. 11, 1997 we could use the following query:
QueryJunk
SELECT * FROM QueryDailyDemand WHERE DateOrdered = CVDATE("11/11/1997")
The function CVDATE() is a useful function available in MSACCESS for converting a string version of a date into a numeric Date field. We called this QueryJunk because it is not what we want. The problem with it is that the the date we are searching for ("11/11/1997") is hard-coded into the query. We want to use a different date for each day simulated. If we want the date we are searching for to be a variable we need to use another trick. Here is one technique: create a table that has only one record in it: that single record contains the current date.
TableCurrentDate
CurrentDate Date
There is nothing in the definition of the table that limits it to containing only one record, so we will have to exercise care to make sure that it never has more than one record. Now, we can create a query that selects just the records in QueryDailyDemand that match the date in the one record sitting in TableCurrentDate.
QueryCurrentDateDemand
SELECT * FROM TableCurrentDate LEFT JOIN QueryDailyDemand ON (TableCurrentDate.CurrentDate = QueryDailyDemand.DateOrdered)
Note that if TableCurrentDate accidentally contained more than one record, we would get more than one day's demand in the recordset resulting from QueryCurrentDateDemand. Here is another query that should have the same effect:
SELECT * FROM TableCurrentDate,QueryDailyDemand WHERE (TableCurrentDate.CurrentDate = QueryDailyDemand.DateOrdered)
Use whichever version turns out to run the fastest.
Parameter Input: We have minimum and maximum levels of desired inventory position (on hand plus on order less backorders) as well as the initial inventory in the system and the number of days lead time required when placing orders, in the form of a table:
TableInventoryPolicy:
PartNumber Text
MinPosition Single
MaxPosition Single
InitialInventory Single
DaysToDeliver Integer
We create a number of tables to record the state of the system at any point in time. The state of the system should consist of everything we need to know in order to make a decision and advance to the next day. The table TableCurrentDate is an example of a table that forms part of the state of the system: we need to know the current date first of all. What else do we need to know? Start by asking what the decisions will be. The decisions will be: how much of each part number should we order today. To answer that question we should need to know how much inventory we have of each part number and how much is already on order. That leads to the following two tables.
TableInventory
PartNumber Text
NetInventory Single
OnOrder Single
The definition of NetInventory is On hand inventory less Backorders, so it could be a negative number (indicating a backorder position). Note that there is no date field in this table. There will be a single record in this table for each possible part number. This table will not provide us with a history of inventory levels at the end of the simulation. If we wanted that history then we would need to design a larger table (one that included a date field). This table will tell us the netinventory level for each part number for the current date of the simulation.
The OnOrder field in TableInventory is a summary of all the outstanding orders. To be able to advance the simulation, we will need to know what orders are arriving on the current day. We need to expand the state of the system to include the detail of the order history.
TableOrderHistory
PartNumber Text
DateOrdered Date
DateDue Date
Quantity Single
In contrast to the TableInventoryCurrent, this table has a date field (DateOrdered) and will grow to be quite large by the end of the simulation since it will record each order placed for each part number and the date that order was ordered and was due to be delivered. The assignment at the end of this note suggests a different approach.
Now, we need two queries to complete our description of the state of the system. First, we need to know what orders are being delivered on the current date of the simulation. The orders being delivered query is easy: it is just like what we did with daily demand.
QueryCurrentOrdersDue
SELECT * FROM TableCurrentDate LEFT JOIN TableOrderHistory ON (TableCurrentDate.CurrentDate = TableOrderHistory.DateOrdered)
Next we need to know what orders are outstanding. An order is outstanding if it was ordered before the current date but is due to arrive after the current date. The following query should do the trick:
QueryOutstandingOrders
SELECT * FROM TableCurrentDate,TableOrderHistory WHERE (TableOrderHistory.DateOrdered < TableCurrentDate.CurrentDate) AND (TableOrderHistory.DateDue > TableCurrentDate.CurrentDate)
This query returns a list of records, one for each outstanding order. That is actually more than we need. All we really need to know is the total outstanding quantity on order. This next query could give us that.
QueryOutstandingOrderQuantity
SELECT PartNumber,SUM(Quantity) as QuantityOnOrder FROM TableCurrentDate,TableOrderHistory WHERE (TableOrderHistory.DateOrdered < TableCurrentDate.CurrentDate) AND (TableOrderHistory.DateDue > TableCurrentDate.CurrentDate) GROUP BY TableOrderHistory.PartNumber
This query results in a single record for each part number listing the part number and total outstanding quantity on order.
Next, we want to compute the inventory of each part number at the end of the current date. That will be the quantity we use to decide how much more to order. The ending inventory is simply the beginning inventory plus the orders due to arrive on the current date and less the demand for the current date. We need to join together several queries to get this result.
QueryEndingInventory
SELECT TableInventory.PartNumber, (TableInventory.NetInventory + QueryCurrentOrdersDue.Quantity - QueryCurrentDateDemand.TotalQuantity) as EndingInventory FROM (TableInventory LEFT JOIN QueryCurrentOrdersDue ON (TableInventory.PartNumber = QueryCurrentOrdersDue.PartNumber)) LEFT JOIN QueryCurrentDateDemand ON (TableInventory.PartNumber = QueryCurrentDateDemand.PartNumber)
The above query didn't work as intended because the QueryCurrentOrdersDue doesn't have a record for every single part number. For those part numbers, the field QueryCurrentOrdersDue.Quantity is null and the resulting field "EndingInventory" is also null. It's ugly, but here is a possible fix:
SELECT TableInventory.PartNumber, (TableInventory.NetInventory + IIF(ISNULL(QueryCurrentOrdersDue.Quantity),0,QueryCurrentOrdersDue.Quantity) - QueryCurrentDateDemand.TotalQuantity) as EndingInventory FROM (TableInventory LEFT JOIN QueryCurrentOrdersDue ON (TableInventory.PartNumber = QueryCurrentOrdersDue.PartNumber)) LEFT JOIN QueryCurrentDateDemand ON (TableInventory.PartNumber = QueryCurrentDateDemand.PartNumber)
The IIF() function checks for a null field and replaces it with 0.
The ending (net) inventory from the current date should be the starting net inventory of the following date. So, we would like to update TableInventory so that the NetInventory field matches the results of QueryEndingInventory. We need to do this in two steps. First we save the ending inventories and the outstanding order quantity into a table called TableEndingInventory and then we use that table to update TableInventory.
QueryMakeTableEndingInventory
SELECT QueryEndingInventory.PartNumber, QueryEndingInventory.EndingInventory, QueryOutstandingOrderQuantity.QuantityOnOrder AS QuantityOnOrder INTO TableEndingInventory
FROM QueryEndingInventory LEFT JOIN QueryOutstandingOrderQuantity ON QueryEndingInventory.PartNumber = QueryOutstandingOrderQuantity.PartNumber
QueryUpdateTableInventory
UPDATE TableInventory LEFT JOIN TableEndingInventory ON TableInventory.PartNumber = TableEndingInventory.PartNumber SET TableInventory.NetInventory = TableEndingInventory.EndingInventory, TableInventory.OnOrder = TableEndingInventory.QuantityOnOrder
Similarly, let's create a query to compute the amount of stock to order and then use that query in a query that appends the new orders to TableOrderHistory.
QueryCurrentOrders
SELECT TableInventory.PartNumber, TableCurrentDate.CurrentDate as DateOrdered,TableCurrentDate.CurrentDate + TableInventoryPolicy.DaysToDeliver AS DateDue, TableInventoryPolicy.MaxPosition - TableInventory.NetInventory -IIF(ISNULL(TableInventory.OnOrder),0,TableInventory.OnOrder) AS Quantity
FROM TableCurrentDate, TableInventory INNER JOIN TableInventoryPolicy ON TableInventory.PartNumber = TableInventoryPolicy.PartNumber
WHERE ((TableInventory.NetInventory + IIF(ISNULL(TableInventory.OnOrder),0,TableInventory.OnOrder) < TableInventoryPolicy.MinPosition))
The above query only selects records where net inventory plus the outstanding order quantity is below the minimum inventory position. It orders sufficient quantity to bring the ending inventory plus the outstanding order quantity up to the maximum inventory position. The OnOrder field of TableInventory is potentially null, so the IIF() function is used to replace such occurrences with 0.
The query QueryCurrentOrders returns a list of records one for each new order to be placed, by part number. We want to append that list to TableOrderHistory. We could modify the query to do it directly, but for clarity we will add a new append query.
QueryAppendCurrentOrders
INSERT INTO TableOrderHistory SELECT QueryCurrentOrders.* FROM QueryCurrentOrders
To advance to the next date we need to update the current date.
QueryUpdateCurrentDate
UPDATE TableCurrentDate SET TableCurrentDate.CurrentDate = TableCurrentDate.CurrentDate+1
Lastly, we need to delete the table TableEndingInventory when we have finished using it.
QueryDropEndingInventory
DROP TABLE TableEndingInventory
So, to advance the simulation one day, we would execute the five action queries in sequence:
QueryUpdateCurrentDate
QueryMakeTableEndingInventory
QueryUpdateTableInventory
QueryAppendCurrentOrders
QueryDropEndingInventory
Let's put all of those action queries together into one MSACCESS macro:
MacroAdvanceOneDay
OpenQuery QueryUpdateCurrentDate
OpenQuery QueryMakeTableEndingInventory
OpenQuery QueryUpdateTableInventory
OpenQuery QueryAppendCurrentOrders
OpenQuery QueryDropEndingInventory
Let's create three more action queries that will restore the database to the initial conditions we want. Let's say that at the beginning of the simulation there are no outstanding orders and the net inventory of each part is set to an initial value as stored in TableInventory.
QueryDeleteOrderHistory
DELETE * FROM TableOrderHistory
QueryInitializeInventory
UPDATE TableInventory INNER JOIN TableInventoryPolicy ON TableInventory.PartNumber = TableInventoryPolicy.PartNumber SET TableInventory.NetInventory = TableInventoryPolicy.InitialInventory, TableInventory.OnOrder = 0
QueryInitializeCurrentDate
UPDATE TableCurrentDate SET TableCurrentDate.CurrentDate = CVDate("11/11/1997")
In this case, we have hard-coded the initial date into the query. This is bad practice but serves our purpose. See the assignments at the end of the note for an alternative.
We could have a macro that initializes the database for us:
MacroInitializeSimulation
OpenQuery QueryDeleteOrderHistory
OpenQuery QueryInitializeInventory
OpenQuery QueryInitializeCurrentDate
Armed with these two macros (MacroInitializeSimulation and MacroAdvanceOneDay), you could now simulate the entire system (all part numbers) day after day by clicking the "Run" button for macro MacroAdvanceOneDay. That would not be a fun way to spend your day but it is about as far as you can go without using a procedural language. The MSACCESS macro language does not have the commands necessary to write control loops. To automatically loop over the successive days of the simulation, we need to write a small program.
Let's write a program to control the simulation loop but use the action queries to do all the "work" of the simulation. To do this, we create a module in MSACCESS and call it "ModuleSimulate". In that module, we create the following function:
Function SimulateOK() as integer
msgbox "So far so good."
End Function
So far, this function does nothing but launch a message box. That's so we can test it. We need a way to call this function. The easiest is to create a macro:
MacroLaunchSimulation
RunCode SimulateOK()
Now, when we run the macro MacroLaunchSimulation we get a message box that says "So far so good".
It is a good idea when dealing with databases to include error handling code inside every function or subroutine that interacts with the database. That way, if there is any problem, your program either recovers or pauses gracefully. Here is the standard error handling I try to put into every such routine:
Function SimulateOK() as integer
on error goto ErrorSimulateOK
msgbox "So far so good."
EndSimulateOk:
Exit function
ErrorSimulateOk:
Msgbox Error$
Resume EndSimulateOk
End Function
Note the use of two labels: EndSimulateOk, that precedes the normal exit point of the function, and ErrorSimulateOK that marks the beginning of the error handling code. Also note the convention to use the function name as part of each label. It saves having to think of what to label these two points.
A function should return a value that means something, so let's make this function return "True" if no error was discovered and "False" otherwise. We have no particular use of the return value at this point, but it is good form.
Function SimulateOK() as integer
SimulateOk = False
on error goto ErrorSimulateOK
msgbox "So far so good."
SimulateOk = True
EndSimulateOk:
Exit function
ErrorSimulateOk:
Msgbox Error$
Resume EndSimulateOk
End Function
So those few lines are standard code to include in all your functions. Now lets make it do something.
Function SimulateOK() as integer
SimulateOK = False
On Error GoTo ErrorSimulateOk
DoCmd RunMacro "MacroInitializeInventory"
For i = 1 To 3
DoCmd RunMacro "MacroAdvanceOneDay"
Next i
SimulateOK = True
EndSimulateOk:
Exit Function
ErrorSimulateOk:
MsgBox Error$
Resume EndSimulateOk
End Function
This function will now initialize all the tables and simulate three days by running the macro "MacroAdvanceOneDay" in a loop. So now you see we have the full power of SQL together with the procedural language of Basic. Whatever we cannot accomplish easily with a query, we can fall back and use the Basic language.
If you ran this program or any of the macros we have created, you would discover an irritating feature: each action query stops with a message box, forcing you to click "Ok" before it will proceed. This is a useful feature during debugging but not when you want to execute action queries in a loop. In this next version of the function we don't use the macros at all. Instead we connect to the database using a database variable and then execute each of the action queries directly.
Function SimulateOK () As Integer
SimulateOK = False
On Error GoTo ErrorSimulateOk
'create a database variable and set it to the first open database
Dim db As Database
Set db = DBEngine.Workspaces(0).Databases(0)
'run the action queries to initialize the simulation
db.QueryDefs("QueryDeleteOrderHistory").Execute
db.QueryDefs("QueryInitializeInventory").Execute
db.QueryDefs("QueryInitializeCurrentDate").Execute
For i = 1 To 3
'run the action queries to advance one day
db.QueryDefs("QueryUpdateCurrentDate").Execute
db.QueryDefs("QueryMakeTableEndingInventory").Execute
db.QueryDefs("QueryUpdateTableInventory").Execute
db.QueryDefs("QueryAppendCurrentOrders").Execute
db.QueryDefs("QueryDropEndingInventory").Execute
Next i
SimulateOK = True
EndSimulateOk:
Exit Function
ErrorSimulateOk:
MsgBox Error$
Resume EndSimulateOk
End Function
That's the essence of using MS Access to simulate an inventory policy. The following assignments would all form useful extensions to the approach. Each exercise is advanced because it is stated in general terms and its solution requires you to know or discover features of MS Access not mentioned in this note.
1. The loop in SimulateOk is hard-coded to be three days. It would be better to have the loop be driven by data in the database. We should have a table, call it TableSimulationParameters, with one record but with several fields such as StartDate and EndDate. The function SimulateOk should read those two fields and use them to control the loop.
2. The table TableOrderHistory may grow to be too large. Rewrite the simulation to use a table called instead TableOutstandingOrders from which records are deleted as soon the orders are delivered.
3. The table TableDemandHistory may be too big (it is measured in gigabytes). The simulation may be too slow as it attempts to run queries on such a large table. Suppose we break the table up into separate tables: one table for each day. The naming convention could be something like TableDemandDay001. Now suppose we had another table like the following:
TableDemandTableList
DemandDate Date
TableName Text
The table TableDemandTableList would contain a list of all the table names used for the demand history and the date associated with each table. Rewrite the simulation to look up the table name for the current date and modify the query QueryCurrentDateDemand programmatically to use the appropriate demand table.
4. We want the simulation to track certain statistics for us such as fill rate and average inventory by part number. Include queries in the simulation loop that compute and archive these statistics.