Smarter ways to Pivot Data without Using Pivot Tables

Question

I have got some data I would like to pivot and display the results in Access and Excel.    I would prefer not to use the Pivot Table feature which has sub totals and extra columns where I don't want them.  In Access I want to get the pivoted data on a form (datasheet or continuous form) or a report of some kind and also into a nicely spaced matrix in Excel

The data is financial data - a cash flow type of thing so I want to get the years and months across the top.    There are four groups of data I want to subtotal in the report.   The number of rows for each period could vary because not all expenses will be populated for each month.

Does anyone know of any good appraoches to do this?

This is what I am contemplating:

* To create a dummy recordset not bound to a table. (e.g. based on something like SELECT " as Col1, "" as col2, "" as col3).  Note there is not meant to be a table name....the idea is to create a recordset with columns and no rows initially

* To repeatedly parse another query to create first the column headings for each each month, then each row using the .Add and .Update methods

* To bind the dummy query to a form or report

I have never tried anything like this and wonder if anyone knows whether it might work or what alternative approaches I could try.

---

PG A bit of experimentation by trial and error often helps.

Answer 1

There's a very simple example of this sort of thing as Payments.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

You might have to copy the text of the link into your browser's address bar (not the link location).  For some reason it doesn't always seem to work as a hyperlink.

The demo shows how to open a report in a 'crosstab' type format, in this case with the dates of a week as column headings, payees as the 'row headings' and the payments per payee as the data at the intersections.  The values are summed per payee and as a grand total.

The way it works is by using two multi-column subreports, one to generate the column headings, the other the values.  The parent report returns the payees and the second subreport is linked to the parent report on the payee, so each row returns the data per payee.  Both the column headings and the data are restricted to a selected week by referencing a parameter in a form from which the report is opened.

The report will not export correctly to Excel, however, as the layout is determined by the across-then-down column flow of the subreports, which is meaningless to Excel, so the values per payee would be inserted into Excel as a single column of cells, not in a set of cells across one line.

---

Ken Sheridan, Stafford, England

Answer 2

Ken

Thanks for your reply.   I went through the technique and I did think it was quite clever and certainly something to bear in mind as a possibility. This option istrongly ties the solution to Access and as you say it cannot be transferred to Excel, so it does not quite do what I wanted.

The vision I had was perhaps to get the data into a Datasheet in Access which could be easily copied and pasted into Excel.  I am still pretty much at the starting point in thinking - how do I do this?   As far as I can see, my options I see are:

1. I could return the results of a query in a recordset and loop through it once for each period, transpose the data and store it in a temporary table.    I could then put the transposed data in the temporary table in a datasheet and display it.  (But  ...  this is quite a lot of work, certain to work in the end but surely there must be simpler way ???   )

2. To try and create recordset not bound to a table, create columns for it and then add rows in the way described above - However, I not certain about whether it would work because it is territory I have never covered before ...

I wonder if you could help by providing your views on a couple of questions:

• Is the idea of creating a dummy non table based recordset viable - or would it be a hiding into nothing?  (I don't really like the idea of creating temporary tables if I can avoid it.)
• Is there any way of getting an array on to a data sheet?  How?

I did find the post below which refers to doing something similar to what I am contemplating using ADODB and dates back to 2004.  

http://www.dbforums.com/microsoft-access/1002213-create-unbound-recordset-ado.html

• PS: I forgot to mention, the data I am working with falls into 3 groups each of which may or may not be subtotalled depending on how many rows are in each group.  So if there is only one row in a group, no need for subtotal - and I am not sure how many rows there will be in each group until the data is populated.  That was why I felt the best option would be to put the data in a datasheet, allowing the possibility of tidying it up in Excel and then ultimately a report when shape and size are better known !!!

---

PG A bit of experimentation by trial and error often helps.

Answer 3

Hi Patrick.Grant01,

Thanks for posting in the MSDN Forum.

It's based on my experience that we aren't avoid create temporary table in your issue. I will involve some experts into this issue to see whether they have better work round. There might be some time delay. Appreciate for your patience.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Tom

Thanks for your interest.  I look forward to any suggestions that your colleagues can suggest.

I have tried Ken's suggestion of using multi column reports to rotate the data.   I have a few issues with it which were partly expected. These are:

• The data cannot be pasted from Access to Excel.   It cannot be exported to Excel successfully because it loses alot of the formatting in the report.  What did come as a pleasant surprise was the data is tab delimited in Word which makes it easy to import into Excel.  A bit awkward but do-able.  Overall opinion, a bit clunky but better than pivot tables !!!
• The method only works if all cells in the matrix are populated, so my first attempt ended up positioning everything in the wrong place due to voids in the data.   I managed to fix it with a Query and SQL that was designed to generate a Carthesian product and replace nulls with a zero.

I am starting to look at other alternatives to get the data transformed into a data sheet, so that it can easily be copied and pasted into Excel.   I intend to try initially with a temporary table - and again with a temporary recordset, if I can, and no temporary table. Will report back on that later.  I think my idea of a temporary recordset may just work but I have never heard of anyone doing it.

---

PG A bit of experimentation by trial and error often helps.

Answer 5

Tom

A quick update.  I am now pivoting the table using DAO recordsets to reformat the code.  While it has taken about a days work to do this, it does seem a much more flexible way of doing the pivot than other alternatives.

---

PG A bit of experimentation by trial and error often helps.

Answer 6

Hi Patrick.Grant01,

I can't think of anything else other than programmatically creating the pivot table which it sounds like you've already got working.  Normally I would suggest opening up a support case with us but I'm not sure we'd come up with anything further.  Hopefully other community members can make some suggestions.  Here are some links that may give you some ideas.

How to programmatically build a pivotTable view in an Access 2002 form
http://support.microsoft.com/kb/298764

Programming Pivot Tables for Access Forms
http://msdn.microsoft.com/en-us/library/aa662945(v=office.11).aspx

Creating a PivotTable Programmatically - Andrew Whitechapel
http://blogs.msdn.com/b/andreww/archive/2008/07/25/creating-a-pivottable-programmatically.aspx

Sorry I couldn't help!

---

Sharon M, Microsoft Online Community Support