Output question

[jarik77]

Hello everyone,

I hope someone can help me figure out a problem I'm stuck on. I would like to output column data into rows. I'm using MS Access 2000.

My current data set looks like this

testTable
List Name | Company
List 60 | MC
List 60 | SFK
List 60 | SM
List 59 | TK
List 59 | ICC
List 56 | UB

I'm trying to output this data to a TXT file that looks like this:

List 60 MC, SFK, SM
List 59 TK, ICC
List 56 UB

I think I need to use an array or two along with a loop. But I am having trouble understanding anything on the web written about arrays. I look at this problem and I think it should be easy, but it is driving me nuts. Any help would be greatly appreciated. Thank you very much!

Sincerely,

Jarik

 

[pbaldy]

This type of thing?

http://www.mvps.org/access/modules/mdl0004.htm

 

[jarik77]

Thank you for the info.

However, I was able to output my Company names only. I wasn't able to break after each list change.

My output looks like this now MC, SFK, SM, TK, ICC, UB

I still need it to break and start a new row when List Name changes.

 

[Khalid_Afridi]

Hi Jarik
Welcome to AWF, did you try CrossTab Queries in Ms-Access??
A crosstab query is a matrix, where the column headings come from the values in a field.

 

[pbaldy]

What exactly did you try? Properly done, that link will do exactly what you described.

 

[jarik77]

Thanks everyone for the replies. I eventually figured out my problem. It took me about 4 hours of trial and error, but I got it to work. Also, Pbaldy, you pointed me in the right direction, but I couldn't quite wrap my mind around the code you referenced. I'm still pretty green when it comes to coding in VBA. Anyway, I was able to solution without an array or two recordsets. Using the "Dim X as Variant" helped tremendously.

Lastly, here is the code I came up with. It works! But if anyone can find potential gaps I would appreciate it.

Sub ColumnToRows()
Dim cnnY As ADODB.Connection
Set cnnY = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnnY

Dim strCompany As String
Dim strListName, strListName2 As String
Dim strVar As Variant

MyFile = "C:\YourFileName.txt"
fnum = FreeFile()
Open MyFile For Output As fnum

myRecordSet.Open "[testTable]"
While myRecordSet.EOF = False
strVar = " "
strListName = myRecordSet.Fields(0).Value
strListName2 = strListName
While strListName = strListName2
strCompany = myRecordSet.Fields(1).Value
strVar = strVar & strCompany & ","
myRecordSet.MoveNext
If myRecordSet.EOF = True Then
strListName2 = " "
Else
strListName2 = myRecordSet.Fields(0).Value
End If
Wend
strVar = Left(strVar, Len(strVar) - 1)
Print #fnum, strListName & " " & Right(strVar, Len(strVar) - 1)
Wend
Close #fnum
End Sub

My output file looked like this:

List 60 MC, SFK, SM
List 59 TK, ICC
List 56 UB