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