Mark Tamura
Access's query manager is a powerful and easytouse tool. Too often, developers overlook its advantages and immediately delve into coding an exclusively VBA solution. Here's one case where using the query manager is the better choice over conventional programming.
Recently, I worked on an application that attempted to forecast how many employees we'd need in a business management department within my company. Initially, I wasn't thrilled to be assigned to the project, as I'd done many similar applications. However, this project had a slight twist to the requirements that made it very interesting.
Dynamic function
By definition, users of forecasting programs want to perform "what if" scenarios that let them see the effects of manipulating the values in the appropriate fields that make up a formula. In this case, my clients also wanted a formula that they could change every time they ran the application. They also wanted the ability to store the formula for reuse. Not only that, but each group wanted to be able to create their own formula for forecasting their staffing requirements.
Figure 1
My first impression was that this wasn't going to be all that hard to do. Looking ahead to the user interface, I quickly decided on a form with an "expression builder" interface (see Figure 1). In this simplified version of the design, there is a maximum of three key fields (or variables) involved in the formula: Cost Factor, Work Volume, and ManHours for any particular month. The user interface allows the user to build and save an arithmetic expression representing any formula by using numeric constants, basic math functions, and the three required fields. The formula is built by simply capturing and concatenating the label caption for each of the corresponding command buttons and treating them as field names in the formula (except for the ManHours button, which is a custom function). Once the user has built the formula, I store it in the tlkpFormula table, shown in Table 1.
Table 1. The tlkpFormula table.
Field name 
Field type 
Length 
Primary key? 
GroupCode 
Text 
10 
Yes 
GroupTitle 
Text 
50 
No 
Formula 
Text 
255 
No 
Then I explored the possibility of creating a function at runtime that would calculate the estimated headcount using the generated formula. I soon came to the conclusion that this method was too complicated and realized that designing the user interface wasn't the most difficult part of solving this problem. Executing the formula that I was letting my users generate was going to be the biggest hurdle that I had to face.
Query manager
My solution turned out to be very simple: I used a query. There were lots of advantages to this solution: It worked (always a good feature), it was flexible and easy to implement, and, most importantly, it let me add error handling to the routine that ran the query. With error handling in place, if my users found a way to enter an impossible formula, I'd be able to terminate processing gracefully.
The technique I employed can be broken down into three major steps:
1. Get the formula for the desired business group from the table tlkpFormula.
2. Build a SELECT query that incorporates the formula as a calculated field expression.
3. Run the query.
Here's the source code for the function that performs those three steps:
Function EstimatedHeadCount _ (strGroupCode As String, strBudgetItem As String)
' Compute head count using any stored formula ' for a given Group Code and Budget Item.
On Error GoTo Err_EstimatedHeadCount
Dim RS As Recordset Dim strSQL As String
' Default value EstimatedHeadCount = 1
' Step 1: Get the Formula for desired Group Code. strSQL = "SELECT [Formula] FROM tlkpFormula" _ " WHERE [GroupCode]='" & strGroupCode & "'" Set RS = _ CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
' Step 2: Substitute Formula into SQL statement. strSQL = "SELECT " & RS![Formula] & " AS [MyAnswer]" _ " FROM tblHeadCount" _ " WHERE [BudgetItem]='" & strBudgetItem & "';"
' Step 3: Calculate Head Count; run the query. Set RS = _ CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) EstimatedHeadCount = RS![MyAnswer]
Exit_EstimatedHeadCount: RS.CLOSE Set RS = Nothing Exit Function
Err_EstimatedHeadCount: Beep MsgBox "Missing Data And/Or Invalid Formula", _ vbCritical, "Computation Error" Resume Exit_EstimatedHeadCount
End Function 
A typical SQL statement generated by this routine might look like this:
SELECT (CostFactor * WorkVolume)/ManHours(1) _ AS [MyAnswer] FROM tblHeadCount WHERE [BudgetItem]='1.1'; 
Running this SQL statement causes the formula to be calculated and returned as the field MyAnswer. Once the answer is returned, it's stored in the MyAnswer field of the table tblHeadCount (see Table 2).
Table 2. The tblHeadCount table.
Field name 
Field type 
Length 
Primary key? 
BudgetItem 
Text 
10 
Yes 
Hcmonth 
Integer 

Yes 
CostFactor 
Single 

No 
WorkVolume 
Single 

No 
Headcount formula
A typical formula consists of these elements:
•  Cost Factor is the estimated man hour(s) per unit of work. 
•  Work Volume is the estimated unit(s) of work to be done per month. 
•  ManHours is a simple function that gets the man hour(s) per person for a particular month. 
The ManHours function contains the following code (Table 3 shows the layout for the table used by this routine):
Function ManHours(intMonth) as Single ' Get the man hours for the given month. Dim RS as Recordset Dim strSQL as String ' Default setting ManHours = 0.0 strSQL = "SELECT DISTINCTROW * FROM tlkpManMonth" & _ " WHERE MonthIndex=" & intMonth Set RS = _ CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) If Not RS.BOF And Not RS.EOF Then ManHours = RS![MonthHours] Endif RS.Close Set RS = Nothing End Function 
Table 3. The tlkpManMonth table.
MonthIndex 
MonthHours 
1 
168.0 
2 
161.0 
3 
176.0 
4 
176.0 
5 
168.0 
6 
176.0 
7 
176.0 
8 
168.0 
9 
168.0 
10 
176.0 
11 
152.0 
12 
136.0 
After all of those calculations, you might be interested in seeing what a typical set of answers looks like. Table 4 shows the figures for 12 months for one budget item. As you can see, the formulas return "partperson" quantities. In the first month, 1.9 people are required to support the work volume, and 2.98 people are required in the second month. A graph of this table allows the users to see how their headcount demand will fluctuate and use that to determine whether to increase headcount, add contractors, or redistribute the amount of work to be done.
Table 4. Sample forecast results.
BudgetItem 
HCmonth 
CostFactor 
WorkVolume 
MyAnswer 
1.1 
1 
160 
2 
1.90 
1.1 
2 
160 
3 
2.98 
1.1 
3 
160 
2 
1.82 
1.1 
4 
160 
4 
3.64 
1.1 
5 
160 
5 
4.76 
1.1 
6 
160 
1 
0.91 
1.1 
7 
160 
2 
1.82 
1.1 
8 
160 
2 
1.90 
1.1 
9 
160 
3 
2.86 
1.1 
10 
160 
4 
3.64 
1.1 
11 
160 
2 
2.11 
1.1 
12 
160 
1 
1.18 
I didn't deal with any specific errors in the error handling in my function, but I certainly could have. Some examples would be error 11 (division by zero) or 3075 (syntax error or the formula is mathematically unacceptable to the query manager).
This technique can be employed whenever you're manipulating quantitative data under indeterminate conditions in order to compute an estimated value. Many disciplines use empirical (that is, laboratory or field survey) data as a basis to make educated predictions or to determine whether any correlations exist between items of the formula. Applications supporting these uses must allow the user to "tweak" their formula until it seems right. Providing this facility lets scenarios with the data be played out by simply changing the formula value. By using a querybased solution, you can provide a very flexible calculation engine.
Read about the download ADHOCQ.ZIP on this page