Products Quality Control DB Questions and Answers Per Item

[c.stewart28]

I have been looking at a couple sample databases provided on this site and I think I'm close, but just can't get some things figured out. I have a Products table, Answers table, and Questions table.

Products table has relationship with Answers table and Questions table has a relationship with the Answers table as well.

I would like to have a subform off my main ProductsDetails form with all the questions and answers related to that type of product, such as camera, server, workstation, etc. The questions are related to how well the product works and should only display the questions identified to that particular product. Each question has an applicability where you can choose if it relates to which product. I know I need a query, but I need to list all the questions and then based on the answer store the answers in the answers table.



Also want to create a checklist for each item as a report in the end.

I attached the database so you can see what I'm talking about.

Thanks for any help, kind of new at all of this.

[June7]

Might want to rename tbleAnswers to tblAnswers.

How will a subform of the relevant questions on ProductsDetails form help get response into tblAnswers?

Presume tblAnswers is where response to each question for each product will be stored.

tblAnswers will need fields for QuestionID, ProductID, Answer - already have those.

Options for data entry form design:

1. single form bound to tblAnswers with comboboxes to select product and question

2. main form bound to tblProducts and subform bound to tblAnswers with combobox to select question

3. main form bound to tblQuestions and subform bound to tblAnswers with combobox to select product

This data structure assumes there is only one set of responses for each product - not responses from multiple users.

[c.stewart28]

How will a subform of the relevant questions on ProductsDetails form help get response into tblAnswers? I was thinking that I would get the list of questions in a subform, then answer them and on the bottom of the subform have an update button to push the data into the answers table and when you select the QA tab fire off an action that would do a look up to see if there are any questions that have been answered and display them.

The first issue I need to figure out is how to list all the questions from the questions table in like in a data sheet view on a sub form, when I don't have a direct link to that table from the tblProducts to the tblQuestons. I'm trying to create a query but can't seem to get it list all the questions.

[c.stewart28]

I got the list of questions, but still having issues on trying to figure out the subform. After thinking do I really need to have it linked? How about this, when moving to the tab QA Questions, run a query against the questions table and pull back all questions that are appropriate for the product, look at the answers table and if nothing is there pull back just the list, but if already had an answer pull those back. Have a button "update" and when pressed would write productid, questionid, and answer to the answers database. So where to start to display the query list?

[June7]

If you bind the subform to the questions table, then will be editing those records by changing the value of the yes/no fields. I don't think you really want to do that because those yes/no fields identify what products question is associated with. I think the process you describe is not practical.

[c.stewart28]

I just ran a query and pulled down the list of questions where fixed equal yes, just to get a list of question. I should be able to have a button to update the answers table with each of the responses to each question ID. Should I not be able to query the answers table and look for the products id and pull back the questionid and answer and if nothing is there just the blank questions and then write the productid, questionid and answer back to the answers table?

[June7]

What????

Exactly where are these 'responses' being entered?

[c.stewart28]

I want to be able to pull up a product and on the QA tab have a list of question pertaining to that product displayed, then select yes/no/or na for each of the questions. Then run a report for each product (kind of a checklist) with some product information and the questions and answers.

[June7]

The 'list' of questions would be a combobox from which user would select a question then provide response in another control and these inputs would be saved into tblAnswers. A combobox list can be conditional on value in another control. This is dependent (cascading) comboboxes. http://www.datapigtechnologies.com/f...combobox2.html

If you want answers to be associated with different users, then need a field in tblAnswers for UserID. And then really need a tblUsers.

[c.stewart28]

Single user. This does not display the list of question.

[June7]

Combobox will list questions in dropdown. Need to build combobox on form bound to tblAnswers.

If you want to be presented with a complete set of questions and then just enter answer, this requires pre-established records in tblAnswers.