Count number of occurence of certain values across multiple columns in a query

[Anita.Murray@jacobs.com]

Good Day, I have a question around how to create a query which counts certain values across multiple columns in a query for each month. I have a form with options: Exceed (3), Meet (2), Develop (1), Not Applicable (0). Currently the output is as follows:

Months	SHE_Perf	Cost_Comp	Sched_Comp	Qual_of_Work	Man_Change	GT_Std_Proc	Qual_Comm	RiskMan	Man_Con	Contract_Comp
March 2018	3	2	1	0	1	2	3	2	1	3
April 2018	3	2	1	2	1	2	3	2	1	3

I want to be able to count the number of times 1 is present, as well as 2 and also 3 (per month) .and use the output to create a monthly doughnut chart which indicates the status of Exceed, Meet and Develop. Can I please ask for assistance in how this is accomplished? I have researched and tried several VBA methods but cannot seem to get the correct results. Many thanks Anita

[June7]

Is that a sample of the table? Normalized data structure would make this a lot easier.

Rearrange the data with a UNION query and use that query in a CROSSTAB query.

Query1:

SELECT Months, "SHE_Perf" AS Category, Choose(SHE_Perf +1,"Not Applicable", "Develop", "Meet", "Exceed") AS Status FROM Table1
UNION SELECT Months, "Cost_Comp", Choose(Cost_Comp +1,"Not Applicable", "Develop", "Meet", "Exceed") FROM Table1
UNION SELECT Months, "Sched_Comp", Choose(Sched_Comp +1,"Not Applicable", "Develop", "Meet", "Exceed") FROM Table1
UNION SELECT Months, "Qual_of_Work", Choose(Qual_of_Work +1,"Not Applicable", "Develop", "Meet", "Exceed") FROM Table1
UNION SELECT Months, "Man_Change", Choose(Man_Change +1,"Not Applicable", "Develop", "Meet", "Exceed") FROM Table1
UNION SELECT Months, "GT_Std_Proc", Choose( GT_Std_Proc +1,"Not Applicable", "Develop", "Meet", "Exceed") FROM Table1
UNION SELECT Months, "Qual_Comm", Choose(Qual_Comm +1,"Not Applicable", "Develop", "Meet", "Exceed") FROM Table1
UNION SELECT Months, "RiskMan", Choose(RiskMan +1,"Not Applicable", "Develop", "Meet", "Exceed") FROM Table1
UNION SELECT Months, "Man_Con", Choose(Man_Con +1,"Not Applicable", "Develop", "Meet", "Exceed") FROM Table1
UNION SELECT Months, "Contract_Comp", Choose(Contract_Comp +1,"Not Applicable", "Develop", "Meet", "Exceed") FROM Table1;

Query2:

TRANSFORM Count(Query1.Category) AS CountOfCategory
SELECT Query1.Months
FROM Query1
GROUP BY Query1.Months
PIVOT Query1.Status;

The output:

Months	Develop	Exceed	Meet	Not Applicable
April 2018	3	3	4
March 2018	3	3	3	1

[Anita.Murray@jacobs.com]

June7, thank you so very much! That is exactly what I was looking for, after so many frustrating days of searching the web. This is a fantastic forum and you are always so very helpful. Much appreciated! Regards Anita