Thursday, January 13, 2011

Domain Function Example: Rolling Average in Query

So far in this series on Domain Functions, I've discussed the general syntax (Domain Functions Demystified) and problems involved in building criteria expressions (Domain Functions Demystified: Criteria Expressions). Unfortunately, many of the examples I've given are relatively trivial. So for my next few blog posts, I thought I'd give what I consider truly useful applications of domain functions.

Other Examples:
  1. Simulate AutoNumber with DMax
  2. Running Sum with DSum
  3. Numbered Query with DCount
  4. "Difference Between" with DLookup/DMax
  5. Begin Date and End Date from Effective Date
Another value that is difficult to produce in a query, is the Rolling Average for a given number of records.

For instance, suppose I wanted to display a rolling average for the last 12 weeks for the table below:


For Week 26, I need to display the average for weeks 15-26 (39.85). For Week 25, it would be the average for weeks 14-25 (43.85), and so forth. For weeks with less than 12 in the recordset, it will average only those weeks available. So Week 9 would only average weeks 7-9 (53.67).

In other words, this:

Figure 2

The problem is that SQL does not have positional notation like Excel does. There's no way to simply point to the record above the one you're on -- or the previous 12, for that matter. The only way to do it is to somehow identify the previous records in terms of a Where condition. Since this Where condition must be evaluated for each line, O can do this with a domain aggregate function or a correlated subquery. In this case, two domain functions and two subqueries.

For either method to work, I must have a unique record ID. The Autonumber field is ideal for this. It doesn't matter if there are gaps in the sequence, but I have to sort on this field, so there cannot be duplicates and they must be in the order I need displayed. In the above sample, ID fits the bill.

Domain Function Method (DCount and DAvg)

Domain Aggregate functions are an Access-only method to return statistical information about a specific set of records, whether from a table or query. DCount in particular will return the number of records in a given recordset. DAvg will return the average of a given recordset. Both functions have three arguments: 1) an expression that identifies a field, 2) a string expression that identifies a domain (that is, the table or query), and 3) a Criteria, which is essentially an SQL Where clause without the word WHERE.

The first step in this process is to create an unbroken sequence number for the records. It must be unbroken so I can subtract 12 from it to average the correct number of weeks. The second step produces the average.

Step1: DCount_RollingAverage1:

SELECT DCount("ID","Table1","ID <=" & [ID]) AS Sequence, tWeek, tValue
FROM Table1

The Order By clause in the query is important. This will sort the query on the ID field. I'll need to have that order to use the criteria argument in the DCount.

Here's how it works.

For each record in the query, Access runs the DCount function. The DCount returns the number of records in the domain where the ID in the function is less than or equal to the ID in that record of the query.

So in the first record, the ID is 1. So the DCount opens the domain (essentially opens the Customers table again) and it sees that there is only 1 record whose ID is less than or equal to 1. So it returns 1.

Then it processes the second record. The ID of that record is 3, and the DCount function sees that there are only 2 records which have an ID whose value is less than or equal to 2. So it returns 2.

It is not necessary that the Order By field is an unbroken sequence. As long as that field has unique values and is sorted, it will work.

 Figure 3

Step2: DCount_RollingAverage2:

Now that DCount_RollingAverage1is a recordset with an unbroken sequence, I can use as it as the record source for the query that will create the rolling averages:

SELECT Sequence, tWeek, tValue, DAvg("tValue","[DCount_RollingAverage1]",
"Sequence Between " & [Sequence] & " And " & [Sequence]-12) AS [12-Week Rolling Average]
FROM DCount_RollingAverage1;

For each record in the query, Access runs the DAvg function. The DAvg returns the average for the range of values between the sequence number and the sequence number minus 12.

So in the first record, the Sequence is 26. So the DAvg opens the domain (essentially opens Table1 again) and averages weeks 15-26. Then it processes the second record, averaging weeks 14-25 and so forth.

Figure 4

Subquery Method
There is no way to combine these two queries into one. To do that, I'd need to use a correlated subquery, which I may discuss at a later date. However, you can find both methods on my website in this sample: RollingAverages.mdb.


Ruby Claire said...

I have a question/ Query with two easy fields "Time frame and Milk
(daily take advantage of production). I would like to make a
query so that I can have a third area that determines a
moving or shifting typical of Milk using the last 3 days

Query forms


Unknown said...

This is a great solution, thank you. I have used DSum to create a rolling sum version fo this. However, can I offer a correction? Should the sequence not be <<& " And " & [Sequence]-11) >> in order to create a 12 week average?

Unknown said...

This is a great solution, thank you. I have used DSum to create a rolling sum version fo this. However, can I offer a correction? Should the sequence not be <<& " And " & [Sequence]-11) >> in order to create a 12 week average?

Anonymous said...

I have a question about using the rolling average in a query. Is it possible to have the rolling average update a field in an existing table so that I am able to use it in a calculation?

Thank you

Roger Carlson said...

I'm sure it can, although the specific solution would vary depending on the circumstances. However, I usually don't recommend storing calculated values. It breaks the third normal form of normalization, and leaves you open to significant data anomaly issues.

I would either use this query as the data source, or make a temporary table with a make table query, if performance is an issue.

Anonymous said...

So, if I make a temporary table with the rolling average query, then can I create a report based off of that temporary table?

Thank you

Roger Carlson said...

Yes, but you would have to be CERTAIN to run the make table query before running the report. In most cases, however, using the rolling average query as the reports data source would be better.

Anonymous said...

So, if i need to perform calculations on the rolling average results using numbers in the original table then i just need to include all of the fields from the original table in the rolling average query and then link the query to the report?

thank you