Report Sum

[mystifier]

I have columns that can have values 0,1,2,3,4,5,6,7,8



I need to be able to Sum/Avg columns but ONLY where the value is <6

At the moment, they are smallint which I would like to retain but I could make them char or single if necessary ('0','1','2','3','4','5','A','B','C')

Can anyone think of any ideas?

[AccessMSSQL]

how many columns do you have and I'm assuming you need to sum/average them all at the same time?

[AccessMSSQL]

Here is an example of a query that accomplishes what you ask:

SELECT Avg(IIf([col1]<6,[col1],Null)) AS Expr1, Avg(IIf([col2]<6,[col2],Null)) AS Expr2, Avg(IIf([col3]<6,[col3],Null)) AS Expr3
FROM Table1;

[mystifier]

I have 10 columns, kpi01..kpi10 so I need =Sum(kpi01) and =Avg(kpi01) etc., in the GroupFooter and ReportFooter.

Changing to char and using Val() would be okay for =Sum but =Avg is not so easy... I don't really want to change type if I can avoid it.

Thanks.

[AccessMSSQL]

Here is a link to an old article - it's Access 2000 but the technique here should still work in 2010:

http://support.microsoft.com/kb/210458

You may just need to add 10 hidden textboxes to your report with control source = "=iif(kpi01 < 6,Null,kpi01)", etc. and base your AVG on these textboxes.

Hope this helps!

[mystifier]

Thanks AccessMSSQL.

I discarded using IIf(kpi01<6, kpi01, 0) because it gave the wrong result but I didn't think about trying Null.

[mystifier]

Perfect!

While you are on good form, my next query needs to provide:

%0s, %1s, %2s, %3s, %4s %5s discarding scores of >5 grouped by SiteRef

;-)

[AccessMSSQL]

Okay. we will tackle that one tomorrow.

[mystifier]

I hope I can do it before then

Thanks for your help.

[AccessMSSQL]

I think this is what you need. Two queries to do this and a third query based on Query1 and Query2.
Query1:
SELECT SiteRef, Sum(IIF([kpi01]>5,kpi01,0)) AS Expr1
FROM YourTable GROUP BY SiteRef;

Query2:
SELECT Sum(IIF([kpi01]>5,1,0)) AS Totalkip01
FROM YourTable;

Query3:
SELECT Query3.SiteRef, Query3.Expr1, Query4.Totalkip01, [Expr1]/[Totalkip01] AS PercKPI01
FROM Query3, Query4

[mystifier]

Sorry AccessMSSQL, I missed this reply.

What I meant was a query that returned the percentage of all kpi for each possible score of 0 to 5 (ignoring scores >5)

e.g.
SiteRef, pc0s, pc1s, pc2s, pc3s, pc4s, pc5s
===========================
Site01, 5, 15, 30, 30, 15, 5
Site02, 5, 10, 40, 30, 10, 5
...

i.e
05% of Site01 kpi scores = 0
15% of Site01 kpi scores = 1
30% of Site01 kpi scores = 2
30% of Site01 kpi scores = 3
15% of Site01 kpi scores = 4
05% of Site01 kpi scores = 5
etc.,

It is proving too clever for me (not difficult!).

[AccessMSSQL]

Try these:

Query1: This stores your totalSum of scores < 5
SELECT SiteRef, Sum(IIf([kpi01]<5,1,0)) AS TotalSum FROM YourTable
GROUP BY SiteRef

Query2:
SELECT SiteRef, Sum(IIf([kpi01]<5,1,Null)) AS Score, IIf([kpi01]<5,[kpi01],Null) AS SumScore
FROM YourTable
GROUP BY SiteRef, IIf([kpi01]<5,[kpi01],Null)

Query3:
SELECT Query2.SiteRef, Query1.Score, Sum([SumScore]/[TotalSum]) AS Perc
FROM Query2 INNER JOIN Query1 ON Query2.SiteRef = Query1.SiteRef
GROUP BY Query2.SiteRef, Query1.Score
HAVING (((Query1.Score) Is Not Null))

[mystifier]

Thanks AccessMSSQL. I am not quite sure what this gives but it produces multiple lines for each Site and concentrates only on kpi01.

I am sure there needs to be a number of count(*) in there somewhere. At the moment, I am still trying to get (even forgetting <5 filter):

SiteRef, CountOfSiteRef, CountOf0, CountOf1, CountOf2, CountOf3, CountOf4, CountOf5

eg.
Site01, 85, 5, 20, 30, 20, 20, 5
(Site01 has 85 results comprising 5 x 0, 20 x 1, 30 x 2, 20 x 3, 20 x 4, 5 x 5)

How can such a simple 'language' be so difficult ?!?

[AccessMSSQL]

Here is a query that will give you your counts, but the total SiteRefCount will need to be calculated for each column, right? You will have a different count for kpi01, kpi02,etc?

SELECT Query5.SiteRef, DCount("SiteRef","Table1","SiteRef = '" & [SiteRef] & "'") AS CountofSiteRef, Query5.CountKPI01, Query5.countkpi02, Query5.countkpi03
FROM Query5

So you will need 10 DCount functions. Dcount("SiteRef", "Table1", "KPI01 < 5 AND SiteRef = '" & [SiteRef] & "'") as CountOfSiteRefKPI01, etc.

[AccessMSSQL]

Oops...here is the source for Query5:
SELECT Table1.SiteRef, Count(IIf([kpi01]<5,1,Null)) AS CountKPI01, Count(IIf([kpi02]<5,1,Null)) AS countkpi02, Count(IIf([kpi03]<5,1,Null)) AS countkpi03
FROM Table1
GROUP BY Table1.SiteRef;