Rolling Monthly Trend Query in Access

INHOUSERES
INHOUSERES used Ask the Experts™
on
I have a report which I need to automate because it angers me to no end...
Takes me almost an entire day to produce a dozen reports.

On a quarterly basis, we produce a report where figures are based on 6 monthly rolling trend.
For example, when we run this report in march, there will be 3 new figures:

Aug - Jan
Sep - Feb
Oct - Mar

At the moment, what we are doing is exporting each month into a spreadsheet then doing calculations on these figures to get the 6 month figure.

The data is currently stuck in many different places and I plan to get everything in one place to hopefully run one query (or a procedure, or report) to get the figures I want.

My question is... how would I go about doing this because the same data could be in multiple rows?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Generally this is best built using temporary tables. That is, you build the routines that would get the first "set" of data, and dump that to a table. Then, you build routines which would give the next "set", and dump that to the same table, and so on. From there you can then build a report based on that table.
Top Expert 2009

Commented:
If the monthly ranges are based on the current date, you could create ranges in code (say to populate temp tables, as LSMConsulting suggests), or as calculated query fields.  If you can explain (in English) what the date ranges should be, relative to the current date, I could provide the syntax.

Author

Commented:
well I guess I could write a function which gives me an SQL filter for a string (see code below).

Then all I'd need is to create a query then play with it to get my figure.

The alias needs to be only the letters of the month because the x axis would be too big. So:

J - N '09
J - D '09
A - J '10
S - F '10
O - M '10

See Second Function

Then just loop through a set of months to present me my figures.

Does all that make sense?
Public Function RollingTrend(Field as String, Month as Date) as String

    SQLString = Field & " Between #" & Format(DateSerial(Year(Month),Month(Month)-5,1), "dd mmm yyyy") & "# And #" & Format(Month,"dd mmm yyyy") & "#"

    RollingTrend = SQLString

End Function



Public Function RollingTrendAlias(Month as Date) as String

    AliasString = Left(Format(DateSerial(Year(Month),Month(Month)-5,1), "mmm"),1) & " - " & Left(Format(Month,"mmm"),1) & " '" & Format(Month,"yy")

    RollingTrendAlias = AliasString

End Function

Private Sub cmdCreateFigures_Click()

Dim db as Database
Dim rst as Recordset
set db = CurrentDB

Dim mySQL as String

