Trying to count multiple columns that have the same data

[hkarlson]

I'm not sure the best way to word what I am trying to do.

I have a database table which collects feedback data on some training courses my company provides. We track feedback on 3 different instructors and attendees answer 4 questions, each response is either excellent, very good, good, fair or poor. I have created a form connected to a table to collect the counts all of the (excellent, very goods, goods, etc) for each instructor. I'm not extremely versed in VBA but I can usually get by. However, currently the code I am using would require me to type out something similar to:

x = dcount ("i1_1", "my_query or table", "Class = Forms!my_form!Class AND i1_1='excellent'
x = dcount ("i1_2", "my_query or table", "Class = Forms!my_form!Class AND i1_2='excellent'
x = dcount ("i1_3", "my_query or table", "Class = Forms!my_form!Class AND i1_3='excellent'
x = dcount ("i1_4", "my_query or table", "Class = Forms!my_form!Class AND i1_4='excellent'

for each instructor and each answer to 4 questions. Not my idea of fun. How can I consolidate this to shorten the length of my VBA code and the time it takes to calculate?

I have been looking around and it seems like I can do the following:

dcount("[i1_1 & i1_2 & i1_3 & i1_4]", "my_query", "[Class] = Forms!my_form!Class ... "

But ... when it gets to the criteria part of it, I am at a loss. AND, OR, & and + don't seem to give me what I want when I'm trying to get it to count each 'excellent' or whatnot in columns 1, 2, 3 and 4.

So, after this incoherent ramble, I'm hoping someone can help me. The answer is probably pretty simple and right in front of my nose.

[ADezii]

Post your Table Name, Field Names along with their Data Types, Sample data, and Sample Results so that we can get a better picture of what is going on.

[hkarlson]

There are likely two tables that you will need this info on: the first table name is tbl_FB (stores all of the collected data and and the second is tbl_FBCount (counts the collected data in tbl_Feedback):

In tbl_FB, we track the following information:

Class_Full_Name (combo box linked to a query)
Ins1 (Text)
Ins1_1, Ins1_2, Ins1_3, Ins1_4
Ins2_1, Ins2_2, Ins2_3, Ins2_4
Ins3_1, Ins3_3, Ins3_3, Ins3_4

(Fields ending in "_#" are set up as combo boxes to select either "Excellent," "Very Good," "Good," Fair," or "Poor")

I've set the table up this way because we are hoping to email the participants and let answers anonomously auto-fill into the DB. Currently, everything is pen and paper and we're trying to move away from having to manually count everything. Which is why I also have the table "tbl_FBCount."

Some fields are as follows. Each field is set up as text box:

Class_Name (combo box linked to a query)
Ins1_Part (used to capture Total # of participants answering questions for Instructor 1)
Ins1_E (used to capture Total of "Excellent" for Instructor 1)
Ins1_VG (used to capture Total of "Very Good" for Instructor 1)
Ins1_G (used to capture Total of "Good" for Instructor 1)
Ins1_F (used to capture Total of "Fair" for Instructor 1)
Ins1_P (used to capture Total of "Poor" for Instructor 1)

So, now, I have a form called frm_FBCount, which I used to calculate all these totals, but the issue is that when I started the counting I used the following code:

Dim P1 As Integer
P1 = DCount("Ins_1", "qry_feedback", "Class_Full_Name = Forms!frm_FBCount!Class_Name")
Ins1_Part = P1
 
Dim E1_1 As Integer
Dim E1_2 As Integer
Dim E1_3 As Integer
Dim E1_4 As Integer
 
E1_1 = DCount("[ins1_1]", "qry_feedback", "[Class_Full_Name] = Forms!frm_FBCount!Class_Name AND ins1_1 ='Excellent'")
E1_2 = DCount("[ins1_2]", "qry_feedback", "[Class_Full_Name] = Forms!frm_FBCount!Class_Name AND ins1_2 ='Excellent'")
E1_3 = DCount("[ins1_3]", "qry_feedback", "[Class_Full_Name] = Forms!frm_FBCount!Class_Name AND ins1_3 ='Excellent'")
E1_4 = DCount("[ins1_4]", "qry_feedback", "[Class_Full_Name] = Forms!frm_FBCount!Class_Name AND ins1_4 ='Excellent'")
 
Ins1_E = E1_1 + E1_2 + E1_3 + E1_4

To do this for each option (Very good, good, fair and poor) and instructor (2 and 3) is time consuming, and the DCount takes forever to calculate. I'm not looking for instantaneous, but I have only done enough code for 1 instructor and I can get up from my desk and do other things before its done.

Is there a shorter / easier way to count all these things? I have tried this:

Dim Count1E As Integer
 
Count1E = DCount("[ins1_1] & [ins1_2] & [ins1_3] & [ins1_4]", "qry_feedback", "[Class_Full_Name] = Forms!frm_FBCount!Class_Name AND [ins1_1] AND [ins1_2] AND [ins1_3] AND [ins1_4]='excellent'")
 
Ins1_E = Count1E

But "AND," "OR, and "&" don't seem to count what I want it to. Would it be simpler to use a SQL command?

[ADezii]

1. I'm actually going to work backwards on this one, since I'm still not crystal clear on what you are requesting. I've created the following Sample Data consisting of 5 possible Ratings (Excellent, Very Good, Good, Fair, Poor) for 4 Questions (Ins_1 ==> Ins_4), involving 6 different Classes (Class A ==> Class F), and 3 different Instructors (Instructor 1 ==> Instructor 3). Sorry, no time to properly Format the Data.
   

   Class      Ins1            Ins_1        Ins_2    Ins_3    Ins_4
   Class A    Instructor 3    Excellent    Fair    Poor    Very Good
   Class A    Instructor 1    Good         Fair    Fair    Excellent
   Class A    Instructor 2    Very Good    Good    Fair    Poor
   Class B    Instructor 1    Excellent    Very Good    Excellent    Poor
   Class B    Instructor 2    Good         Excellent    Poor    Fair
   Class B    Instructor 3    Good         Excellent    Fair    Poor
   Class F    Instructor 3    Excellent    Very Good    Poor    Very Good
   Class F    Instructor 2    Good    Very Good    Good    Good
   Class F    Instructor 1    Good    Fair    Excellent    Excellent
   Class E    Instructor 2    Excellent    Very Good    Good    Poor
   Class E    Instructor 3    Excellent    Fair    Poor    Fair
   Class E    Instructor 1    Fair    Fair    Excellent    Very Good
   Class D    Instructor 1    Very Good    Very Good    Fair    Good
   Class D    Instructor 2    Good    Fair    Very Good    Poor
   Class D    Instructor 3    Fair    Excellent    Good    Poor
   Class C    Instructor 2    Good    Good    Poor    Excellent
   Class C    Instructor 1    Excellent    Excellent    Good    Very Good
   Class C    Instructor 3    Poor    Excellent    Fair    Excellent

2. I created a Query with Calculated Fields calling a Public Function that calculates the Totals for each Rating for a given Class <==> Instructor Combination.
   

   Class      Ins1          Excellent  Very Good  Good  Fair  Poor
   Class A    Instructor 1    1            0        1     2    0
   Class A    Instructor 2    0            1        1     1    1
   Class A    Instructor 3    1            1        0     1    1
   Class B    Instructor 1    2            1        0     0    1
   Class B    Instructor 2    1            0        1     1    1
   Class B    Instructor 3    1            0        1     1    1
   Class C    Instructor 1    2            1        1     0    0
   Class C    Instructor 2    1            0        2     0    1
   Class C    Instructor 3    2            0        0     1    1
   Class D    Instructor 1    0            2        1     1    0
   Class D    Instructor 2    0            1        1     1    1
   Class D    Instructor 3    1            0        1     1    1
   Class E    Instructor 1    1            1        0     2    0
   Class E    Instructor 2    1            1        1     0    1
   Class E    Instructor 3    1            0        0     2    1
   Class F    Instructor 1    2            0        1     1    0
   Class F    Instructor 2    0            1        3     0    0
   Class F    Instructor 3    1            2        0     0    1

3. At this point, I simply need to know if the Results are correct.

[hkarlson]

If I am looking at your example correctly, you have it set up where you can select an instructor in a new recordset. The way my table is set up is that Instructor 1, 2 and 3 are in the same recordset, along with feedback on 6 course questions.

I would love to set it up where you select a class, instructor, answer the 4 questions and go to a new record. I'm almost thinking that is the only way to go at this point. From your example, the last thing I would need to do from a query is count the total of Instructor 1, Excellent, Very Good, Good, Fair and Poor (i.e., 12 Participants, 30 Excellent, 5 Very Good, 8 Good, 5 Fair)

However, the drawback is that participants would have to send up to 3 emails to fill out 1 survey on instructors, and yet another to give us feedback on the course itself, yes?

[ADezii]

I am now returning to the 2nd half of my Request in Post #2, namely: provide some sample Data from tbl_FB, and the results from analyzing the Data contained within. Better yet, can you Upload the DB with with both tbl_FB and tbl_FBCount populated, so that I can get an accurate picture of 'exactly' what you are looking for. Without the actual DB, I'm afraid that I am 'Stuck-in-the-Mud'. I cannot accurately interpret what you are requesting from the opposite end of a Web Page.

[NeoPa]

I have no problem with the uploading of a database, but seeing the figures in the post enables more people to understand the thread in a practicable way. Attached databases are not something that most people have the time or inclination to look at. I certainly avoid them largely on the basis that most information can be imparted quite adequately if properly prepared.

That said, I too am waiting for a proper response to post #2. The info requested would explain the situation very much better, and more easily, than the words of explanation.

From this point, all I can say is that you are looking at Domain Aggregate functions (DCount() is one such), where you should probably be looking at the more simple Aggregate functions (Count(), Sum(), etc), which fit more neatly into a simple query, and can produce results for each and every grouping in your query.

It is very unlikely that you would need a separate table to store aggregated values, as queries do that for you quite adequately without resorting to permanent storage of the values (with all the problems that automatically introduces).

[ADezii]

Hello NeoPa, just for curiosity, do you understand the exact nature of the request?

[NeoPa]

No ADezii. My paragraph #2 is an echoing of your request for data which would surely enable us to work out what the question means. I can pretty well determine what's going wrong, but how to put it right depends on knowing what they actually want, and this is not clear from the question.