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

Count records in a table

P: 4
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
Sep 10 '08 #1
Share this Question
Share on Google+
4 Replies


aas4mis
P: 97
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.
Sep 20 '08 #2

P: 4
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
Sep 23 '08 #3

aas4mis
P: 97
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Sep 23 '08 #4

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Sep 29 '08 #5

Post your reply

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