What is the best way to build a db to report a total number of true yes/nos'?

[SPC Camp]

I am trying to build a db to report the deficiencies/strengths of security. We have about 1000 criterion that we base these on. I am trying to set it up to where we can specify which criteria they are deficient in, and it will report the total number of deficiencies that section has. i have tried to do this with a yes/no field, and code that looks a little like this:

Sum(([a1000]+[a1001]+[a1003]+[a1002]+[a1004]+[a1005])*-1)

in order to calculate the total number, but you can see how after even 20 feilds in one table this would get unweildy, let alone across several different tables, because of the 255 field limit that 'MS Access' has.

Is there anything that I am missing? How can I optimize my calculation and db to make it more functional and less error prone. I am used to debugging in c/c++, but SQL is kind of killing me with all of its rules.

[ADezii]

Hopefully I interpreted your request correctly, but here goes. I created a Generic Function for you which will enable you to list the Yes/No Fields by Name in a Table Name passed to the Function, calculates the Number of Yes(s) in each Yes/No Field, then calculates the Percentage of Yes(s) based on the Total Number of Records in the Table. I'll post the Function Definition below, an example of a Sample Call to the Function, and subsequent OUTPUT based on Sample Data (20 Records in tblTest with 15 Fields, 10 of which were Yes/No Type).

1. Function Code:
   

   Public Function fCountYesNos(strTableName As String)
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim intNumOfFields As Integer
   Dim intFldCtr As Integer
   Dim lngNumOfRecs As Long
   Dim intNumOfYess As Integer
   Const conYESNOField As Byte = 1
    
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset(strTableName, dbOpenSnapshot)
    
   rst.MoveLast: rst.MoveFirst
    
   intNumOfFields = rst.Fields.Count
   lngNumOfRecs = rst.RecordCount
    
   Debug.Print "Field Name", "Yes(s)", "Percent"
   Debug.Print "--------------------------------------------"
    
   With rst
     For intFldCtr = 0 To intNumOfFields - 1               'Fields Collection indexed at 0
       If .Fields(intFldCtr).Type = conYESNOField Then     'Sorry, Yes/No Field Types only
         intNumOfYess = DCount("*", strTableName, .Fields(intFldCtr).Name & " = True")
         Debug.Print .Fields(intFldCtr).Name, intNumOfYess, Format(intNumOfYess / lngNumOfRecs, "Percent")
       End If
     Next
   End With
    
   rst.Close
   Set rst = Nothing
   End Function

2. Sample Call to Function, passing a Table Name:
   

   Call fCountYesNos("tblTest")

3. OUTPUT based on Sample Data:
   

   Field Name    Yes(s)        Percent
   --------------------------------------------
   Field1         0            0.00%
   Field2         5            25.00%
   Field3         3            15.00%
   Field4         2            10.00%
   Field5         5            25.00%
   Field6         2            10.00%
   Field7         4            20.00%
   Field8         3            15.00%
   Field9         3            15.00%
   Field10        4            20.00%

[NeoPa]

I would suggest you look at the design of your database to see how you could get the RDBMS (Access in this case) to work for you. A table structure where the table had records for each question within each situation would seem to fit your situation very much better than what you appear to be using now.

From that position your calculations would be a cinch.

[SPC Camp]

I think that gets me about 3/4 of the way there. In the report, I am querying the tables with the deficiencies, and putting that information along side the section information. I am doing it this way in order to print it out and give it to my boss so she can read it and know what section needs the most work. Also, when there was only 10 criterion, i had the tables linked so i could drop down the deficiencies table inside the section info table and modify it there. i added the other tables, and things just sort of blew up. i guess what i am asking is if this will work across multiple tables?

also, as i am not particularly familiar with SQL, do you think that you could define some of the syntax?

[SPC Camp]

NeoPa,

i am not familiar with databases or SQL at all, so i am looking at Access like it is Excel, and while i know that is wrong, i am also thinking like i can modify it like its c/c++. i am now understanding that i am wrong. with that being said, i dont really understand what you are saying about the questions.could you slow down and teach me a little bit? im sorry for my inexperience, and i am really greatful that you are taking your time out to help me!

[NeoPa]

I will try. A very valuable article on database design generally can be found at Normalisation and Table structures, but I'll post what I can to explain my thoughts anyway.

Assuming you currently have a structure like :
Table = [tblProject]

Field         Type      Index
ProjectID  Autonumber   PK
Q0001      Boolean
Q0002      Boolean
Q0003      Boolean
...

You might have records like the following :

ProjectID  Q0001  Q0002  Q0003  ...
1034         Y      N      Y
1062         N      N      Y

Data stored this way is hard to manipulate in the way you require. What I suggest is :
Table = [tblProjectQ]

Field         Type      Index
ProjectID  Number   FK & 1st part of composite PK
QNumber    Number   FK & 2nd part of composite PK
Q          Boolean

With records like the following :

ProjectID  QNumber  Q
1034         1      Y
1034         2      N
1034         3      Y
1034        ...
1062         1      N
1062         2      N
1062         3      Y
1062        ...

Counting the Y or N values across a particular project would then be a simple aggregate query.

There would also be a [tblProject] and [tblQuestion] tables, but these would contain data pertaining to those items specifically, and not include the values for any questions within any project. EG. The [tblProject] would contain the name of the project and [tblQuestion] probably the security question itself.