How do I return a count of true booleans from "Table_1" in "Table_2"?

[SPC Camp]

in Microsoft Access '07, I am creating a Database that tracks whether or not a specific department is deficient in specific areas. I am using Booleans in the form of check marks to annotate which departments have which deficiencies. True = Deficient, and False = Satisfactory.
I would like to summarize this data in another table that has all of the companies departments, and all of the information of said departments, in it, while stating the number of deficiencies without telling every one else what everyone elses deficiencies are. To do this, I have tried to insert a "SELECT" function in the "Default Value" feild of the "Field Properties" table in "Design View". the function that I used was

SELECT COUNT ([A3005]) FROM [Table_1];

and I get an error stating "Object invalid or no longer set."

Although I have programming experience (C++, C, C#) this is my first time using Access or SQL, so I have no clue what I am doing wrong or what syntax I should be using i pulled the function that i am using from "http://www.1keydata.co m/sql/sqlselect.html" . The tutorials dont go over Booleans very well, and "help" is, in all honesty, no help.

[Oralloy]

Try something a little different:

SELECT key, [A3005], COUNT(*)
  FROM [Table_1]
  WHERE [A3005]
  GROUP BY key

Where key is your key field for Table_1.

It should show you the counts of all the "True" records.

[SPC Camp]

Access adds to your code when I run it.

SELECT key AS Expr1, Table_1.[A3005], Count(*) AS Expr2
FROM Table_1
WHERE (((Table_1.[A3005])<>False))
GROUP BY key, Table_1.[A3005];

I further modified the code to read:

SELECT Table_1.Dept AS Dept, Table_1.A3005, Table_1.A3009, Count(*) AS Totals
FROM Table_1
WHERE (((Table_1.A3005)<>False)) OR (((Table_1.A3009)<>False))
GROUP BY Table_1.Dept, Table_1.A3005, Table_1.A3009;

to test for operability. It displays the statements that return true, but does not return the total true statements. I know that I can use the "Totals" button under "Home>Recor ds" on the ribbon menu on the table to have it count the true statements, but how would I reference that in another table?

[Oralloy]

@SPC Camp,

If all you care about is hits by department, try doing something like this:

SELECT Table_1.Dept AS Dept, Count(*) AS Total
FROM Table_1 
WHERE (((Table_1.A3005)<>False)) OR (((Table_1.A3009)<>False)) 
GROUP BY Table_1.Dept;

If you need separate totals, it gets to be a little trickier, but not really bad. I think this is it.

SELECT Table_1.Dept AS Dept,
       COUNT((Table_1.A3005)<>False) AS TOTAL_A3005,
       COUNT((Table_1.A3009)<>False) AS TOTAL_A3009
FROM Table_1 
WHERE (((Table_1.A3005)<>False)) OR (((Table_1.A3009)<>False)) 
GROUP BY Table_1.Dept

Failing that, this might be what you want:

SELECT Table_1.Dept AS Dept,
      COUNT(IIF(Table_1.A3005,True,NULL)) AS TOTAL_A3005,
      COUNT(IIF(Table_1.A3009,True,NULL)) AS TOTAL_A3009
FROM Table_1 
WHERE (((Table_1.A3005)<>False)) OR (((Table_1.A3009)<>False)) 
GROUP BY Table_1.Dept

[SPC Camp]

I decided on something a little less eloquent

SELECT sum(([a3005]+[a3009]+[a1005]+[a3001]+[a3002]+[a3006]+[a1142]+[a3053]+[a1152]+[a5001])*-1) AS Totals, [Unit]
FROM table_1
GROUP BY [Unit];

This code gives me a count of deficiencies per dept. i have hundreds of deficiencies to track per dept, and will have well over a few hundred depts by the time i am done. Thank you for the help!

[Oralloy]

Sounds like a plan.

Just beware that addition behaves badly in the presence of NULL values.

Luck!