I have 2.5M rows of data split across five tables by year. I want to combine the information into one. What's the best way to do this?
I have 2.5M rows of data split across five tables by year. I want to combine the information into one. What's the best way to do this?
Last edited by fervorking; 06-24-2011 at 08:40 AM.
Hi fervorking
The maximum number of rows is a 48K over 1 million. See http://office.microsoft.com/en-us/ex...010073849.aspx
Can you delete duplicates?
Can youj put the 5 tables into buckets or group your data somehow?
Can you use First-of-Day or First-Of-Week data instead of all of it?
I'm thinking pivot tables for each sheet and then copy and paste, values only into a combined table.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thanks for the response. I wasn't aware Access had the same limitations as Excel. The 2.5M is the cleaned up version. I need to run some resource-heavy formulas (Countifs, arrays) in Excel but thought I might be do some of it in Access before destroying my workstation. I'll figure it out. Thanks again.
Hi fervorking,
I was speaking about Excel only in the max number of rows. In Access I believe you can simply append your tables together using queries.
It looks like Access is limited by the file size and not the number of records. See
http://bytes.com/topic/access/answer...ccess-can-hold or
http://office.microsoft.com/en-us/ac...010030739.aspx for a better answer.
Are you just looking for the right SQL statement to combine the tables into one?
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks