Need help with Percentages

[Guest]

This is my SQL statement:

SELECT Abs(Sum([Form2]="Filed")) AS Unit1Form2Filed,
Abs(Sum([CAMT]="Filed")) AS Unit1CAMTFiled, Abs(Sum([Form2]="Req & Rec")) AS
Unit1Form2ReqRec, Abs(Sum([CAMT]="Req & Rec")) AS Unit1CAMTReqRec,
Abs(Sum([Form2]="Outstanding")) AS Unit1Form2Outstanding,
Abs(Sum([CAMT]="Outstanding")) AS Unit1CAMTOutstanding, Abs(Sum([Form2]="Not
Required")) AS Unit1Form2NotRequired, Abs(Sum([CAMT]="Not Required")) AS
Unit1CAMTNotRequired, Abs(Sum(IsNull([Form2]))) AS Unit1Form2NoInfo,
Abs(Sum(IsNull([CAMT]))) AS Unit1CAMTNoInfo, Count(*) AS Total FROM tblUnit1;


I need to add percentages to each and I haven't had any luck using the
examples from this forum.

Any help would be appreciated.

Cheers

 

[Guest]

Divide by the total and multiply by 100, e.g.

Abs(Sum([Form2]="Filed"))/Count(*)*100 AS Unit1Form2FiledPerCent

I notice you are using the absolute value of the sum of an expression to
return conditional counts of rows. While this technique is often used, and
even appears in some books, its not regarded as best programming practice to
rely on the implementation of Boolean values as -1 or 0 in this way. Its
what the head of one software engineering company of my acquaintance once
described as "being unduly chummy with the implementation". In Access a
better approach is to sum the return values of IIF function calls, e.g.

SUM(IIF([Form2] = "Filed",1,0))

Ken Sheridan
Stafford, England