Exploiting the Query Manager

<< Click to Display Table of Contents >>

Navigation:  Queries >

Exploiting the Query Manager

Mark Tamura            
 
Access's query manager is a powerful and easy-to-use 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.

199905_MT1 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 "part-person" 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 query-based solution, you can provide a very flexible calculation engine.
 
This can be found in the download ADHOCQ.ZIP

This can be purchased with all the other downloads on this page