finding percent change between two rows

[gagnonconsulting]

Hi,
This is driving me crazy, but I am sure I am missing something simple. I have built an Access 2007 report that shows 2 rows of sales data from each of a bunch of store locations. My table has the date, the location and the sales_amount. I need to have my report group on locations. The resulting rows include a date and the sales_amount, and I have been able to display the sales_amount for a requested date (through a parameter query) and the sales_amount from the same week in the previous year, and these rows are shown for each location. But I now need to be able to show the $ change in sales_amount and the percent change in sales_amount for each location between the current and previous year. This means that I somehow need to access data from (both of) the grouped rows and calculate and display the results. How do I access data from multiple rows? Do I have to create the report manually using VBA, and if so, any examples out there?

Here is an example of what I am trying to accomplish:
Table Sales has Location, Date and SalesAmount, and records such as:

loc1, 10/10/2007, $40
loc2, 10/10/2007, $40
loc3, 10/10/2007, $60
loc1, 10/10/2008, $50
loc2, 10/10/2008, $30
loc3, 10/10/2008, $70

I want a report that shows:
--- start of report ---

loc1
10/10/2008 $50
10/10/2007 $40
$change = $10 +25%
loc2
10/10/2008 $30
10/10/2007 $40
$change = -$10 -33%
loc2
10/10/2008 $70
10/10/2007 $60
$ change - $10 +17%

----- end of report -----

[NeoPa]

This will need to be done in code. Queries (SQL) have no concept of relative records.

[ADezii]

Too close to bedtime, but given your demonstrated format, this code will produce the desired results, but only in a Linear Fashion:

Dim strSQL As String
Dim MyDB As DAO.Database
Dim rstSales As DAO.Recordset
Dim rstClone As DAO.Recordset

strSQL = "SELECT Sales.Location, Sales.Date, Sales.[Sales Amount] " & _
         "FROM Sales ORDER BY Sales.Location, Sales.Date DESC;"
         
Set MyDB = CurrentDb
Set rstSales = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
Set rstClone = rstSales.Clone

rstSales.MoveFirst
rstClone.MoveFirst
rstClone.Move 1     'Move to the 2nd Record in Clone

With rstSales
  Do Until rstClone.EOF
    If ![Location] = rstClone![Location] Then
      Debug.Print ![Location] & " | " & ![Date] & " | " & ![Sales Amount] & _
                  " | " & rstClone![Date] & " | " & rstClone![Sales Amount] & " | " & _
                  Format$(![Sales Amount] - rstClone![Sales Amount], "Currency") & _
                  " | " & Format$((![Sales Amount] - rstClone![Sales Amount]) / ![Sales Amount], "Percent")
    End If
    rstClone.MoveNext
    .MoveNext
  Loop
End With

rstSales.Close
rstClone.Close
Set rstSales = Nothing
Set rstClone = Nothing

OUTPUT:

loc1 | 10/10/2008 | 50 | 10/10/2007 | 40 | $10.00 | 20.00%
loc2 | 10/10/2008 | 30 | 10/10/2007 | 40 | ($10.00) | -33.33%
loc3 | 10/10/2008 | 70 | 10/10/2007 | 60 | $10.00 | 14.29%

NOTE: Any questions, feel free to ask.

[gagnonconsulting]

Thank you very much. So I have to use VBA and create a custom Report, which in hindsight, seems obvious. I will go and get a Access/VBA book and figure out the details, but this has helped me get started.

[NeoPa]

Sounds like a plan :)

We're here if you need help of course.

Welcome to Bytes!