Table design and query to report rolling average of multiple site scores

Question

Using MS Access 2007.

We've established a metric for approximately 50 sites that are calculated once a month and stored in a table. We want to establish a report that is generated to show the last month's score along with a rolling 3, 6 and 12 month average for trending. First we setup the table as below since calculating the rolling averages were easy via SQL using four SELECT TOP N statements joined with UNION ALL.

score_date  site1  site2  ...  site50
date1       x1%    x2%         x50%
date2       y1%    y2%         y50%

Query to calculate rolling averages:

SELECT roll, AVG(site1) AS site1Avg, AVG(site2) AS site2Avg, etc
FROM (
SELECT TOP 12 'roll12' AS roll, *
FROM tblAuditScore
ORDER BY score_date DESC) AS a
GROUP BY roll

UNION ALL

SELECT roll, AVG(site1) AS site1Avg, AVG(site2) AS site2Avg, etc
FROM (
SELECT TOP 6 'roll06' AS roll, *
FROM tblAuditScore
ORDER BY score_date DESC) AS b
GROUP BY roll

UNION ALL

etc

This works well for calculating the rolling averages but we ran into a problem generating a report as there is no unpivot function and using UNION ALL with a PIVOT as shown in the link below with 50 sites is cumbersome with 50 union queries pulling data from the base query above to find the last score, 3 month, 6 month and 12 month average (but will use if needed).

Access Union/Pivot to Swap Columns and Rows

Next we looked at laying out the table as below which can easily create the original table using a crosstab query. However, this now results in 200 unique queries as there are four separate queries for each site to find the last score, 3 month, 6 month and 12 month average. The first method at least allowed the underlaying query to remain the same.

score_date  site   score
date1       site1  x1%
date1       site2  x2%
date2       site1  y1%

As the sites that are assigned to our region fluctuate, we currently plan to loop through the table as a recordset and create several massive queries via VBA prior to generating the report. Are we completely missing a simple solution or approaching this the wrong way? Let us know if any clarifying information is needed. Thanks.

Additional Info

Source data is a query with a UDF that outputs this information

site_code  score_date  audit_score

tblAuditScore is the two different table layouts above

Answer 1

I have revised to use "Iif" (instead of Case/When which is SQL Server)... ... I still do NOT have a clear list of columns in the raw data. Let me propose the following --

Select
    site,

    AVG(Iif Beg1Mo <= score_date 
    and score_date <= EndDate, score, null) as Last1Mo,

    AVG(Iif Beg3Mo <= score_date 
    and score_date <= EndDate, score, null) as Last3Mo,

    AVG(Iif Beg6Mo <= score_date 
    and score_date <= EndDate, score, null) as Last6Mo,

    AVG(Iif Beg12Mo <= score_date 
    and score_date <= EndDate, score, null) as Last12Mo

From raw_data 
Where    Beg12Mo <= score_date 
  and score_date <= EndDate  
Group by site

and in my next post worry about computing the Beg-dates and EndDate

Answer 2

Maybe someone will point out a better way to do this, but here is how I solved calculating rolling averages for multiple sites. A query is run for each site iteratively. Changing the arrMonth string quickly allows different rolling averages to be calculated. If enough scores have not been calculated for the rolling average (eg four scores for a six month rolling average) then it will not be logged.

tblAuditScore

score_key           site_code  score_date  audit_score
yyyymm+<site_code>  site1      date        score

tblAuditScoreRoll

roll_key                 site_code  roll_period  roll_date  roll_score
yyyymm+<site_code>+<xx>  site1      period       date       score

Above xx is two digits for rolling average 03, 06, etc. Period are the field names you want to use in your report.

Dim rs As Recordset
Dim qdf As QueryDef
Dim strSQL As String
Dim strSQLBase As String
Dim strTable As String
Dim strTableRoll As String
Dim arrMonth() As String
Dim i As Integer

