Form conundrum based

[Kev G]

Hi,

I'm working on what should be a really simple supplier audit database
and I thought I had set up the relationships OK until I started
trying
to create some forms for data entry and now I'm really stumped.

Each supplier answers a bunch of audit questions belonging to a
survey
(i.e. a group of questions) and I want to be able to arrange the
forms
so that for each supplier I can enter their 18 categories of audit
question responses.

I'm not a 100% that the relationships are appropriate but I can't
think of how esle they should be done, they're defined below.

What I really want to try and do is have one main form that lets a
user select a Company (there's a simple supplier data entry form
elsewhere) and then iterate through the questions for each catgeory.

After many attempts, I can't base a master form on tblSupplier because
it's not related to tblQuestions. I also can't use tblResponse as a
Master becuase it's really a transaction type table and there's
nothing until responses are generated.

When I try and base something tblQuestions or tblSurveys, I'm not able
bring in the list of Suppliers.

Is there something I'm missing?
Would greatlly appreciate any pointers!


tblSurvey
-------
SrvID (PK)
SrvName


tblSupplier
----------
SupplierID (PK)
SplName
bunch of other fields that describe a supplier


tblQuestions
-------------
QuestionID (PK)
SrvID (FK)
QuestionText
QuestionNumber (this for the order of each question on each survey)
QuestionType (qualifies what data type the response can be , number,
boolean freetext)


tblResponses
--------------------
SupplierID (PK)
QuestionID (PK)
ResponseNumber
ResponseBool
ResponseText


Data integrity is on and relationships look like:
1-many between tblSurvey and tblQuestions
1-many between tblQuestions and tblResponses
1-many between tblSuppliers and tblResponses

Some base assumptions:
-Suppliers have to be defined so that surverys can be answered.
-Suppliers will answer each question once i.e. they won't have another
set of responses next year.
-A question is unique to each survey

I would great appreciate any help and advice thrown my way.

I posted this to experts-exchange.com as well so far the main
suggestion is to add srvID to the tblResponse, I'll see if that helps.

Thanks,
Kevin

 

[tina]

i'd suggest changing the responses table, as

tblResponses
ResponseID (primary key)
SupplierID
SurveyID
<other fields that describe a specific survey returned by a specific
supplier - perhaps a survey date?>

tblResponseDetails
DetailID (primary key)
ResponseID (foreign key from tblResponses)
QuestionID (foreign key from tblQuestions)
Response (or "answer")

the above setup allows you to 1) enter multiple instances of the same survey
to the same supplier - maybe you send the supplier the same survey once
every year, or two years, etc? and 2) set up a mainform bound to
tblResponses, with a subform bound to tblResponseDetails - and "pre-fill"
the subform with a record for each appropriate survey question, so you can
simply enter through the records, filling in the answers as you go.

hth

 

[Kev G]

Thanks Tina.

I ended writing an append query to make a cartesian product of
tblSupplier and tblQuestion (i.e. no join specified to give every
combo of question and supplier) and using it populate the
tblResponses.

Awkward workaround for something I thought would be simpler.

Thanks again!
Kevin

 

[tina]

yes, it is awkward, and not necessary.

Thanks Tina.

I ended writing an append query to make a cartesian product of
tblSupplier and tblQuestion (i.e. no join specified to give every
combo of question and supplier) and using it populate the
tblResponses.

Awkward workaround for something I thought would be simpler.

Thanks again!
Kevin