CSV Import and show in report

[DThreadgill]

Don't know if this can be done or not but I'm sure the gurus here will let me know ;)

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.

'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
 

(The join to tbl_questions is so that the actual question/statement will be shown).

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!

[NeoPa]

As I don't know what RD refers to I found almost everything after that point almost meaningless. A shame as it seemed very clear up to that point (and you'd clearly put effort into explaining as fully as possible).

Let me see if I can post something helpful anyway. If you need more then I'll check later for a clarified question.

First of all, it seems to me that your data is presented to you in a format that is not designed to enable you to do what is requested. The good news though is that some VBA coding can help to determine the count of the Yeses in all the numeric fields. With a function set up called CountYes() you could have SQL in your query similar to :

SELECT ...
     , CountYes([4398] & [4399] & [4400] & ... & [4480]) AS CountYs
     , ...
 
FROM   [YourTable]

The function would be quite simple :

Public Function CountYes(strQs As String) As Integer
    CountYes = Len(strQs) - Len(Replace(strQs, "Yes", "**"))
End Function

[NeoPa]

There is another possible approach. It's still code-based, but may be easier to understand for some.

You can create a basic query on the table and use this in code as a RecordSet. In a RecordSet you can access the .Fields collection and process through them all in a loop (For Each ... Next) excluding all other known fields or including only those whose name is made of 4 digits.

This could be done as an update query or a Make-Table query to make the values available generally. There are many options available once you have the basic concept.

[DThreadgill]

Sorry NeoPa - - I meant to change the fieldnames in the first part before i actually posted. RD = RDNAME as shown in the image. RM = RMName. I guess trying to simplify actually made it worse! :)

Here are the correct field names as imported in my Main table:
Issue (text)
Returned_Date (Date/Time)
RDName (text)
RMName (text)
Region (Text)
Type (text)
ClientName (text)
4398
4399
4400
etc.

You're correct in that the format of the .csv file is definitely NOT designed in my favor but since it's coming from an outside source, I have to make do (or not if not possible). I've already requested changes that they won't make. As it stands, there's a possiblity that if a particular numeric field isn't populated across the board, it won't be exported with their process so my import process is already defunct!

I can get the count with no problem but I have to reveal the actual questions for each client in the report. :)

I think I get the concept of the .fields collection and will work in that direction and see where it takes me.

Hope this clears up my previous post. :)

Any additional ideas will be greatly appreciated

[NeoPa]

Ah, so would I be right in thinking you must show all the Yeses with their titles included, but ignore those that aren't Yes?

The .Fields collection would probably be the way to go here I think.

[DThreadgill]

Bingo! Haven't started on it yet...I'm a procrastinator :) Any problems, I'll sure let ya'll know!

Thanks, NeoPa!!!!

[DThreadgill]

After a bit of frustration, I finally got the .fields to work :) I appreciate your guidance NeoPa....I'd still be trying to figure out which way to go!!!

As per your suggestion, got the fieldnames using the .field collection and added to a table:

If Not rs.BOF Then rs.MoveFirst 
Do While Not rs.EOF 
 
For nextfield = 8 To 42 
sql = "Select [Question] from tbl_Questions where [ID]= '" & rs.Fields(nextfield).Name & "'" 
Set ors = CurrentDb.OpenRecordset(sql) 
If rs(nextfield).Value = "Yes" Then 
 
'Add results to table 
sql = "Select * from tblTmpAllYes" 
Set trs = CurrentDb.OpenRecordset(sql) 
 
trs.AddNew 
trs(0) = rs(0) 'RD Name 
trs(1) = rs(1)  'RM Name 
trs(2) = rs(4)  'Client Name 
trs(3) = rs(2)  'region 
trs(4) = rs(3)  'Area
trs(5) = rs(5) 'Type 
trs(6) = rs(6) 'Date Created 
trs(7) = rs(7) 'Notes 
trs(8) = ors(0) 'Question Title
trs(9) = rs.Fields(nextfield).Name   'Question # 
trs.Update 
End If 
 
If Not ors.EOF Then ors.MoveNext 
Next nextfield 
 
If Not rs.EOF Then rs.MoveNext 
Loop 

[NeoPa]

Good for you Dave. Sounds like you have it sussed :)