How to show questions from one category in form

[Guest]

Database

I am designing a database which basically consists of a set of questions
that are fixed
And answer that will change for each client.

I need to be able to store the Companies Details, The Company owners details
and also their spouses details. That is the easy part since it will be a
basic contact database.

From there I need each Company to complete the various questions.
The questions will be divided into categories and sub-categories.

My database design looks like this:
(see images at bottom) THE IMAGES WONT COPY IN HERE.
I have the following tables
tb-questions
tb-answers
tb-categories
tb-businessdetails
tb-ownerdetails



My problem comes in when I am doing my forms.
What I want to have happen is this:
I want the client to select the form look-up his company name and then
select the category of questions he wants to answer.

He will for example select Marketing and all the questions that are in the
questions table with the category field marketing will then show in the form,
with space to answer (each answer will have a unique auto number)
I just don’t seem to be able to get it to work.
Currently if I do a form everything works independently from each other I
can select Marketing and the first question from the questions table is all
that show (and the question is not marketing related)

Please HELP?!
Natasja

 

[Amy Blankenship]

Database

I am designing a database which basically consists of a set of questions
that are fixed
And answer that will change for each client.

I'm assuming you don't literally mean that the answers that are shown as
selections will change for each business, but rather that the selections
made will be different for each business. If the actual things that can be
selected will change, then post back and I'll give you a different answer.
If the answers are more of an essay type thing, also post back, because the
solution below won't work.

I need to be able to store the Companies Details, The Company owners
details
and also their spouses details. That is the easy part since it will be a
basic contact database.

From there I need each Company to complete the various questions.
The questions will be divided into categories and sub-categories.

My database design looks like this:
(see images at bottom) THE IMAGES WONT COPY IN HERE.
I have the following tables
tb-questions
tb-answers
tb-categories
tb-businessdetails
tb-ownerdetails



My problem comes in when I am doing my forms.
What I want to have happen is this:
I want the client to select the form look-up his company name and then
select the category of questions he wants to answer.

He will for example select Marketing and all the questions that are in the
questions table with the category field marketing will then show in the
form,
with space to answer (each answer will have a unique auto number)
I just don't seem to be able to get it to work.
Currently if I do a form everything works independently from each other I
can select Marketing and the first question from the questions table is
all
that show (and the question is not marketing related)

Try this:

tblBusinessDetails
=============
BusinessID (PK)
BusinessName
Businessetc

tblCategories
=========
CategoryID (PK)
CategoryDesc

tblQuestions
========
QuestionID (PK)
CategoryID
QuestionStem

tblChoices (the multiple choice answers that appear on screen)
=======
ChoiceID (PK)
QuestionID
ChoiceText

tblSelections (what was selected by each company)
=========
SelectionID (PK)
CompanyID
SelectionID

Post back to the Forms or FormsCoding forum once you get to that stage,
because unfortunately one thing Access does not do well is to provide you
with a partially filled set of data to finish filling.

HTH;

Amy

 

[Guest]

Hi Amy
It is more like an essay type answer, that will be different for ever company.
Natasja

 

[Amy Blankenship]

OK, what you need is:

tblBusinessDetails
=============
BusinessID (PK)
BusinessName
Businessetc

tblCategories
=========
CategoryID (PK)
CategoryDesc

tblQuestions
========
QuestionID (PK)
CategoryID
QuestionStem

tblResponses
=========
ResponseID (PK)
BusinessID
QuestionID
ResponseText

Post back for help on the form itself. It's kind of complicated, because
Access doesn't do this sort of thing well, and I haven't got time right now
to post full details. If you need help quickly, probably a new thread would
attract other respondents.

HTH;

Amy