Introduction.
Your Company has several Customers who place orders for Products regularly and you maintain the Orders detail data in an MS-Access Table. The management would like to know the frequency of each customer order so that the company can plan and acquire adequate stock in advance to meet their requirements in time.
We have a table of Orders (tblOrders), of a particular customer, with the following fields and sample data as shown below:
| AutoID | OrderNo | OrderDate | OrderValue | Days |
| 1 | 2012060500 | 05-06-2012 | 100000 | |
| 2 | 2012070701 | 15-07-2012 | 50000 | |
| 3 | 2012109000 | 25-10-2012 | 150000 | |
| 4 | 2012120050 | 27-12-2012 | 125000 | |
| 5 | 2013028075 | 14-02-2013 | 175000 |
Our task is to find the frequency of orders, in the number of days, from this particular customer. This can be done by finding the difference between the Order Dates. The sample data records are organized in such a way that they have a sequence number in the first column. This is very important for the first method we are going to try out. It is easy to find the OrderDate in the next record with the help of the Dlookup() Function in an MS-Access Query.
Organizing the Data.
We are going to use only two columns from the tblOrders Table, AutoID & OrderDate, and will create a third column Days by finding the difference between Order Dates.
Here, the data records are organized (as shown above) in such a way that the output in the Days Column can be found with a simple Query. The Query-based solution works only when the AutoID field has consecutive values and the OrderDate is arranged in Ascending Order.
The SQL of the sample MS-Access Query is given below:
SELECT tblOrders.AutoID,
tblOrders.OrderID,
tblOrders.OrderDate,
DateValue(nz(DLookUp("OrderDate","tblOrders","AutoID = " & [AutoID]+1),"31-12-1899")) AS EndDate,
IIf([EndDate]-[OrderDate]<0,0,[EndDate]-[OrderDate]) AS Days
FROM tblOrders
ORDER BY tblOrders.OrderDate;
The result of the run of the Query is shown below:
| AutoID | OrderID | OrderDate | EndDate | Days |
| 1 | 2012060500 | 05-06-2012 | 15-07-2012 | 40 |
| 2 | 2012070701 | 15-07-2012 | 25-10-2012 | 102 |
| 3 | 2012109000 | 25-10-2012 | 27-12-2012 | 63 |
| 4 | 2012120050 | 27-12-2012 | 14-02-2013 | 49 |
| 5 | 2013028075 | 14-02-2013 | 0 | 0 |
Even though the MS-Access Query-based solution looks simple and effective, preparing data with consecutive number values is not that easy, because you will be filtering and creating output data from a larger data file and the auto-number values, if exists, will not be consecutive, if they are taken from the main table. But, you can create auto-numbers in Query Column very easily with a VBA User-defined Function. You can find the Code and details here.
The VBA-Based Solution.
The VBA-based solution doesn’t need a column with consecutive numbers. But, the OrderDate field must be sorted in Ascending Order. To prepare the data from our MS-Access Table tblOrders, as input for our VBA Program FrequencyCalc() we need only a SELECT Query with required fields from the tblOrders Table. The SQL of the sample Query is given below:
Query: tblOrdersQ – OrderDate field value is sorted in ascending order.
SELECT tblOrders.OrderID, tblOrders.OrderDate, tblOrders.Days FROM tblOrders ORDER BY tblOrders.OrderDate;
VBA Code of the FrequencyCalc() Function is given below:
Public Function FrequencyCalc()
'----------------------------------------------------------
'Author: a.p.r.pillai
'Date : March 2013
'All Rights Reserved by www.msaccesstips.com
'----------------------------------------------------------
Dim db As Database, rst1 As Recordset, rst2 As Recordset
Dim m_diff As Integer
On Error GoTo FrequencyCalc_Error
Set db = CurrentDb
'Open tblOrdersQ's first instance and position on the first record
Set rst1 = db.OpenRecordset("tblOrdersQ", dbOpenDynaset)
'Open tblOrdersQ's second instance and position on the second record
Set rst2 = db.OpenRecordset("tblOrdersQ", dbOpenDynaset)
rst2.MoveNext
'Find difference between dates from first & second instances of OrderDates
'in the same Query.
'update number of days in the second record onwards.
Do While Not rst1.EOF
m_diff = rst2!OrderDate - rst1!OrderDate
If Not rst2.EOF Then
With rst2
.Edit
!Days = m_diff
.Update
rst1.MoveNext
.MoveNext
End With
If rst2.EOF Then
Exit Do
End If
End If
Loop
rst1.Close
Set rst1 = Nothing
rst2.Close
Set rst2 = Nothing
db.Close
Set db = Nothing
FrequencyCalc_Exit:
Exit Function
FrequencyCalc_Error:
MsgBox Err & " : " & Err.Description, , "FrequencyCalc()"
Resume FrequencyCalc_Exit
End Function
Demo Run Result of VBA Code.
The run result of the Program is given below:
| AutoID | OrderID | OrderDate | Days |
| 1 | 2012060500 | 05-06-2012 | |
| 2 | 2012070701 | 15-07-2012 | 40 |
| 3 | 2012109000 | 25-10-2012 | 102 |
| 4 | 2012120050 | 27-12-2012 | 63 |
| 5 | 2013028075 | 14-02-2013 | 49 |
The VBA procedure updates the frequency of Days in the second record onwards, rather than the first record through the sample Query we have tried earlier with the Dlookup() Function.
Earlier Post Link References:
