Percent Change between months by group

[P5C768]

I have a database of about 40,000 records which I want to create a query that will identify the % change between the current and prior month's data.

I want to group on two fields, and organiztion code and a permanent/temporary field. The org. code field has about 50 unique values.

I would ultimately like to see the change in records between month's broken down by org and perm/temp.

How could this be accomplished in a query?

[weekend00]

do you have 2 tables or one table?
how can you identify the changes?(how do you know record was changed)

[P5C768]

One table, two different fields in the table. I don't really care if a record changed, I want to see if the totals changed, as in a Org. that had 10 records last month and 11 this month would have a +10% change. I also want to break it our by perm/temp so that I could see, for example that an Org. had a +5% change in perm. records and a -5% change in temp. records.

[ajetrumpet]

I can offer some help here for you as well. I ran into this problem quite a bit when I was working heavily with trends.

I wrote my own solution because I'm 99% sure you can't do this, even with stacked queries, unless you call some code to manipulate it.

This function analyzes the table being queried and returns the average percent sales increase per period (per product) over a specified length of time. In other words, if you're selling 10 items and you've been in business for one year, it will return the average sales volume increase per month over the entire year, in percent format:

Function AvgSalesPerIncrease(pName As String, _
                             pNameFld As String, _
                             pNameTbl As String, _
                             sDateFld As String, _
                             Optional tFactor As String, _
                             Optional sdate As Date, _
                             Optional eDate As Date)

'******************************************************************************
'_____________________________________________________________________________*
'                                                                             |
'THIS FUNCTION ASSUMES THAT YOU ARE ANALYZING A SALES TRANSACTION TABLE       |
'THAT HAS A DATE FIELD, PRODUCT NAME FIELD, AND TOTAL SALES AMOUNT FIELD.     |
'_____________________________________________________________________________|
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 5/23/2008                                                              *
'Purpose: To analyze the average sales trend over a specified time period.    *
'         Returns the average percent increase per Trend Period.              *
'                                                                             *
'Arguments:                                                                   *
'pName > Product Name to analyze.                                             *
'pFldName > Name of the field that holds the Product you want to analyze.     *
'pTblName > Name of the table that holds the sales transaction data.          *
'sDateFld > Name of the date field that indicates the transaction date.       *
'tFactor > Trend Factor.  This is the Grouping period.  Options shown below.  *
'    "Weekly, Monthly, Quarterly, Yearly" - if ommitted, "Yearly" is used.    *
'                                                                             *
'sDate > Start Date. If ommitted, the earliest sale date will be used.        *
'eDate > End Date.  If ommitted, today's date will be used.                   *
'                                                                             *
'******************************************************************************

On Error GoTo ErrHandler

Dim i As Long 'INDICATES WHICH PERIOD WE ARE EVALUATING (BASE 0 - total # of periods)
Dim ctr As Long 'GENERAL LOOP COUNTER
Dim fldIndex As Long 'INDEX VALUE OF THE DATE FIELD
Dim pChange As Double 'PERCENT CHANGE BETWEEN PERIODS
Dim curYear As Long 'INDICATES CURRENT YEAR WE ARE EVALUATING
Dim curPeriod As Long 'INDICATES THE PERIOD NUMBER IN THE CURRENT YEAR
Dim curTransDate As Date 'DATE OF RECORD BEING EVALUATED
Dim mPeriods As Long 'INDICATES NUMBER OF MISSING PERIODS BETWEEN EVALUATIONS
Dim prevDate As Date 'DATE OF PREVIOUS RECORD
Dim pPeriodAry() As Long 'STORES ALL TREND PERIODS BY NUMBER
Dim pSalesAry() As Currency 'STORES EACH TREND PERIOD TOTAL SALES
Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset 'SALES TABLE
Set rs = db.OpenRecordset("SELECT * FROM " & pNameTbl & " " & _
                          "WHERE [" & pNameFld & "] = '" & pName & "' " & _
                          "ORDER BY [" & sDateFld & "]", dbOpenDynaset)

'OPEN TABLE
rs.MoveLast
rs.MoveFirst
ctr = 0
i = 0

'ESTABLISH ARGUMENTS/VARIABLES
If sdate = 0 Then
   sdate = DMin(sDateFld, pNameTbl)
End If
   If eDate = 0 Then
      eDate = Date
   End If
      For ctr = 0 To rs.Fields.Count - 1
         If rs.Fields(ctr).Name = sDateFld Then
            fldIndex = ctr
               Exit For
         End If
      Next ctr
      
      'LOCATE STARTING POINT
      rs.FindFirst "[" & sDateFld & "] >= #" & sdate & "#"
         If rs.NoMatch Then 'START DATE INVALID.  END CODE.
            GoTo ErrHandler
         End If
         
'ESTABLISH TREND FACTOR FOR THE DATEPART() FUNCTION
tFactor = IIf(tFactor = "Weekly", "ww", _
          IIf(tFactor = "Monthly", "m", _
          IIf(tFactor = "Quarterly", "q", _
          IIf(tFactor = "Yearly", "yyyy", "yyyy"))))

'START ARRAYS
ReDim Preserve pSalesAry(i)
ReDim Preserve pPeriodAry(i)
   pPeriodAry(i) = i
   curYear = DatePart("yyyy", rs.Fields(sDateFld)) 'CURRENT YEAR
   curPeriod = DatePart(tFactor, rs.Fields(sDateFld)) 'YEAR'S PERIOD NUMBER
   curTransDate = rs.Fields(sDateFld)
   
   With rs

      Do Until curTransDate > eDate

         If DatePart("yyyy", .Fields(sDateFld)) <> curYear Then
            curYear = DatePart("yyyy", .Fields(sDateFld)) 'NEW YEAR
            curPeriod = DatePart(tFactor, .Fields(sDateFld)) 'NEW PERIOD NUMBER
            i = i + 1 'NEXT PERIOD INDICATOR
            
FillmPeriods: 'CHECK HOW MANY PERIODS HAVE NO DATA
               mPeriods = DateDiff(tFactor, prevDate, .Fields(sDateFld)) - 1
               
                 'FILL THESE PERIODS WITH 0's
                  While ctr < mPeriods
                     ReDim Preserve pPeriodAry(i)
                     pPeriodAry(i) = i
                        ReDim Preserve pSalesAry(i)
                        pSalesAry(i) = 0
                           ctr = ctr + 1
                           i = i + 1 'PREP FOR NEW SALES PERIOD
                  Wend
                        ctr = 0
                        GoTo NewSalePeriod
         Else
            If DatePart(tFactor, .Fields(sDateFld)) <> curPeriod Then
               curPeriod = DatePart(tFactor, .Fields(sDateFld)) 'NEW PERIOD NUMBER
               i = i + 1 'NEXT PERIOD INDICATOR
                  GoTo FillmPeriods
            End If
         End If

SameSalePeriod:
            pSalesAry(i) = pSalesAry(i) + _
            (!unitprice * !Quantity) 'ADD DATE'S SALES TO CURRENT PERIOD'S SALES
               GoTo NextSale
               
NewSalePeriod:
            ReDim Preserve pPeriodAry(i)
            pPeriodAry(i) = i
               ReDim Preserve pSalesAry(i)
               pSalesAry(i) = !unitprice * !Quantity

NextSale:
                  prevDate = .Fields(sDateFld) 'FOR NEXT REC COMPARISON
                     If .AbsolutePosition + 1 = .RecordCount Then
                        Exit Do 'LAST RECORD REACHED
                     Else
                        .MoveNext 'NEXT TRANSACTION
                           curTransDate = rs.Fields(sDateFld)
                     End If
      Loop

   End With

'ADD UP ALL PERCENT CHANGES BETWEEN PERIODS
For i = 0 To UBound(pSalesAry) - 1
   If pSalesAry(i) = 0 Then
      pChange = 0
   Else
      pChange = Round(CDbl((pSalesAry(i + 1) - pSalesAry(i)) / pSalesAry(i)), 2)
   End If
      AvgSalesPerIncrease = AvgSalesPerIncrease + pChange
Next i

'AVERAGE FOR FINAL RESULT
AvgSalesPerIncrease = Format(AvgSalesPerIncrease / (UBound(pPeriodAry) - 1), "Percent")

ErrHandler:
   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
       i = 0
       ctr = 0
       pChange = 0
       curYear = 0
       curPeriod = 0
       mPeriods = 0

End Function

I had a sales table when I initially wrote this. The query's SQL looked like:

SELECT DISTINCT tblSales.pName, 

AvgSalesPerIncrease([pName],"pName","tblSales","saleDate","monthly")

FROM tblSales;

You can adapt this to do what you need as well. If you do and you would need help with the coding, I'd be glad to help if I'm here. It is well documented though, so you can find your way around easily. Watch out for the sections that use the "(!unitprice * !quantity)" blocks of code. Those were manually written in because I did not have a totals field. I had to create them on the fly.

HTH