By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

Trying to count multiple columns that have the same data

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. x = dcount ("i1_1", "my_query or table", "Class = Forms!my_form!Class AND i1_1='excellent'
  2. x = dcount ("i1_2", "my_query or table", "Class = Forms!my_form!Class AND i1_2='excellent'
  3. x = dcount ("i1_3", "my_query or table", "Class = Forms!my_form!Class AND i1_3='excellent'
  4. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 17 '10 #1
Share this Question
Share on Google+
8 Replies

Expert 5K+
P: 8,638
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.
Mar 17 '10 #2

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. Class_Full_Name (combo box linked to a query)
  2. Ins1 (Text)
  3. Ins1_1, Ins1_2, Ins1_3, Ins1_4
  4. Ins2_1, Ins2_2, Ins2_3, Ins2_4
  5. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim P1 As Integer
  2. P1 = DCount("Ins_1", "qry_feedback", "Class_Full_Name = Forms!frm_FBCount!Class_Name")
  3. Ins1_Part = P1
  5. Dim E1_1 As Integer
  6. Dim E1_2 As Integer
  7. Dim E1_3 As Integer
  8. Dim E1_4 As Integer
  10. E1_1 = DCount("[ins1_1]", "qry_feedback", "[Class_Full_Name] = Forms!frm_FBCount!Class_Name AND ins1_1 ='Excellent'")
  11. E1_2 = DCount("[ins1_2]", "qry_feedback", "[Class_Full_Name] = Forms!frm_FBCount!Class_Name AND ins1_2 ='Excellent'")
  12. E1_3 = DCount("[ins1_3]", "qry_feedback", "[Class_Full_Name] = Forms!frm_FBCount!Class_Name AND ins1_3 ='Excellent'")
  13. E1_4 = DCount("[ins1_4]", "qry_feedback", "[Class_Full_Name] = Forms!frm_FBCount!Class_Name AND ins1_4 ='Excellent'")
  15. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim Count1E As Integer
  3. 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'")
  5. 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?
Mar 17 '10 #3

Expert 5K+
P: 8,638
  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.
    Expand|Select|Wrap|Line Numbers
    1. Class      Ins1            Ins_1        Ins_2    Ins_3    Ins_4
    2. Class A    Instructor 3    Excellent    Fair    Poor    Very Good
    3. Class A    Instructor 1    Good         Fair    Fair    Excellent
    4. Class A    Instructor 2    Very Good    Good    Fair    Poor
    5. Class B    Instructor 1    Excellent    Very Good    Excellent    Poor
    6. Class B    Instructor 2    Good         Excellent    Poor    Fair
    7. Class B    Instructor 3    Good         Excellent    Fair    Poor
    8. Class F    Instructor 3    Excellent    Very Good    Poor    Very Good
    9. Class F    Instructor 2    Good    Very Good    Good    Good
    10. Class F    Instructor 1    Good    Fair    Excellent    Excellent
    11. Class E    Instructor 2    Excellent    Very Good    Good    Poor
    12. Class E    Instructor 3    Excellent    Fair    Poor    Fair
    13. Class E    Instructor 1    Fair    Fair    Excellent    Very Good
    14. Class D    Instructor 1    Very Good    Very Good    Fair    Good
    15. Class D    Instructor 2    Good    Fair    Very Good    Poor
    16. Class D    Instructor 3    Fair    Excellent    Good    Poor
    17. Class C    Instructor 2    Good    Good    Poor    Excellent
    18. Class C    Instructor 1    Excellent    Excellent    Good    Very Good
    19. 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.
    Expand|Select|Wrap|Line Numbers
    1. Class      Ins1          Excellent  Very Good  Good  Fair  Poor
    2. Class A    Instructor 1    1            0        1     2    0
    3. Class A    Instructor 2    0            1        1     1    1
    4. Class A    Instructor 3    1            1        0     1    1
    5. Class B    Instructor 1    2            1        0     0    1
    6. Class B    Instructor 2    1            0        1     1    1
    7. Class B    Instructor 3    1            0        1     1    1
    8. Class C    Instructor 1    2            1        1     0    0
    9. Class C    Instructor 2    1            0        2     0    1
    10. Class C    Instructor 3    2            0        0     1    1
    11. Class D    Instructor 1    0            2        1     1    0
    12. Class D    Instructor 2    0            1        1     1    1
    13. Class D    Instructor 3    1            0        1     1    1
    14. Class E    Instructor 1    1            1        0     2    0
    15. Class E    Instructor 2    1            1        1     0    1
    16. Class E    Instructor 3    1            0        0     2    1
    17. Class F    Instructor 1    2            0        1     1    0
    18. Class F    Instructor 2    0            1        3     0    0
    19. Class F    Instructor 3    1            2        0     0    1
  3. At this point, I simply need to know if the Results are correct.
Mar 17 '10 #4

P: 3
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?
Mar 17 '10 #5

Expert 5K+
P: 8,638
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.
Mar 17 '10 #6

Expert Mod 15k+
P: 31,494
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).
Mar 17 '10 #7

Expert 5K+
P: 8,638
Hello NeoPa, just for curiosity, do you understand the exact nature of the request?
Mar 18 '10 #8

Expert Mod 15k+
P: 31,494
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.
Mar 18 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.