Calculations in reports

[tracymesser]

Hi

I need to run a report based on a customer survey each month, the customer
gives a score of 1 -10, at the moment this data gets put into Excel to do the
calculation but I would like to try and do this within the database to save
time exporting etc.

This is the first sum in Excel: This give the score for the month:
=((COUNTIF($G$2:G11,">=9")/COUNT($G$2:G11))-((COUNTIF($G$2:G11,"<=6")/COUNT(
$G$2:G11))))*100

We then do a percentage sum with the following:
=AVERAGE(G2:G11)*10

I would really appreciate any help with how I convert this to access language!

Thanks & Regards
Tracy

 

[KARL DEWEY]

Try this --
Field Score in table Survey.
SELECT ((Sum(IIF([Score] >=9, 1, 0)/ Count([Score]))) - (Sum(IIF([Score]
<=6, 1, 0)/ Count([Score])))) * 100 AS Some_Label, Avg([Score]) * 10 AS
Average_Score
FROM Survey;

 

[tracymesser via AccessMonster.com]

Thank you Karl, I will give it a go!
Regards
Tracy

Try this --
Field Score in table Survey.
SELECT ((Sum(IIF([Score] >=9, 1, 0)/ Count([Score]))) - (Sum(IIF([Score]
<=6, 1, 0)/ Count([Score])))) * 100 AS Some_Label, Avg([Score]) * 10 AS
Average_Score
FROM Survey;

Hi

[quoted text clipped - 14 lines]

Thanks & Regards
Tracy