strTable = "tblAuditScore" 'table to store scores
strTableRoll = "tblAuditScoreRoll" 'table to store rolling averages
arrMonth = Split("03,06,12", ",") 'modify array to contain any rolling averages desired

'open query with audit score into recordset
strSQL = "" _
    & "SELECT Format(DATE(),'yyyymm') & site_code AS score_key, " _
        & "site_code, " _
        & "DATE() AS score_date, " _
        & "audit_score " _
        & "score_weight " _
    & "FROM qryAudit_report;"
Set rs = dbLocal.OpenRecordset(strSQL)

'read all plants from query to generate multiple queries to populate table
If Not (rs.EOF And rs.BOF) Then

    'define base SQL string to be used repetitively
    strSQLBase = "" _
        & "SELECT MAX(score_key) & '<<xx>>' AS roll_key, site_code, 'roll<<xx>>' AS roll_period, MAX(score_date) AS roll_date, ROUND(AVG(audit_score),4) AS roll_score " _
        & "FROM (" _
        & "SELECT TOP <<xx>> score_key, site_code, score_date, audit_score " _
        & "FROM " & strTable & " " _
        & "WHERE site_code='<<site_code>>' " _
        & "AND score_date BETWEEN DATESERIAL(YEAR(DATEADD('m',-<<x>>,DATE())),MONTH(DATEADD('m',-<<xx>>,DATE()))+1,1) AND DATE() " _
        & "ORDER BY score_date DESC) AS u<<xx>> " _
        & "GROUP BY site_code " _
        & "HAVING COUNT(audit_score) >= ROUND(<<xx>>*5/6,0) "

    rs.MoveFirst
    Do Until rs.EOF = True

        If DCount("[score_key]", strTable, "[score_key]='" & rs!score_key & "'") = 0 Then
            strSQL = "INSERT INTO " & strTable & " (score_key, site_code, score_date, audit_score, score_weight) " _
                & "SELECT " & rs!score_key & ", " & rs!site_code & ", #" & rs!score_date & "#, " & ROUND(rs!audit_score, 4) & ", " & rs!count_AUFNR & ";"
            dbLocal.Execute strSQL, dbFailOnError

            strSQL = "" 'clear string
            'generate SQL for all rolling averages defined
            For i = LBound(arrMonth) To UBound(arrMonth)
                strSQL = strSQL & Replace(Replace(strSQLBase, "<<xx>>", arrMonth(i)), "<<site_code>>", rs!site_code) _
                    & vbNewLine & vbNewLine & "UNION ALL "
            Next i

            'remove trailing UNION ALL
            strSQL = Left(strSQL, Len(strSQL) - 14)
            'insert results into table
            strSQL = "INSERT INTO " & strTableRoll & " (roll_key, site_code, roll_period, roll_date, roll_score) " _
                & "SELECT * FROM (" & strSQL & ") AS q;"

            'create temp query to insert rolling average into table
            Set qdf = dbLocal.CreateQueryDef("")
            With qdf
                .SQL = strSQL
                .Execute dbFailOnError
                .Close
            End With

        End If

        rs.MoveNext

    Loop

'add code to export report

Else
    MsgBox "There are no records in the query to store in " & strTable
End If

rs.Close
Set rs = Nothing

TLDR

The above loops through all sites for each rolling average required using the following six month rolling average example:

SELECT MAX(score_key) & '06' AS roll_key, site_code, 'roll06' AS roll_period, MAX(score_date) AS roll_date, ROUND(AVG(audit_score),4) AS roll_score 
FROM (
SELECT TOP 06 score_key, site_code, score_date, audit_score 
FROM strTable  
WHERE site_code='<<site_code>>' 
AND score_date BETWEEN DATESERIAL(YEAR(DATEADD('m',-6,DATE())),MONTH(DATEADD('m',-6,DATE()))+1,1) AND DATE() 
ORDER BY score_date DESC) AS u
GROUP BY site_code
HAVING COUNT(audit_score) >= 5