Percentage of Total

[O.92]

I have data for employee's as such:

Recruiter........Employee ID............Employee Status
Steven..........643983489..............ACTIVE


Hailey...........643983578..............LEAVE OF ABSENCE
Steven..........643983834..............ACTIVE
Steven..........643983001..............TERMINATED
Steven..........643983283..............ACTIVE
Hailey...........643983542..............ACTIVE
Steven..........643983111..............LEAVE OF ABSENCE
Hailey...........643983782..............ACTIVE

I am trying to build a query that calculates the percentage of total that is Active or Leave of Absence by each recruiter. Is there a way to do this, or a workaround using multiple queries? I appreciate any help!

[ItsMe]

Have you tried creating a totals query? I would start there. You can use the Totals query tool in the Query Designer and then select the Sum() function from the appropriate field within the Grid at the bottom of the Query Designer.

[O.92]

I'm not sure that would work? Can you sum non-numerical values?

[ItsMe]

I'm not sure that would work? Can you sum non-numerical values?

No, you can some their count. And actually, for averages, you will need a Count. I got my functions mixed up. Start with getting a count and then you can use the Count for your Avg() function.

In the end, you will probably end up with something like this.
Avg(Count(MyFieldName))

So start with design view and use the Totals button at the top, within the ribbon. Add the Count function. Switch to SQL view and wrap that function with the Avg() function.

[InsuranceGuy]

I'm not sure that would work? Can you sum non-numerical values?

You can interpret the non-numerical values and then use the numbers. Give this a try:

SELECT RECRUITER, sum(IIF([Employee Status]="ACTIVE",1,0))/count(*) Active_Percentage, sum(IIF([Employee Status]="LEAVE OF ABSENCE",1,0))/count(*) Leave_Percentage
FROM tblName
GROUP BY RECRUITER

Replace tblName as appropriate.