Hi,
I am hoping that someone can help me out. I wish to generate a complex query using VBA that I have working in the design window. While my eventual question is about method and syntax I shall attempt to explain how the data looks.
The target table contains answers to a number of questions stored in another table. The answers are in sets and relate each set relates to a type of vehicle. An example question might be "Is an HF upper sideband radio fitted? Tx? Rx? with the Tx and Rx fields being Boolean (Tx is shorthand for Transmit, Rx for Receive).
I wish to compare the answers listed for two vehicles, A and B, and have an output that looks like this:
QuestionNo, Question Text , A_Tx, B_Rx, B_Tx, A_Rx
102 HF upper sideband? -1 -1 0 -1
It is simple to write a query that lists the answers on two rows one per vehicle but more of a challenge to get the output in a single row.
I created two queries each listing the answers for a single vehicle. I then used these in a third query to get the desired output - so far so good.
The query that creates the final output is listed below (copied from my VBA code). rstA and rstB are the two queries that list the answers per platform.
sqlComp = "SELECT rstA.Fields('QuestionID'), ImplementationQuestions.Message, ImplementationQuestions.ImpQuestion, rstA.Fields('MessageID'), " & _
"rstA.Fields('OriginalTx') , rstB.Fields('OriginalRx'), rstB.Fields('OriginalTx'), rstA.Fields('OriginalRx') " & _
"FROM (QryPlatformA_Answers INNER JOIN QryPlatformB_Answers ON rstA.Fields('QuestionID') = rstB.Fields('QuestionID')) " & _
"INNER JOIN ImplementationQuestions ON rstA.Fields('QuestionID') = ImplementationQuestions.QuestionID " & _
"WHERE (((rstA.Fields('OriginalTx') = True)) Or (((rstB.Fields('OriginalTx')) = True)) " & _
"ORDER BY ImplementationQuestions.DisplayOrder;"
So my questions . Is using a recordset within a second query allowed. If not what approach should be adopted?
Also while the query works in the query design window it is rather complex. Is there a simpler method of achieving the same result?
My work around is to use the simple multi line query to return a list of answers and then parse this record set creating an output. But I am interested if there are any other ideas
best wishes
Simon
I am hoping that someone can help me out. I wish to generate a complex query using VBA that I have working in the design window. While my eventual question is about method and syntax I shall attempt to explain how the data looks.
The target table contains answers to a number of questions stored in another table. The answers are in sets and relate each set relates to a type of vehicle. An example question might be "Is an HF upper sideband radio fitted? Tx? Rx? with the Tx and Rx fields being Boolean (Tx is shorthand for Transmit, Rx for Receive).
I wish to compare the answers listed for two vehicles, A and B, and have an output that looks like this:
QuestionNo, Question Text , A_Tx, B_Rx, B_Tx, A_Rx
102 HF upper sideband? -1 -1 0 -1
It is simple to write a query that lists the answers on two rows one per vehicle but more of a challenge to get the output in a single row.
I created two queries each listing the answers for a single vehicle. I then used these in a third query to get the desired output - so far so good.
The query that creates the final output is listed below (copied from my VBA code). rstA and rstB are the two queries that list the answers per platform.
sqlComp = "SELECT rstA.Fields('QuestionID'), ImplementationQuestions.Message, ImplementationQuestions.ImpQuestion, rstA.Fields('MessageID'), " & _
"rstA.Fields('OriginalTx') , rstB.Fields('OriginalRx'), rstB.Fields('OriginalTx'), rstA.Fields('OriginalRx') " & _
"FROM (QryPlatformA_Answers INNER JOIN QryPlatformB_Answers ON rstA.Fields('QuestionID') = rstB.Fields('QuestionID')) " & _
"INNER JOIN ImplementationQuestions ON rstA.Fields('QuestionID') = ImplementationQuestions.QuestionID " & _
"WHERE (((rstA.Fields('OriginalTx') = True)) Or (((rstB.Fields('OriginalTx')) = True)) " & _
"ORDER BY ImplementationQuestions.DisplayOrder;"
So my questions . Is using a recordset within a second query allowed. If not what approach should be adopted?
Also while the query works in the query design window it is rather complex. Is there a simpler method of achieving the same result?
My work around is to use the simple multi line query to return a list of answers and then parse this record set creating an output. But I am interested if there are any other ideas
best wishes
Simon
Creating a complex query using VBA
0 commentaires:
Enregistrer un commentaire