By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,479 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,479 IT Pros & Developers. It's quick & easy.

CSV Import and show in report

P: 57
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.

Expand|Select|Wrap|Line Numbers
  1. 'First Client 
  2. counter = 1 
  3.  
  4. sql = "GROUP BY tbl90Day.Header, tbl90Day.Header1, tbl_Questions.Question HAVING (((tbl90Day.Header1) Is Not Null)) ORDER BY tbl90Day.Header DESC " 
  5.  
  6. Set ors = New ADODB.Recordset 
  7. ors.ActiveConnection = CurrentProject.Connection 
  8. ors.Open _ 
  9.      Source:="SELECT tbl90Day.Header, tbl90Day.Header1, tbl_Questions.Question FROM tbl_Questions RIGHT JOIN tbl90Day ON tbl_Questions.ID = tbl90Day.Header " & sql, _ 
  10.      LockType:=adLockOptimistic, _ 
  11.      options:=adCmdText 
  12.  
  13. If Not ors.BOF Then ors.MoveFirst 
  14. Do While Not ors.EOF 
  15.  
  16. If ors(0) = "Type" Then 
  17. Report_srpt_CPS90.Text18 = ors(counter) 
  18. ElseIf ors(0) = "CLIENTNAME" Then 
  19. Report_srpt_CPS90.Text10 = ors(counter) 
  20. ElseIf ors(0) = "DATECREATED" Then 
  21. Report_srpt_CPS90.Text21 = ors(counter) 
  22. ElseIf IsNumeric(ors(0)) Then 
  23.     If question1 = "" Then 
  24.     question1 = ors(2) 
  25.     ElseIf question2 = "" Then 
  26.     question2 = ors(2) 
  27.     ElseIf question3 = "" Then 
  28.     question3 = ors(2) 
  29.     End If 
  30. End If 
  31. If Not ors.EOF Then ors.MoveNext 
  32. Loop 
  33.  
  34. Dim strPara1 As String 
  35. strPara1 = question1 & vbCrLf & vbCrLf 
  36. strPara1 = strPara1 & question2 & vbCrLf & vbCrLf 
  37. Report_srpt_CPS90.Text23 = strPara1
  38.  
(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!
Jun 15 '09 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT ...
  2.      , CountYes([4398] & [4399] & [4400] & ... & [4480]) AS CountYs
  3.      , ...
  4.  
  5. FROM   [YourTable]
The function would be quite simple :
Expand|Select|Wrap|Line Numbers
  1. Public Function CountYes(strQs As String) As Integer
  2.     CountYes = Len(strQs) - Len(Replace(strQs, "Yes", "**"))
  3. End Function
Jun 15 '09 #2

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 15 '09 #3

P: 57
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
Jun 15 '09 #4

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 15 '09 #5

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

Thanks, NeoPa!!!!
Jun 15 '09 #6

P: 57
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:

Expand|Select|Wrap|Line Numbers
  1. If Not rs.BOF Then rs.MoveFirst 
  2. Do While Not rs.EOF 
  3.  
  4. For nextfield = 8 To 42 
  5. sql = "Select [Question] from tbl_Questions where [ID]= '" & rs.Fields(nextfield).Name & "'" 
  6. Set ors = CurrentDb.OpenRecordset(sql) 
  7. If rs(nextfield).Value = "Yes" Then 
  8.  
  9. 'Add results to table 
  10. sql = "Select * from tblTmpAllYes" 
  11. Set trs = CurrentDb.OpenRecordset(sql) 
  12.  
  13. trs.AddNew 
  14. trs(0) = rs(0) 'RD Name 
  15. trs(1) = rs(1)  'RM Name 
  16. trs(2) = rs(4)  'Client Name 
  17. trs(3) = rs(2)  'region 
  18. trs(4) = rs(3)  'Area
  19. trs(5) = rs(5) 'Type 
  20. trs(6) = rs(6) 'Date Created 
  21. trs(7) = rs(7) 'Notes 
  22. trs(8) = ors(0) 'Question Title
  23. trs(9) = rs.Fields(nextfield).Name   'Question # 
  24. trs.Update 
  25. End If 
  26.  
  27. If Not ors.EOF Then ors.MoveNext 
  28. Next nextfield 
  29.  
  30. If Not rs.EOF Then rs.MoveNext 
  31. Loop 
Jun 23 '09 #7

NeoPa
Expert Mod 15k+
P: 31,709
Good for you Dave. Sounds like you have it sussed :)
Jun 23 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.