Beginner's Problem with a Membership DB

[Morpheus_UK]

Whats the best way of doing this. I have a table called profiles for storing the usual personal info (Name,gender,Contact Info,Etc). One of my fields is called category. The user can enter 4 outcomes from a combo box:

Non-Member
Student Member
Member
Leader

Heres the problem, each category has a different amount and a different set of questions to be asked. E.g. Non-Member

FIELD NAME
1. Previously Applied for Membership = YES/NO
2. If yes, state reason for rejection = TEXT

A Member would have far more questions. What would be the best way to not waste space (file size). Have an individual table for each category's set of questions?

tblNon-Member_Questions
tblStudent Member_Questions
tblMember_Questions
tblLeader_Questions

Also how would you link the profile to the category, so that a profile can only answer questions from the chosen category.

I know its asking a lot of help from a begininer, but when I come to making the form, when doing data entry, is it possible to select a category (e.g. Member) from a combobox on the main form, and the set of related questions would appear in on a subform?

Any advice on how structure should be (ER diagram), or any guidence would be great and well appreciated.

HAPPY NEW YEAR PEOPLE

 

[krunalprajapati]

U can create one table for all the sets of questions with category field.
than u can create a subform for questions with the relation on category field.

 

[Morpheus_UK]

U can create one table for all the sets of questions with category field.
than u can create a subform for questions with the relation on category field.

I done an ER of what I think you implied. Is this correct with what you meant? And what I have in terms of fields will work for linking everything. If its wrong please tell, or if I entirely misinterpreted your suggestion.

Thanks again for the trouble

 

[Pat Hartman]

Your category field actually contains three attributes -
student status
membership status
leader status

Your list of valid categories does not contain all possible combinations of the three attributes. Your list contains 4 possibilities when there are actually 8. I'm sure that some of the 8 are not valid though. Here is a complete list.

Student	Member	Leader	YourCategory
y	y	y	?
y	y	n	Student-Member
y	n	n	?
y	n	y	?
n	n	n	Non-Member
n	n	y	?
n	y	y	Member-Leader
n	y	n	Member

I would recommend this as the category table and other related tables. This first set of tables defines the category and related questions. It allows the same set of questions to be used for more than one category and it allows the same question to be used in more than one set.
tblCategory
CategoryID (autonumber primary key)
Student (y/n)
Member (y/n)
Leader (y/n)
CategoryDesc
QuestionSetID (foreign key to tblQuestionSet)

tblQuestionSet:
QuestionSetID (autonumber primary key)
QuestionSetName

tblQuestions:
QutstionID (autonumber primary key)
QuestionText

tblQuestionSetQuestions:
QuestionSetQuestionsID (autonumber primary key)
QuestionSetID (unique idx fld1, foreign key to tblQuestionSet)
QuestionID (unique idx fld2, foreign key to tblQuestions)

This table allows you to store the answers to questions.
tblAnswers:
MemberID (pk fld1, foreign key to tblMember)
QuestionSetQuestionID (pk fld2, foreign key to tblQuestionSetQuestions)
Answer

A properly normalized structure will allown you to perform any analysis requested.

 

[krunalprajapati]

i think it is right. but read Pat's answer, it will help u more. but understand it properly