Solved

Calculating values using data from grouped rows

Posted on 2008-10-22
13
267 Views
Last Modified: 2013-11-28
I have a query that returns one or two rows (typically) and would like to be able to calculate the difference and percent change between one of the fields to show it in a report. Specifically, using the TotalSales from row A, which is this year, and TotalSales from row B, which is last year, and having the group values show the difference of A-B and percent change from the same.
0
Comment
Question by:Rich
  • 7
  • 6
13 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 22782676
Try In a new column type the following

PercentChange: 100*((sum([rowB])-sum([rowA]))/sum([rowA])

0
 

Author Comment

by:Rich
ID: 22795620
The question is how to access just rowA and rowB in the query?
0
 
LVL 16

Expert Comment

by:Sheils
ID: 22799831
I am not sure that I understand what you are trying to do. I believe you wanted to have a report that shows the percentage change.

In that case just do all you calculation in the query. Then create a report from the query.

You can also create the recordset programatically from within the report but that's a little bit more complicated
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Rich
ID: 22800182
That is correct: percentage change from valueA in rowA to valueA in rowB
So if 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 -----


I can get everything except the $ change and the percent change, since I do not know how to reference data in the two rows needed as the same time.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 22800324
OK in that case just perform the calculation from the report text box.

Let's say the $50 is in textbox1 and the $40 is in textbox2

then you create the $ change textbox as an unbounded textbox and set the record source to

= [textbox2]-[textbox1]

You also need an unbounded textbox for the percentage. Its record source will be

=[textbox$change]/[textbox2]  set its format to percentage
0
 

Author Comment

by:Rich
ID: 22804622
Are you saying that the returned row values of each line in the report are in a 'addressable' textbox, and that I have access to them? If so, what is the syntax? So if I was to use an OnLoad event for the report, I could refer back to each value in the report and calculate/populate the $ and % change?
0
 
LVL 16

Expert Comment

by:Sheils
ID: 22804976
Try the following code for the onload event

Me.textbox$change = Me.textbox2-Me.textbox1
Me.TextboxPercent = Me.textbox$change/Me.textbox2


0
 

Author Comment

by:Rich
ID: 22808163
The problem is that I don't see how the text box with the value is named textbox1 and textbox2, it is named [Total Sales], without number; there is only one text box on the report, in the repeating detail section, called [Total Sales]. Can multiple (rows) of data in [Total Sales] simply be referenced by adding a 1 or 2 after the name. I don't think so, and I tried your code, but the Me.AmountChange and Me.PercentChange were not populated.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 22820034
send a screen shoot of your report so that I can have a beter understanding of how its designed

The name textbox1 and textbox2 are just genreic names that I am using as example. Your code must contain the actual name of the relevant fields. You have to find out the name of the field that holds the total sale for this year and last year. To find this open your report in design view. Right click on the textbox that holds these value and click property. Go to the "other" tab. The name of the field will be on the first row.

"I don't think so, and I tried your code, but the Me.AmountChange and Me.PercentChange were not populated. "

Attach a snippet of your code so that I can have a look and try to figure out where you've gone wrong.


0
 

Author Comment

by:Rich
ID: 22821613
Thanks for helping. I have added screen shots of the report witht the two fields that I am trying to calculate, [Dollar Change] and [Peercent Change], which should be based on the [Total Sales] values from the two rows above. I have also added a shot of the report design, showing that the values that I am trying to get are in [Total Sales], but from multiple rows, which is what I am having questions about getting.

There is no code to calculate the [Dollar Change] and [Percent Change] values at this time since I do not know how to reference the [Total Sales] variables.
report.gif
report-design.gif
0
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
ID: 22824942
I see why this approach is not working. The [Total Sale] is the same for both current and previous year. The report from report view was unreadable but the design view has given ne a fairly good idea of what the problem is.

You will need to create a query recordset at the on current event. Then use the field from the recordset to  populate the unbounded fields


 
0
 
LVL 16

Expert Comment

by:Sheils
ID: 22825155
You are probably not familiar with sql and record sets. There a few websites that might help you out. And do feel free to send a sample/copy of your database and I fix it for you. This might be the most dtraight forward way of solving this. But you will have to send it in the 2003 format

http://www.codecoffee.com/articles/sql1.html
http://www.w3schools.com/sql/sql_syntax.asp
http://articles.techrepublic.com.com/5100-10878_11-1050307.html

Plus some stuffs on recordset attached



Pages-from-Access-2007-VBA-Progr.pdf
0
 

Author Closing Comment

by:Rich
ID: 31509033
Thanks for looking into this for me.
Actually, I figured out at least one solution without having to manually create the recordset, which is to add some VBA code to the Format event of the various sections of the report. The VBA collects and summarizes the data just printed on the report in Public variables, and then calculates and fills in the results in unbound variables that I added to the footer sections.

Not sure if this is the best or most effecient way to solve this, and it only works with Print Preview.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2007 Switch To/Retry/Cancel Pop-Up 6 30
Create report using crosstab query 11 29
is Microsoft Access going to Die? 9 42
Filter a form 8 12
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now