Convert results to percentage

[andrewf10]

I have a query which is based on another (SearchCriteria2)...the SQL is as below:


SELECT SearchCriteria2.[Problem Source], Sum(SearchCriteria2.[CountOfProblem Source]) AS [SumOfCountOfProblem Source]
FROM SearchCriteria2
GROUP BY SearchCriteria2.[Problem Source];



This gives an output like this:

Problem Source...................SumOfCountOfProblem Source
Authorisation......................1
Availability.........................363
Back Order Report...............64
Breakage...........................2
Collateral Order...................25
Complaint..........................3
Credit...............................7



What I'm trying to do is add another field to this query which will express the "SumOfCountOfProblem Source" column in percentage terms so I can eliminate those below 1%.

This would give a result like this:

Problem Source..................SumOfCountOfProblem Source........Percentage
Availability.........................363..........................................8.06451613
Back Order Report...............64...........................................13.76344086
Collateral Order...................25...........................................5.376344086
Credit................................7............................................1.505376344


Can someone please give me some inspiration?

Thanks in advance

 

[Jon K]

You can incorporate DSum() in your query as the Denominator in the percentage calculation.


Or build a new query based on your query:-

SELECT [Problem Source], [SumOfCountOfProblem Source],
[SumOfCountOfProblem Source]/DSum("[SumOfCountOfProblem Source]","yourQuery")*100 AS Percentage
FROM yourQuery
WHERE [SumOfCountOfProblem Source]/DSum("[SumOfCountOfProblem Source]","yourQuery")*100 >=1;
.

 

[andrewf10]

Jon K youre a legend, your code works brilliantly.

Thanks so much