Search code examples
ms-accessvbams-access-2010ole

Split a Table to Format into Spreadsheet

I have a table, that has data like.

    ------------+---------------+---------------+---------------+---------------+---------------+---------------+------------+
    Agent Name  |   Total Fails |   To Process  |   Processed   |   Declined    |   Total Today |   Daily Net   |   Switched |  
    ------------+---------------+---------------+---------------+---------------+---------------+---------------+------------+
    John H      |       0       |       3       |       0       |       3       |       10      |       18      |       3    |
    Mary B      |       0       |       1       |       0       |       1       |       14      |       25      |       4    |
    Department  |       0       |       4       |       0       |       4       |       24      |       43      |       7    |
    ------------+---------------+---------------+---------------+---------------+---------------+---------------+------------+

What I would like is an Embedded Excel sheet in an Access Form. Added to the complexity, I need it to be organized in a different fashion. I need the Excel sheet to be in this format. 

    Agent Name      |   John H  |   Mary B  |   Department
    ----------------+-----------+-----------+---------------
    Total Fails     |       0   |       0   |       0   
    To Process      |       3   |       1   |       4
    Processed       |       0   |       0   |       0
    Declined        |       3   |       1   |       4
    Total Today     |       10  |       14  |       24
    Daily Net       |       18  |       25  |       43
    Switched        |       3   |       4   |       7

So in simple terms I would want the first Column values in the table to be the Column headers in excel. The column names to be the First column of the Excel sheet. I am unable to explain exactly what I need.

I need to transpose the Sheet, not completely. Any ideas or thoughts?

Solution

  • The following is one example, from Microsoft, of how to transpose the data rows/columns in a table: http://support.microsoft.com/kb/182822

    Titled 'ACCESS: How to Transpose Data in a Table or Query', it offers two methods:

    • Method 1 - Transpose the Data in Microsoft Excel
    • Method 2 - Use a Custom Function to Transpose the Table (includes sample code)

    Glad it worked!