Solved Charts (1 Viewer)

[althaherm]

Dear All,

Kindly, I have tables and each table contains a Yes/No field, I need to create a chart that shows how many Yes and how many No in each table.

something like the attached photo.

Thanks in advance for your help.

 

[arnelgp]

SELECT Count(IIf([YesNoField],1,Null)) AS Yes, Count(IIf([YesNoField],Null,1)) AS [No], "Table1" AS Expr1
FROM table1
GROUP BY "Table1"
UNION
SELECT Count(IIf([YesNoField],1,Null)) AS Yes, Count(IIf([YesNoField],Null,1)) AS [No], "Table2" AS Expr1
FROM table2
GROUP BY "Table2"
UNION
SELECT Count(IIf([YesNoField],1,Null)) AS Yes, Count(IIf([YesNoField],Null,1)) AS [No], "Table3" AS Expr1
FROM table3
GROUP BY "Table3"
UNION
SELECT Count(IIf([YesNoField],1,Null)) AS Yes, Count(IIf([YesNoField],Null,1)) AS [No], "Table4" AS Expr1
FROM table4
GROUP BY "Table4"
UNION SELECT Count(IIf([YesNoField],1,Null)) AS Yes, Count(IIf([YesNoField],Null,1)) AS [No], "Table5" AS Expr1
FROM table5
GROUP BY "Table5";

 

[harpygaggle]

How many data do you have? You may also use pivot and from there create a table.

 

[arnelgp]

use pivot

the Modern chart will create the pivot/crosstab.

 

[althaherm]

SELECT Count(IIf([YesNoField],1,Null)) AS Yes, Count(IIf([YesNoField],Null,1)) AS [No], "Table1" AS Expr1
FROM table1
GROUP BY "Table1"
UNION
SELECT Count(IIf([YesNoField],1,Null)) AS Yes, Count(IIf([YesNoField],Null,1)) AS [No], "Table2" AS Expr1
FROM table2
GROUP BY "Table2"
UNION
SELECT Count(IIf([YesNoField],1,Null)) AS Yes, Count(IIf([YesNoField],Null,1)) AS [No], "Table3" AS Expr1
FROM table3
GROUP BY "Table3"
UNION
SELECT Count(IIf([YesNoField],1,Null)) AS Yes, Count(IIf([YesNoField],Null,1)) AS [No], "Table4" AS Expr1
FROM table4
GROUP BY "Table4"
UNION SELECT Count(IIf([YesNoField],1,Null)) AS Yes, Count(IIf([YesNoField],Null,1)) AS [No], "Table5" AS Expr1
FROM table5
GROUP BY "Table5";

Dear arnelgp

Thank you, can you please guide me where and how to insert the code, is it going in the table or? sorry but I am just a beginner. thanks again

 

[althaherm]

How many data do you have? You may also use pivot and from there create a table.

I have 7 tables and each table has a YES/NO field, Thanks

 

[arnelgp]

see this demo.
i have 5 tables.

query1 is first made
then make query2.

create a new form (formModernChart) and i inserted modern chart (column Clustered).
the rowsource of the chart is query2.

open the form.

 

[althaherm]

see this demo.
i have 5 tables.

query1 is first made
then make query2.

create a new form (formModernChart) and i inserted modern chart (column Clustered).
the rowsource of the chart is query2.

open the form.

Thank you, I am attaching a sample of the tables. the field that I am trying to use it in the chart is (HSE approval). from all the tables.

 

[arnelgp]

see form1

 

[althaherm]

see form1

Thank you so very much. Finally, I got it working.