Access to Excel in "Long to Wide" format

you will need VBa codes to do this. is this an option?

see this similar thread

https://www.experts-exchange.com/questions/25122313/how-do-I-transpose-these-two-fields-to-rows.html?anchorAnswerId=26510240#a26510240

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

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

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

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:
https://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

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

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

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

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

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

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

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

<ot>Pittsburgh!</>

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?

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

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

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

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

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

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