Access and Excel Pivot Table

[matthew kramer]

Hi,

I'm using the reverse pivot technique on an excel spreadsheet to create
a list dataset from which I can make a pivot table in excell. The only
problem is that because there are so many years going across, whenever I
try to do this the computer tells me it was only able to do an
incomplete list because the full list exceeds 65,000 lines.

Apart from having to break the dataset down into smaller datasets, is
there some way that I could hook up the original excel dataset to an
Access database and do the reverse pivot technique successfully that way
in order to get a complete dataset list for the pivot table?

There are about 100 years going across in the excel dataset.

Any suggestions would be much appreciated. Many thanks.

Matthew Kramer


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Seeto via AccessMonster.com]

Re: Access and Excel Pivot Table

Hi Matthew,

Wow, that's a lot of years going across the page - if you link this
spreadsheet into the database, you should get each year as a column in the
resulting table eg. [ID], [Year1], [Year2], [Year3], ... [Year100].

You then need to set up a query that returns the results each year:
Query1: [ID], "Year1" as Year, [Year1] as Measure
Query2: [ID], "Year2", [Year2]
Query3: [ID], "Year3", [Year3]

You then need to join up these queries in a UNION query

SELECT * from Query1 UNION
SELECT * from Query2 UNION
SELECT * from Query3 UNION

etc.

A bit of a tip: so long as the first query has the right column headers
("Year" and "Measure"), then the whole query will get those columns, even
though the subsequent queries don't, which saves you some time and effort
in setting up the queries.

Since you've got more than 100 years of data, it will be a bit laborious to
set up in the first place, but it should be easy to add to after that.

The only problem you might run into is a limit on the number of queries you
can append together using the UNION statement: I don't know of any, but if
it happens, you may need to append the data into a separate table instead.

Hope that helps - I'd appreciate some indication of how it works out for
you!

--
Message posted via http://www.accessmonster.com