By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

finding percent change between two rows

P: 2
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 -----
Oct 25 '08 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,492
This will need to be done in code. Queries (SQL) have no concept of relative records.
Oct 25 '08 #2

ADezii
Expert 5K+
P: 8,638
Too close to bedtime, but given your demonstrated format, this code will produce the desired results, but only in a Linear Fashion:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim MyDB As DAO.Database
  3. Dim rstSales As DAO.Recordset
  4. Dim rstClone As DAO.Recordset
  5.  
  6. strSQL = "SELECT Sales.Location, Sales.Date, Sales.[Sales Amount] " & _
  7.          "FROM Sales ORDER BY Sales.Location, Sales.Date DESC;"
  8.  
  9. Set MyDB = CurrentDb
  10. Set rstSales = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  11. Set rstClone = rstSales.Clone
  12.  
  13. rstSales.MoveFirst
  14. rstClone.MoveFirst
  15. rstClone.Move 1     'Move to the 2nd Record in Clone
  16.  
  17. With rstSales
  18.   Do Until rstClone.EOF
  19.     If ![Location] = rstClone![Location] Then
  20.       Debug.Print ![Location] & " | " & ![Date] & " | " & ![Sales Amount] & _
  21.                   " | " & rstClone![Date] & " | " & rstClone![Sales Amount] & " | " & _
  22.                   Format$(![Sales Amount] - rstClone![Sales Amount], "Currency") & _
  23.                   " | " & Format$((![Sales Amount] - rstClone![Sales Amount]) / ![Sales Amount], "Percent")
  24.     End If
  25.     rstClone.MoveNext
  26.     .MoveNext
  27.   Loop
  28. End With
  29.  
  30. rstSales.Close
  31. rstClone.Close
  32. Set rstSales = Nothing
  33. Set rstClone = Nothing
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. loc1 | 10/10/2008 | 50 | 10/10/2007 | 40 | $10.00 | 20.00%
  2. loc2 | 10/10/2008 | 30 | 10/10/2007 | 40 | ($10.00) | -33.33%
  3. loc3 | 10/10/2008 | 70 | 10/10/2007 | 60 | $10.00 | 14.29%
NOTE: Any questions, feel free to ask.
Oct 26 '08 #3

P: 2
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.
Oct 27 '08 #4

NeoPa
Expert Mod 15k+
P: 31,492
Sounds like a plan :)

We're here if you need help of course.

Welcome to Bytes!
Oct 27 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.