Got a .csv file that is being imported into a table using specs. The table consists of the following:
Issue (text)
Returned_Date (Date/Time)
RD (text)
RM (text)
Region (Text)
Type (text)
ClientName (text)
4398
4399
4400
etc.
The etc. being about 80 addtional numeric column names that represent questions being answered on a survey.
My task in this is to retrieve all the "Yes" answers to these questions by RD and list them in a report.
Currently, I'm looping through the table and pulling these "Yes" records based on a RD chosen from a drop-down box and creating another table with the field names Header, Header1, Header2, up to Header10, which is dependent on
how many clients answered the survey so it looks like this:
Then I'm looping through each Header, one at a time and putting into an unbound control on a report.
Expand|Select|Wrap|Line Numbers
- 'First Client
- counter = 1
- sql = "GROUP BY tbl90Day.Header, tbl90Day.Header1, tbl_Questions.Question HAVING (((tbl90Day.Header1) Is Not Null)) ORDER BY tbl90Day.Header DESC "
- Set ors = New ADODB.Recordset
- ors.ActiveConnection = CurrentProject.Connection
- ors.Open _
- Source:="SELECT tbl90Day.Header, tbl90Day.Header1, tbl_Questions.Question FROM tbl_Questions RIGHT JOIN tbl90Day ON tbl_Questions.ID = tbl90Day.Header " & sql, _
- LockType:=adLockOptimistic, _
- options:=adCmdText
- If Not ors.BOF Then ors.MoveFirst
- Do While Not ors.EOF
- If ors(0) = "Type" Then
- Report_srpt_CPS90.Text18 = ors(counter)
- ElseIf ors(0) = "CLIENTNAME" Then
- Report_srpt_CPS90.Text10 = ors(counter)
- ElseIf ors(0) = "DATECREATED" Then
- Report_srpt_CPS90.Text21 = ors(counter)
- ElseIf IsNumeric(ors(0)) Then
- If question1 = "" Then
- question1 = ors(2)
- ElseIf question2 = "" Then
- question2 = ors(2)
- ElseIf question3 = "" Then
- question3 = ors(2)
- End If
- End If
- If Not ors.EOF Then ors.MoveNext
- Loop
- Dim strPara1 As String
- strPara1 = question1 & vbCrLf & vbCrLf
- strPara1 = strPara1 & question2 & vbCrLf & vbCrLf
- Report_srpt_CPS90.Text23 = strPara1
So, at the moment, it is listing 3 of the answers because I've referenced only 3 at this time.
The only problem with this is:
1 - having to do each client one at a time (Header1, Header2, Header3, etc).
2 - There are so many questions so it would become quite long and burdensome to do an elseif on each Client for each question.
Is there a simpler way of doing this?
I was leaning towards an array but I'm not array savy and it just confuses me.
Thanks in advance!