'blah blah, get months
For each Month in Months
    mySQL = "SELECT Figure, IIf(" & RollingTrend("MyMonth", Month) & ",1,0) As RM FROM qryMonthlyFigures WHERE IIf(" & RollingTrend("MyMonth", Month) & ",1,0))=1"
    rst = db.openrecordset(mysql, dbopensnapshot, dbseechanges)

    mySQL = "INSERT INTO tblRolling (Trend, Figure) VALUES (""" & RollingTrendAlias(Month) & """," & rst!Figure & ")
    db.execute mySQL
Next

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
If you are in March, do you really want to include the 6 months period that ends at the end of March?

I agree with LSM, I prefer to do this using a query that populates a temp table.  I'll frequently even store the time that this table was last updated, so I can determine whether I need to repopulate it or whether it is current enough.
 
Is this really going in a report?  If so, I'd make the column headers of the query/table generic, so that you didn't have to change the control source of the controls on your report.  So, assuming you have a command button on a form, you might do something like the code attached.  This would capture the end date of the report from a text field on your form, then use date ranges to identify the records whose values should be summed for each of the 6 month periods.

Then, in the reports open event, I would define the column labels caption property to coincide with the ranges generated by the SQL string.

HTH
Dale
   
               





Public Sub cmd_Report_Click

    Dim intEndMonth as integer
    Dim strSQL as String

    intEndMonth = Month(me.txt_EndDateOfReport)
    
    'delete the records from tbl_RunningSum
    strSQL = "DELETE * FROM tbl_RunningSum"
    currentdb.execute strsql, dbfailonerror

    'Create the SQL string to create the new table
    strSQL = "INSERT INTO tbl_RunningSum (Field1, Field2, Field3, Field4 " _
           & "SELECT SomeField, " _
                & "SUM(IIF([DateField] BETWEEN Dateserial(Year(date()), intMonth - 7, 1) " _
                                        & "AND Dateserial(Year(date()), intMonth-1, 0), [Qty], 0) as Rolling1, " _
                & "SUM(IIF([DateField] BETWEEN Dateserial(Year(date()), intMonth - 6, 1) " _
                                        & "AND Dateserial(Year(date()), intMonth, 0), [Qty], 0) as Rolling2, " _
                & "SUM(IIF([DateField] BETWEEN Dateserial(Year(date()), intMonth - 5, 1) " _
                                        & "AND Dateserial(Year(date()), intMonth+1, 0), [Qty], 0) as Rolling3 " _
                & "FROM YourTable " _
                & "GROUP By [SomeField] "
    currentdb.execute strSQL, dbFailOnError
    docmd.openreport "rpt_RollingSum"

End Sub

Open in new window

Author

Commented:
fyed, Thanks for your response.
Your example gives a transposed version of what I need.
When the report is run, it needs to have these 6 month rolling figures going back a long way.

The figures are from a question in a customer satisfaction survey.
This question has been asked since 2007 and each month will give us one more figure.

This means that the number of columns will expand each month, making a temp table very hard to work with.

The report is currently a line graph created in Excel.
The line graph is created via a macro once all the figures have been entered.
I personally dispise access' reporting when it comes to charts and graphs and I would much prefer programatically exporting the figures into excel and working with excel via access VBA.

If a table contains each 6 monthly figure in a row (per client/region for breakdown purposes) I can easily write queries to export.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Cannot agree with you more if you are talking about creating charts from Access data.  I hate trying to generate charts directly from Access, so I will frequently use Access to format the data the way I want it, and then export it to Excel, or will use access to create the table containing the data in the format I want it in, then use Excel to link to that table and generate the chart based on the table.

I think, from your latest post, you are saying that you want your data arranged like:

Client      Region      PeriodEnd           Period_Desc     Total

I've added the PeriodEnd as a date field to allow you to sort by date, since period Desc would correspond to your earlier post, and would not be sortable.  Is this an accurate assessment?

Going back to your original post, I'm not quite sure what you mean by your question:
"how would I go about doing this because the same data could be in multiple rows?"

Does your data change for past months?  As an example of this, what I mean is are there modifications (additions, deletions, or updates) of data for the months of Jan or Feb which come in during the month of March?  If so, then you would have to recompute any of those 6 month blocks that contain data for those months.  If now, the you would only need to generate the values for the most recent 6 month period.

It would be a lot easier to provide you with specifics if you could provide the various fields you are trying to use in your table (it looks like this includes [Client], [Region], and some other fields).  It would also be helpful if you what kind of trends are you tracking (Total Sales, Average Sales, ...).

Author

Commented:
Thanks fyed,

It's a completely unrelated question, but can you quickly point me towards how I would link excel to access?
That sounds very interesting, as we do a lot of charts from excel!

Good idea with the sorting column, I didn't consider that.
My original question was regarding a query, rather than a procedure.
What I meant was that a figure for Jun 09 (for example) would be included in:
Jan - Jun '09
Feb - Jul '09
Mar - Aug '09
Apr - Sep '09
May - Oct '09
Jun - Nov '09

Access doesn't count the same row twice.

My app is a survey app and each answer to each question is stored as:

"CompanyCode","SerialNo","QID","Question","Answer"

The particular question which has this rolling trend is QID38.
I need to remove CompanyCode from this table, because the SerialNo can get the CompanyCode, RegionCode and the Month I need.

QID38 is an 11 point scale, 0-10.
the score is calculated by:
(the percentage of people who answered 9 or 10) - (the percentage of people who answered 0 to 6)

So, scores range from -100 to 100.

Anyway, all I need to do is merge the figures from that table, for QID38 into a new table, using the procedures we've discussed above.

In terms of data changing in past months... Possibly.
It's rare, but it can happen, but it only happens to the latter months rather than earlier, so if I re-calculated the 3 previous months it would be safe.

So each time the report is run, there will be 3 new pieces of data, but if i calculate 6 then it compensates for any old data which has been updated.

all making sense?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial