Pre-populated Records ?

[catat01]

I am trying to create a database in a very short time-frame and have browsed countless number of pages and am stuck! I have 8 different surveys that are made up of a combination of 37 statements (person responds with agreement - agree, neutral, disagree for each statement). The statements in each of the survey is determined by the service. There are 12-16 statements in each survey and some have the same combination of statements and others do not. I have followed direction from a number of posts and came up with the foillowing structure.

tblStatements
-pkStatementID
-Statement

tblAgreement
-pkAgreementID
-Agreement (Agree, Neutral, Disagree, etc)

tblService
-pkServiceID
-Service (Mental Health, Family Services, etc)

tblParticipantDetails
-pkParticipantID
-DateCompleted
-Status
-Location

tblResponses
-pkResponseID
-fkParticipantID
-fkStatementID
-fkAgreementID

What I am having trouble with is trying to produce a form that will automatically list the applicable statements once the service is selected and the user only has to enter the agreement field. I don't mind creating 8 different forms, but I do not want the user to have to select the statement from a drop-down list as this could result in many errors. Any help would be greatly appreciated! Thanks in advance.

[pbaldy]

What determines which statements are applicable? I'd expect a field in that table that you could filter on, enabling you to run an append query for this.

[catat01]

Thanks for your response. I added the Service to the Participants Details table. The Service determines which statements are applicable. I wasn't sure if the Service should be in the Participants Details or the Responses table. I am fairly new to Access.

[rpeare]

DO you have a table that lists how the services and statements relate to one another? for instance if you have 6 statements that apply to 3 different services, and each service has 3 statements something like

Service 1 - Statements 1, 2 and 3 are applicable
Service 2 - Statements 2, 3 and 4 are applicable
Service 3 - Statements 4, 5 and 6 are applicable

I would think you'd need a table to know which statements the client will be asked

[pbaldy]

If the service determines which statements, I would again expect that field in the statements table. You need some way to determine programmatically which statements to pre-populate.

[catat01]

I added the Survey Questions table and added the Service to the Responses table.

tblStatements
-pkStatementID
-Statement

tblAgreement
-pkAgreementID
-Agreement (Agree, Neutral, Disagree, etc)

tblService
-pkServiceID
-Service (Mental Health, Family Services, etc)


tblSurveyQuestions
-pkSurveyQID
-fkServiceID
-fkStatementID

tblParticipantDetails
-pkParticipantID
-DateCompleted
-Status
-Location

tblResponses
-pkResponseID
-fkParticipantID
-fkServiceID
-fkStatementID
-fkAgreementID

Will this work better?? I really appreciate your responses!

[rpeare]

if these are anonymous surveys and you are not collecting and not concerned with any client related information, then I don't see a reason this would not work though you would likely want the fkServiceID on the tblParticipantDetails table because the serviceID determines which statements will be answered and there's no reason to carry it on each individual response.

[catat01]

OKay, I agree. I've made that change. I've spent all morning reading and playing around trying to solve my initial question. If you agree the structure below is correct, how do I go about the original question? Thanks again.

tblStatements
-pkStatementID
-Statement

tblAgreement
-pkAgreementID
-Agreement (Agree, Neutral, Disagree, etc)

tblService
-pkServiceID
-Service (Mental Health, Family Services, etc)


tblSurveyQuestions
-pkSurveyQID
-fkServiceID
-fkStatementID

tblParticipantDetails
-pkParticipantID
-DateCompleted
-Status
-Location
-fkServiceID

tblResponses
-pkResponseID
-fkParticipantID
-fkStatementID
-fkAgreementID

[rpeare]

catat01.zip

Attached is an example.

This is a bit overkill for an example database but you get the idea.

The example just populates the survey every time the service type is changed. It doesn't do any error checking to see if any responses have already been entered or anything. If you want to save the data you would have to build a SQL statement on the fly but the form will handle a survey of up to 10 questions, if you need to do more questions than that you can just add more combo boxes and name them appropriately.

[catat01]

Wow, thanks for the example. I was able to figure out what you have done and replicate it in my database, yet I know nothing of writing SQL and do need to save this information. Clearly, this is beyond my knowledge and I may just have to scrap the pre-populated fields. Thanks for your help.

[rpeare]

cycling through the controls and adding records to your table isn't hard you have an example of how to do it in my sample database.

Constructing a SQL statement and executing it isn't hard either

start with building a SQL statement that adds a record to your target table then when you get it working look at the SQL statement and generate the same thing based on the variables on your form, you have the serviceID already you'd just need to append a record for each control that has the word 'question' in it (or however I labeled them) the only tricky part is retrieving the survey ID (pkParticipantID) from your most recently added record. If you only have 1 user of this database that becomes trivial as well.