Access to Excel in "Long to Wide" format

ClayinPGH
ClayinPGH used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
you will need VBa codes to do this. is this an option?
Top Expert 2016

Commented:
Top Expert 2010

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2010

Commented:
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

Author

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
Top Expert 2010

Commented:
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

Author

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

Top Expert 2016

Commented:
yes... upload a copy of your db.. and i'll attend to it in a while..kinda busy ;-)

Author

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
Top Expert 2010

Commented:
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

Author

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

Author

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
Top Expert 2016

Commented:
ClayinPGH,
<ot> what does PGH stands for?</>

Author

Commented:
<ot>Pittsburgh!</>
Top Expert 2016

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?
Top Expert 2010

Commented:
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

Top Expert 2010

Commented:
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

Author

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

Top Expert 2010

Commented:
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

Author

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
Top Expert 2016
Commented:
test this, open the table "newtblflat" after the clicking the button




test.mdb

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial