Access to Excel in "Long to Wide" format

I need to pivot data in an Access table or query for export to Excel.  But I'm not looking for Pivot Table help -- I need all the details.

The data currently looks like this in Access:

ID   VisitDate   Test1    Test2
01     5/30/09     15       12
01     7/15/09     14       10
01     8/30/09     16        9
02     6/15/09     10        4
02     7/30/09     13       11
03    10/30/09    17       12
... etc.

I need it to end up in Excel like this:

ID  Visitdate1  V1Test1  V1Test2  Visitdate2  V2Test1  V2Test2  Visitdate3  V3Test1  V3Test2
01     5/30/09     15            12           7/15/09        14            10          8/30/09          16             9
02     6/15/09     10             4            7/30/09        13             11
03    10/30/09    17            12
... etc.

In reality, there are about 20 fields per Visit, and an ID can have unlimited Visits.  I was just trying to keep the example simple.

I have been trying every concatenate code I come across, but either they don't work for multiple fields, or I run into the 255 character limit in Access.  Or I've not implemented them properly -- my VBA skills are rudimentary at best.

I'm a statistician by training and this kind of transform is fairly simple in a stats program like SAS, but the folks I'm working for only have Access and Excel.

Any suggestions?

Thanks!
ClayinPGHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you will need VBa codes to do this. is this an option?
Rey Obrero (Capricorn1)Commented:
Patrick MatthewsCommented:
Hello ClayinPGH,

If you know the maximum number of rows for any given ID, *and* if all combinations of ID and VisitDate are
unique, you can do it without VBA.

Do you know that maximum (and if so what is it), and are those combinations unique?

Regards,

Patrick
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Patrick MatthewsCommented:
ClayinPGH,

I just tested this successfully using the sample data you posted.  I assumes that you have at most three items,
but can be easily expanded to accommodated more.  Change SomeTable to your actual table name.



SELECT z.ID, Max(IIf(z.Ordinal = 1, z.VisitDate, Null)) AS VisitDate1,  Max(IIf(z.Ordinal = 1, z.Test1, Null)) AS V1Test1,  Max(IIf(z.Ordinal = 1, z.Test2, Null)) AS V1Test2,
    Max(IIf(z.Ordinal = 2, z.VisitDate, Null)) AS VisitDate2,  Max(IIf(z.Ordinal = 2, z.Test1, Null)) AS V2Test1,  Max(IIf(z.Ordinal = 2, z.Test2, Null)) AS V2Test2,
    Max(IIf(z.Ordinal = 3, z.VisitDate, Null)) AS VisitDate3,  Max(IIf(z.Ordinal = 3, z.Test1, Null)) AS V3Test1,  Max(IIf(z.Ordinal = 3, z.Test2, Null)) AS V3Test2
FROM (
    SELECT t1.ID, t1.VisitDate, t1.Test1, t1.Test2,
        (SELECT Count(t2.ID)
        FROM SomeTable t2
        WHERE t2.ID = t1.ID And t2.VisitDate <= t1.VisitDate) AS Ordinal
    FROM SomeTable t1) AS z
GROUP BY z.ID




Patrick
ClayinPGHAuthor Commented:
Patrick:
Your SQL is great.  It gets me part way.  There is no set maximum for number of rows per ID, as the data is still being collected. Right now the maximum is 29, and with 20 fields per row, I will eventually run into Access's 255 field limit. Possibly by combining fields I can get a bit further.
Thanks!

capricorn1:
I glanced at the VBA code from the other example (not sure how I missed it before) and it looks like it might work, too.  I'll try it out.

Clay
Patrick MatthewsCommented:
ClayinPGH,

Another alternative...

Add the DConcat function to your VBA project, and then run a query like this:


SELECT SomeTable.ID, DConcat("VisitDate,Test1,Test2","SomeTable","[ID] = '" & [ID] & "'","; ") AS Results
FROM SomeTable
GROUP BY SomeTable.ID;


It will return the results concatenated in its own column, but will self-adjust to any number of items you need
to process:

ID      Results
01      5/30/2009, 15, 12; 7/15/2009, 14, 10; 8/30/2009, 16, 9
02      6/15/2009, 10, 4; 7/30/2009, 13, 11
03      10/30/2009, 17, 12

That function is described here:
http://www.experts-exchange.com/articles/Microsoft/Development/MS_Access/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

The source code:

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)
   
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
   
    ' Requires reference to Microsoft DAO library
   
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
   
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
   
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
   
    On Error GoTo ErrHandler
   
    ' Initialize to Null
   
    DConcat = Null
   
    ' Build up a query to grab the information needed for the concatenation
   
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")
       
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
   
    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF
           
            ' Initialize variable for this row
           
            ThisItem = ""
           
            ' Concatenate columns on this row
           
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next
           
            ' Trim leading delimiter
           
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
           
            ' Concatenate row result to function return value
           
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
   
    ' Trim leading delimiter
   
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
   
    GoTo Cleanup

ErrHandler:
   
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
   
    DConcat = CVErr(Err.Number)
   
Cleanup:
    Set rs = Nothing
   
End Function



Patrick
ClayinPGHAuthor Commented:
capricorn1:
The VBA code from the other example works for me if all the fields are named "fld#" and they are all data type = text.  

My coding skills are not up to modifying it to handle multiple fields.  So the solution I am thinking of is to aggregate all the Visit fields into one big text field called "fld2" and then run the code.  And then expand each Visit field once I am in Excel.

But, for relationship purposes within Access, I'd really like ID to stay numeric, and to stay "ID" instead of "fld1".  Can this be done?

Thanks!

Clay

Rey Obrero (Capricorn1)Commented:
yes... upload a copy of your db.. and i'll attend to it in a while..kinda busy ;-)
ClayinPGHAuthor Commented:
Hi Patrick,
I've tried something like the DConcat code you suggested, but because of the sheer volume of data associated with some IDs, my concatenated fields end up being truncated when they hit the 255 character per field limit.  Does this code avoid that?
Thanks!
Clay
Patrick MatthewsCommented:
ClayinPGH,

The function returns a string of whatever length it needs to be, but Access will truncate it at 255 characters in
the query results window.

However, if you open a recordset in code, you can transfer that recordset directly to an Excel file without losing
your characters.

(This is NOT the same thing as doing TransferSpreadsheet, BTW.)

Patrick
ClayinPGHAuthor Commented:
Hi Patrick,
Ok, I know TransferSpreadsheet, but how do I transfer a recordset to Excel without that?  Or without doing a standard export (which I assume will also truncate the fields)?
Thanks!
Clay
ClayinPGHAuthor Commented:
Capricorn1:
I'm attaching a stripped down database.  In testing things out, I created a button on "Form1" which runs "FlatTable" on the test table "tbltest" and creates the table "newtblflat".  The table I'd really like to transform is "All FU data".  But like I mentioned, I've created a table aggregating all the fields into one text field in "AllFUData Combined".

This seems like a huge job to me, so if you don't have time, don't worry about it -- I think I can get by with what you and Patrick have given me so far.

Thanks!
Clay
test.mdb
Rey Obrero (Capricorn1)Commented:
ClayinPGH,
<ot> what does PGH stands for?</>
ClayinPGHAuthor Commented:
<ot>Pittsburgh!</>
Rey Obrero (Capricorn1)Commented:
ok.. using the name of the fields from the table AllFUdata,
which field/s are we going to take the data from? ID, ?, ?
how do you want the output look like?
Patrick MatthewsCommented:
ClayinPGH said:
>>Ok, I know TransferSpreadsheet, but how do I transfer a recordset to Excel without that?

Something like this:



Sub ExportToExcel()
 
    Dim rs AS DAO.Recordset
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWs As Object
    Dim Counter As Long
    Dim SQL As String
   
    Const SaveToPath As String = "c:\Results\Report_"
 
    SQL = "SELECT SomeTable.ID, DConcat(""VisitDate,Test1,Test2"",""SomeTable"",""[ID] = '"" & [ID] & ""'"",""; "") AS Results " & _
        "FROM SomeTable " & _
        "GROUP BY SomeTable.ID"
   
    Set rs = CurrentDb.OpenRecordset(SQL)
    Set xlApp = CreateObject("Excel.Application")
    xlApp.DisplayAlerts = False
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets(1)
    With xlWs
        For Counter = 0 To rs.Fields.Count - 1
            .Cells(1, Counter + 1) = rs.Fields(Counter).Name
        Next
        .Cells(2, 1).CopyFromRecordset rs
    End With
    xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xls"
    xlWb.Close False
    xlApp.DisplayAlerts = True
    Set xlWs = Nothing
    Set xlWb = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    rs.Close
    Set rs = Nothing
 
    MsgBox "Done"
 
End Sub

Patrick MatthewsCommented:
ClayinPGH,

OK, just tried a little testing, and CopyFromRecordset also truncates at 255 characters, and a simple loop
though each column in the recordset is also proving problematic, but I have an idea that just may work...

Patrick
ClayinPGHAuthor Commented:
Patrick:
Ok, I'll give that a try.

Capricorn1:
I need ALL the fields from AllFUdata, grouped by ID.  That's why this seems like such a huge job.
So the fields in the spreadsheet will end up:
ID   FU1txweek   FU1DateContact   FU1DateNextContact  ...  FU2txweek   FU2DateContact   FU2DateNextContact ... etc

BTW, "FU" stands for "Follow Up", though I've been known to call it something else...

Thanks!
Clay

Patrick MatthewsCommented:
ClayinPGH,

My apologies, but if your data is going to result in a "results" string >255 characters, it is looking like my
approach is not going to work.  You are in excellent hands with cap1.

Patrick
ClayinPGHAuthor Commented:
Hey Patrick,

No problem. That's been my experience with this project all along -- work and work and work only to run into a roadblock at the end.

I still like your original SQL solution -- I can see that coming in handy in a lot of other places.

Thanks!
Clay
Rey Obrero (Capricorn1)Commented:
test this, open the table "newtblflat" after the clicking the button




test.mdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ClayinPGHAuthor Commented:
capricorn1:
That's excellent.  That's exactly what I want, except I need all the fields.  I'm pretty sure I can modify your code to do that.

With 23 fields x 29 (and counting) possible records per ID, I'm going to run into Access's 255 field limit, but I can combine some fields and then separate them once I'm in Excel.  And I can change from text to numeric in Excel also.

I have to step out for a few hours, but I'll try this when I get back.

Thanks!
Clay
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.