Find related records in same table

[GS500]

I have the following table (tblQuestions) structure and example data for a survey database:

QstnID(PK), QstnNum, SubQstnID, RespType, QstnText
1,1,A,1,Text of Question
2,1,B,Blank,Text of Sub-question

The RespType field defines whether the question can be answered Y/N (RespType = 1) or Y/N/NA (RespType = 2). A blank RespType means that the combobox for answers is populated from tblResponses that is related to tblQuestions on the QstnID field. The blank RespType is always on the sub-question; a person will answer the main question with Y, N, or NA and then in the sub question select additional details as defined in the tblResponses table for that particular question. Not all questions have a sub-question, however.

What I am trying to query is the QstnID of those sub-questions where the main question RespType is 2, so I can add NA options to the tblResponses for the sub-questions whose main question has an NA option.

Is that possible with this table structure? how would I do that? So far none of my queries have returned any results, or not the results I'm looking for.

 

[jzwp22]

Each of your subquestions should be records in tblQuestions and you should define the response type for the subquestions just as you did for the main questions. (Normalization rules dictate that the same types of items be in 1 table). Now to relate a subquestion to its main question, you would need another table:

tblRelatedQuestions
-pkRelQuesID primary key, autonumber
-fkMQuesID foreign key to tblQuestion (represents the Main question)
-fkSQuesID foreign key to tblQuestion (represents the Subquestion)

If you have a main question with multiple subquestions, you would have multiple records in this table each having the same fkMQuesID but differing fkSQuesID's.

 

[GS500]

Thanks for the reply!

The subquestions are already records in tblQuestions. They have the same question number as the main question, but a different letter for the subquestion ID.

I have created a tblRelatedQuestions. How would I set up relationships with tblQuestions? What would the join fields be?

The only unique field in tblQuestions is QstnID.

 

[jzwp22]

Add the tblQuestions a second time in the relationship window (Access automatically adds a digit to the table name but it is still the same table). As to the joins, you would join tblQuestions.QstnID to tblRelatedQuestions.fkMQuesID. You would join the tblQuestions_1.QstnID to tblRelatedQuestions.fkSQuesID.

The QstnID must be unique for each question in tblQuestions. You will no longer need the letter code you mentioned. You use the tblRelatedQuestions to bring the related subquestions to its corresponding main question.

 

[GS500]

Got it, thanks.

One more question, is there an easy way to copy the QstnID from tblQuestions to the appropriate fields in tblRelatedQuestions, since the main question is always "A" and the subquestions are always "B" or "C"?

 

[jzwp22]

You will need an append query. What is in the field SubQstnID if the question does not have any related subquestions? I assume that the SubQstnID field will have an A in it whether or not the question has subquestions or not.

Start by creating 2 simple SELECT queries. The first one gets all main questions SubQsntID="A"

query name: qryAQuestions
SELECT tblQuestions.QstnID, tblQuestions.QstnNum, tblQuestions.SubQstnID
FROM tblQuestions
WHERE (((tblQuestions.SubQstnID)="A"));

Create a second simple SELECT query to get the records where SubQstnID = "B"

query name: qryBQuestions
SELECT tblQuestions.QstnID, tblQuestions.QstnNum, tblQuestions.SubQstnID
FROM tblQuestions
WHERE (((tblQuestions.SubQstnID)="B"));

Now create a third query with qryAQuestions and qryBQuestions. Join the two queries via the QstnNum field in both. The joining should eliminate any main questions that do not have a B subquestion. I've added aliases for the two ID fields. The query should look like this:

SELECT qryAQuestions.QstnID AS MainQuestion, qryBQuestions.QstnID AS SubQuestion
FROM qryAQuestions INNER JOIN qryBQuestions ON qryAQuestions.QstnNum = qryBQuestions.QstnNum;

Now change the query type to an APPEND query & select tblRelatedQuestions. Then map the MainQuestion field to fkMQuesID and the SubQuestion field to fkSQuesID. The append query should look like the following. I would save the append query & then run it.

INSERT INTO tblRelatedQuestions ( fkMQuesID, fkSQuesID )
SELECT qryAQuestions.QstnID AS MainQuestion, qryBQuestions.QstnID AS SubQuestion
FROM qryAQuestions INNER JOIN qryBQuestions ON qryAQuestions.QstnNum = qryBQuestions.QstnNum;

Now if you have other subquestions ("C", "D" etc.) You can either create two new queries for each (i.e. qryCQuestions & a corresponding append query) or you can just change the WHERE clause in qryBQuestions to the next letter (and save the amended query) and then just rerun the append query you saved initially.