Working out percentages

[darenbeaney]

Hi, I'm new to this and I've created a report that shows evaluation scores before and after for some training we are delivering. The Query I've created works out the percentage of change for each record. The problem I'm having is that I can't now summerise these percentages for each instructor.

What I'm entering to retreive the percentage score is:

=(Sum(IIf(IsNumeric([Percentage Change]),[Percentage Change],0)))/(Sum(IIf(IsNumeric([Percentage Change]),1,0)))

What i get back is as follows::banghead:

Before score = 2.85 (correct)
After Score = 4.22 (correct)
Percentage of Change = 55% (incorrect this should be 48% )

Hope that makes sense and appreciate any help you can provide.

 

[plog]

Your code calculates an average (Total Value/Number of Values), but the example you gave is giving % change in values. The formula for % changes is this:

(Value2 - Value1)/Value1


If that doesn't help, can you provide better sample data from the data source that query is base on? I don't just want 2.85 and 4.22, I need all the data that goes into those two values.

 

[darenbeaney]

Ok no problem I'll send the data through tomorrow when I'm back online. Do you need the query that works out the percentage score for each record? I'm probably making this more complicated than it should be.

 

[plog]

I don't want any of your code, just two sets of sample data. Data you are feeding into the query and what you expect the query to produce based on that input data. Include field and table/query names for your source data.

 

[darenbeaney]

Hi, attached are 2 files, the evaluation data and the query data (some rows deleted).

Note that I'm trying to present this in a report at a summary level by instructor.

So it should show:

Instructor Name | Before Score | After Score | % Change from Before Score to After Score.

Hope that makes sense.

 

[plog]

Sorry, but you resulting data has 40 columns, I'm not determining which fields you want or how to get them all. Actually looking at Average by Instructor file, not all of its columns are in the Training Evaluation data.

You're going to have to simplify your sample data to just what I need to see.

 

[BlueIshDan]

Wouldn't the change be almost ~200% lol

from 2.x to 4.x ... why are you looking at the after and comparing it to the before.

Before changes to after in my opinion. Maybe I'm just being too anal about this.

 

[plog]

No, change implies comparing it to where you started:

2.85 to 4.22 is a difference of 1.37

You started at 2.85 so that's what you compare it to:

1.37/2.85 ~ .4807

 

[BlueIshDan]

Awesome thanks!

 

[darenbeaney]

Sorry, but you resulting data has 40 columns, I'm not determining which fields you want or how to get them all. Actually looking at Average by Instructor file, not all of its columns are in the Training Evaluation data.

You're going to have to simplify your sample data to just what I need to see.

I've now attached cut down versions, hope that makes it easier to understand.

Thanks

 

[plog]

Nope. Your initial post dealt with 1 field: that last post had around 20. Better than 40, but still overwhelming.

Did you try and implement the function I gave you initially instead of the one you posted?

 

[darenbeaney]

Nope. Your initial post dealt with 1 field: that last post had around 20. Better than 40, but still overwhelming.

Did you try and implement the function I gave you initially instead of the one you posted?

Not sure how your function would work in a report. I can get the correct values in the query as seen in the file attached. I now need to summarise the query results by instructor.

 

[BlueIshDan]

You can call a function in a report if you place it in a module as a Public Function

 

[darenbeaney]

You can call a function in a report if you place it in a module as a Public Function

Hi, I'm pretty new to this, can you provide me an example of the vba that would call the query results and then work out the percentage change?

Thanks

 

[BlueIshDan]

Did plog not provide you a function?

 

[darenbeaney]

Hi, no, plog provided a formula which my query already has for each individual record (see file attached to #12).

 

[BlueIshDan]

Public Function GetChangeValue(ByVal ValueOne As Integer, ByVAl ValueTwo As Integer) As Double
      GetChangeValue = (ValueTwo - ValueOne )/ValueOne 
End Function

SELECT *, GetChangeValue(ValueOne, ValueTwo) As Change FROM TABLE