Count records in a table

[mistap]

I have 2 tables; Course & Questions. This is a basic survey dbase where a course has many surveys. A survey has 10 questions with 4 options to answer from 1 to 4.

I'd like to count the number of options, ie, a tally for different courses....below is an example of how this data must be show;

Course 4 3 2 1
Gender 10 5 4 0
Theatre 5 3 4 2
Behaviour Management 15 10 2 0

mistap

[aas4mis]

Please post a more descriptive table layout with your relations between the two tables. With that I should be able to provide a query that may help you out.

[mistap]

Please post a more descriptive table layout with your relations between the two tables. With that I should be able to provide a query that may help you out.

Heres the layout

tb_course has pk_ID, tb_qtns has fk_courseID. Relationship is 1 to many where tb_course has the primary key.

tb_qtns contains questions from 1 to 10 which are number fields, ie, q1, q2, q3 etc.

A course, GENDER e,g has 50 surveys; In a survey, q1 has four options,
1 to 4.

Generally, I'd like a count of the total number of 1s, 2s, 3s, 4s for GENDER

Each course will have a different number of surveys.

Greatly appreciate your help

Mistap

[aas4mis]

Heres the layout

tb_course has pk_ID, tb_qtns has fk_courseID. Relationship is 1 to many where tb_course has the primary key.

tb_qtns contains questions from 1 to 10 which are number fields, ie, q1, q2, q3 etc.

A course, GENDER e,g has 50 surveys; In a survey, q1 has four options,
1 to 4.

Generally, I'd like a count of the total number of 1s, 2s, 3s, 4s for GENDER

Each course will have a different number of surveys.

Greatly appreciate your help

Mistap

Sorry, still a little confused on the layout. If you're wanting the total count of different fields that all correspond to a single parent value (courseID) I believe you're looking for a crosstab. Just open your query editor you can select a crosstab query and use the courseID as your row heading the q's as your column heading (both group by) and possibly sum(q's) as your value [like I said, still a little confused on your table layout.] If you want a tally for each question in a different query that's another option. That would be:

SELECT c.pk_ID, count(q.q1) FROM tb_course as c INNER JOIN tb_qtns as q on c.pk_id = q.courseID WHERE c.pk_ID = "Gender";

Hope this points you in the right direction.

[mistap]

Sorry, still a little confused on the layout. If you're wanting the total count of different fields that all correspond to a single parent value (courseID) I believe you're looking for a crosstab. Just open your query editor you can select a crosstab query and use the courseID as your row heading the q's as your column heading (both group by) and possibly sum(q's) as your value [like I said, still a little confused on your table layout.] If you want a tally for each question in a different query that's another option. That would be:

SELECT c.pk_ID, count(q.q1) FROM tb_course as c INNER JOIN tb_qtns as q on c.pk_id = q.courseID WHERE c.pk_ID = "Gender";

Hope this points you in the right direction.

Here's the layout again;

2 tables: one is tb_Courses & the other is tb_qtns with tb_courses being the primary table.

For example, Question 1 has four group option buttons which are 1, 2, 3, 4. If 4 is selected for q1, then, q1 contains 4. If 1 is selected for q2, then, the q2 field contains 1

Heres the problem; i would like to count the total number of ones, twos, etc for each course.

Ive tried crosstab but it doesn't seem to work