CODEWITHSUNDEEP
sqlms-access
bd528
bd528

Reputation: 886

Calculate overall percentage of Access Query

I have an MS Access Query which returns the following sample data:

+-----+------+------+
| Ref | ANS1 | ANS2 |
+-----+------+------+
| 123 | A    | A    |
| 234 | B    | B    |
| 345 | C    | C    |
| 456 | D    | E    |
| 567 | F    | G    |
| 678 | H    | I    |
+-----+------+------+

Is it possible to have Access return the overall percentage where ANS1 = ANS2?

So my new query would return:

50

I know how to get a count of the records returned by the original query, but not how to calculate the percentage.

Upvotes: 1

Views: 124

Answers (2)

Lee Mac
Lee Mac

Reputation: 16025

Since you're looking for a percentage of some condition being met across the entire dataset, the task can be reduced to having a function return either 1 (when the condition is validated), or 0 (when the condition is not validated), and then calculating an average across all records.

This could be achieved in a number of ways, one example might be to use a basic iif statement:

select avg(iif(t.ans1=t.ans2,1,0)) from YourTable t

Or, using the knowledge that a boolean value in MS Access is represented using -1 (True) or 0 (False), the expression can be reduced to:

select -avg(t.ans1=t.ans2) from YourTable t

In each of the above, change YourTable to the name of your table.

Upvotes: 2

C Perkins
C Perkins

Reputation: 3882

If you know how to get a count, then apply that same knowledge twice:

SELECT Count([ANS1]) As MatchCount FROM [Data]
WHERE [ANS1] = [ANS2]

divided by the total count

SELECT Count([ANS1]) As AllCount FROM [Data]

To combine both of these in a basic SQL query, one needs a "dummy" query since Access doesn't allow selection of only raw data:

SELECT TOP 1
   ((SELECT Count([ANS1]) As MatchCount FROM [Data] WHERE [ANS1] = [ANS2])
   /
   (SELECT Count([ANS1]) As AllCount FROM [Data]))
   AS MatchPercent
FROM [Data]

This of course assumes that there is at least one row... so it doesn't divide by zero.

Upvotes: 0

Related Questions