Count morethan one column in a table (1 Viewer)

[prabhus]

Dear All,

I have attached a file with Table, Current Result & Desired Result.

I have a table with Month, A, B to G with True or False check box inputs, what i need is on every month, in each category what is the total False;
In the past, i created a count query for each category like A to G and then finally link it. So i want to simplify it and i used below SQL but its giving me the result but not in a good format ( please check current result in my excel file ) how to improve this or is there a better way of doing this, please suggest.

SQL used

SELECT Tbl_DateMonth.[Month], Count(*) AS A, '' as DA_FIEPending, '' as B,'' as DA_PFMEAPending
FROM Tbl_DateMonth LEFT JOIN ([MasterTable_ EngineeringChanges] LEFT JOIN [MHEX Processors Update] ON
FROM Tbl_DateMonth LEFT JOIN ([Table1] LEFT JOIN [Table2] ON [Table1].UniqueID = [Table2].[Unique Id]) ON Tbl_DateMonth.[Effective Month] = [Table1].[Effective Month]
WHERE ((([Table1].DA) Like "-1") AND (([Table2].A) Like "0"))
GROUP BY Tbl_DateMonth.[Month];
UNION ALL
SELECT Tbl_DateMonth.[Month],'' as A, Count(*) AS B,
FROM Tbl_DateMonth LEFT JOIN ([Table1] LEFT JOIN [Table2] ON [Table1].UniqueID = [Table2].[Unique Id]) ON Tbl_DateMonth.[Effective Month] = [Table1].[Effective Month]
WHERE ((([Table1].DA) Like "-1") AND (([Table2].B) Like "0"))
GROUP BY Tbl_DateMonth.[Month];

 

[namliam]

Your query wont work as is, no sense on working on a product that doesnt work.

If you want to get what you "desire", either you have to join your count queries or work with an "IIF" inside your counts

 

[BlueIshDan]

I agree with namliam.

This query is not structured right and you might as well start over.

Give me your table names and definitions and what it is you would like to see from them and we'll go from there I guess.

Here is your query formatted so that you can see where the mistakes are more easily.

SELECT 
	Tbl_DateMonth.[Month],
	Count(*) AS A, 
	'' as DA_FIEPending, 
	'' as B,
	'' as DA_PFMEAPending

FROM 
	Tbl_DateMonth 
	LEFT JOIN ([MasterTable_ EngineeringChanges] 
		LEFT JOIN [MHEX Processors Update] ON 
FROM 
	Tbl_DateMonth 
	LEFT JOIN ([Table1] 
		LEFT JOIN [Table2] 
		ON [Table1].UniqueID = [Table2].[Unique Id]) 
	ON Tbl_DateMonth.[Effective Month] = [Table1].[Effective Month]

WHERE 
	((([Table1].DA) Like "-1") 
	AND (([Table2].A) Like "0"))

GROUP BY 
	Tbl_DateMonth.[Month];

UNION ALL

SELECT 
	Tbl_DateMonth.[Month],
	'' as A,
	 Count(*) AS B,
 
FROM 
	Tbl_DateMonth 
	LEFT JOIN ([Table1] 
		LEFT JOIN [Table2] 
		ON [Table1].UniqueID = [Table2].[Unique Id]) 
	ON Tbl_DateMonth.[Effective Month] = [Table1].[Effective Month]

WHERE 
	((([Table1].DA) Like "-1") 
	AND (([Table2].B) Like "0"))

GROUP BY Tbl_DateMonth.[Month];

 

[prabhus]

Thank you,

Here i am attaching the result of my Query and the result i am expecting to display using sql or vba;

 

[namliam]

cant you do "simply"

Select Sum(Iif(A;1;0) ) countofA
, Sum(Iif(b;1;0) ) countofB
, ...
from table2

??