Summarize 629,487 records - HELP!!!!
Posted by JAF on July 17, 2001 6:46 AM
Hi
I have a VERY large table which currently contains 629,487 records (obviously far too large to fit Excel's 65,536 row limit).
The data consists of a list of accounts and the number of months in arrears each account is at each month end. There are over 31,000 accounts covering data for 64 month ends (accounts that completed more recently obviously have less "rows" of data in the database.
In my SQL databse, the data is in the following format:
Account_Number..Month_End_Date..Months_In_Arrears
What I need to do is to somehow summarise the data in such a way that each Account_Number is listed in Column A, each Month_End_Date in row 1 in a seperate column starting at Column B, with the associated Months_In_Arrears data for each Account_Number being shown for Month_End_Date.
In other words:
......jan.feb.mar.apr
12345.1...2...1...0
12346.0...0...0...1
I can't think of any way to summarize the data in the above format. Any suggestions???
Posted by Mark W. on July 17, 2001 7:24 AM
In a word -- PivotTable.
Posted by JAF on July 17, 2001 8:51 AM
Pivot Table - Not an Option
In 2 words -- won't work!!
There are over 31,000 accounts which is too many for a Pivot Table to handle.
Any other suggestions??
JAF
Posted by Mark W. on July 17, 2001 9:01 AM
Re: Pivot Table - Not an Option
What exactly are you trying to accomplish that you
can't do in your database environment?
Posted by Barrie Davidson on July 17, 2001 9:45 AM
Have you tried importing your SQL data in to an Access database and then running a crosstab query in Access?
Barrie
Posted by JAF on July 18, 2001 3:53 AM
I'll give the Access crosstab a try to see if it's any easier/quicker than the option I'm using at the moment.
I currently use a program called Seagate Info Report Designer to produce a crosstab and then export that data into Excel - with any luck Access will be a bit quicker!!
I was hoping there would be a way completely within Excel, but if not, I've at least got another option to explore now.
Thanks for replies guys... Have you tried importing your SQL data in to an Access database and then running a crosstab query in Access? Barrie
