Transposing Data within tables

[PWP]

Hello,

Hopefully someone can help me out as I've been trying unsuccessfully for about 2 days to figure out how to do this. I've seen similar posts on various forums, but none of the solutions have been particularly useful for me. Basically I have a table within Access, an example cut of which would look like this:

ID No 1 No 2 SA
680836 32469034 1 200000
680837 32469034 1 50000
680838 32469034 1 150000
680839 32469034 1 50000
680840 32469034 8 119000
680841 32469034 19 119000
680842 32469034 23 25000
680843 32469034 24 25000
680844 32469034 25 119000
680845 32469034 26 23800
680853 32469046 2 71000
680854 32469049 1 50000
680855 32469049 22 107
680856 32469049 22 107

What I would like to do is to convert it into a more useable format that I can link into other queries I run from the same database, and also be able to export into Excel (Currently the report is almost 300,000 rows - I've worked out that transposing it in the desired way will cut it down to under 60,000 rows). The format I would like to get the data into is:

No 1/No 2/SA/No 2/SA/No 2/SA/No 2/SA/No 2/SA/No 2/SA/No 2/SA/No 2/SA/No 2/SA/No 2/SA
32469034/1/200000/1/50000/1/150000/1/50000/8/119000/19/119000/23/25000/24/25000/25/119000/26/23800
32469046/2/71000
32469049/1/50000/22/107/22/107

I hope this makes sense; any help would be very much appreciated.

Thanks all.

 

[ByteMyzer]

The following code will create a delimited text file from your table (make the appropriate changes to the highlighted text):

Public Function QueryDataGen( _
    ByVal sFileName As String, _
    Optional sDelimiter As String = ",")

Dim ff As Integer
Dim qd As QueryDef
Dim rs As DAO.Recordset
Dim sBuffer As String
Dim vTemp As Variant
Dim X As Long, Y As Long

Set rs = DBEngine(0)(0).OpenRecordset("SELECT COUNT(T1.ID) " _
    & "FROM [COLOR="Red"][b][i]MyTable[/i][/b][/COLOR] AS T1 " _
    & "GROUP BY T1.[No 1] " _
    & "ORDER BY COUNT(T1.ID) DESC", dbOpenSnapshot)
X = rs(0)
rs.Close
Set rs = Nothing

sBuffer = "No 1"
For Y = 1 To X
    sBuffer = sBuffer & sDelimiter & "No 2-" & CStr(Y) _
        & sDelimiter & "SA-" & CStr(Y)
Next Y

Set rs = DBEngine(0)(0).OpenRecordset("SELECT T1.* " _
    & "FROM [COLOR="Red"][b][i]MyTable[/i][/b][/COLOR] AS T1 " _
    & "ORDER BY T1.ID", dbOpenSnapshot)
Do While Not rs.EOF
    If vTemp <> rs("ID") Then
        vTemp = rs("ID")
        sBuffer = sBuffer & vbNewLine & rs("ID")
    End If
    sBuffer = sBuffer & sDelimiter & rs("No 2") & sDelimiter & rs("SA")
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

ff = FreeFile
Open sFileName For Output As #ff
    Print #ff, sBuffer
Close #ff

End Function

Examples:

To create comma-delimited file C:\QRY.TXT:

QueryDataGen "C:\QRY.TXT"

To create TAB-delimited file C:\QRY.TXT:

QueryDataGen "C:\QRY.TXT", vbTab

See if this solution works for you.