Best way to link to a massive file Excel or Access or something else?

I've got a scenario where I am calculating deferred income. The solution which I came up with - which works fine with a small number of customers and years was to create a table which had a daily entry for every customer (200 or so) over 2 years - linked to a number of other tables and with formulas to calculate the various parameters.  Now my problem is that I now have to scale it up to include 200 customers over 5 years and I am running into capacity problems with Excel. I have saved the file as a binary file and done it on a 64 bit machine (as per advice here) but when I go to do the unpivot in Power Query to do this (to get the number of days per customer)  I get messages about the file being too big etc. So now I am thinking - should I put this into Access and link the table to an Access table - seems to be fewer capacity problems with that or what would you suggest? Thanks
LVL 1
agwalshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
Access can do just about everything without capacity limits...even it has a limit of 2GB pef database it can also use multiple databases or link to limit free SQL server implementation like MSSQL,MySQL,Oracle,PostGre...
Also take note that Access is a database not a spreadsheet...so what ever is limiting you just doesn't apply....and you can always export your data to Excel for some manual intervention ...
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Spreadsheet data often must be converted to normalized data (and that sounds like the case here), so moving to access (or any other database) would require some work on that data. After normalizing I'm sure you'd find Access up to the task you mention.
1
agwalshAuthor Commented:
Yes, the data would be normalised. OK, so let's say I get it into an Access table (normalised etc) and it grows and grows :-) maybe past Excel's million plus limit - how can I keep the link between the Excel sheets and this data set...the users are not very keen on the Access option but since I can eat my dinner and have a cup of tea while I am waiting for it to open - they may be open to it!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

John TsioumprisSoftware & Systems EngineerCommented:
Nobody wants to scroll thousands of lines ...you jut pick your filters in Access and a "nice" export in Excel is the solution...few rows with the needed info...nothing more nothing less...
0
Dale FyeCommented:
agree with John and Scott.  You have the point where you really need to dump Excel and use Access for your data storage (and computational) needs.   you can always export the results to Excel, or allow the users to pull data from Access into Excel if that is a must.

But you can create datasheet forms that would allow your users to view the data in a datasheet, if they need an Excel like appearance.
0
PatHartmanCommented:
saved the file as a binary file and done it on a 64 bit machine (as per advice here)
I'm pretty sure that wasn't quite the advice you got.  Yes, you need a 64-bit machine but pretty much all PC's have been 64 bit for many years.  They were talking about installing the 64-bit version of Office.  That supports million row spreadsheets.  HOWEVER, it will cause problems with Access because you will need to change any API's you use to be the 64-bit versions and unless you make changes so that you use a compile time switch to swap between 32 and 64 bit code, the database will operate in only one environment and if you distribute .accde's, you would nee to create separate versions.  Not a good idea if you can avoid it.

I recently taught a bunch of data analysts who had the same problem - humongous record sets- how to use Access as a preprocessor.  They didn't want to give up their models but even using 64-bit office, Excel couldn't keep up.  They were wasting hours each day waiting for vLookups to run as well as calculations.  So, I taught them how to use queries to perform joins to eliminate the vLookups and then make them totals queries to aggregate or eliminate data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
agwalshAuthor Commented:
Basically all the user wants is a pivot that allows them to see the deferred income at certain dates. I've got all that done and working but now they want it done for 5 years and I'm tearing my hair out as I wait for Excel to load it...the person doesn't want to use Access because - usual story - they had someone who managed their Access databases in a previous life, then they left and no one knew what to do with them. I reckon what I am going to do is see if I can get this humongous file unpivoted in Power Query, dump it into Access and then build a PowerPivot model using measures to get the same effect. It's so frustrating to be so close :-)
0
John TsioumprisSoftware & Systems EngineerCommented:
Well with carefull design you can implement an Access solution that would be future proof...and sorry on that...its way too easy to get Access support ...for a decent amount of money you can get all your problems resolved and even get more features...but unless money are laid on the table none is going to take the next step...its too time consuming and even if you think "big deal...he/she just put a button....or worse just changed a line of code..."...this single change can carry a lot ...
0
agwalshAuthor Commented:
@John Tsioumpris, thanks for the comment. The user doesn't want Access - which I agree it's probably the most stable in the long run and I did mention Access to them. Right now I've posted another question to help me create the query in Access and I'm going to take it from there. I don't expect anyone to build it for me in Access. I've got all the formulas working with my current model - it's just this particular  data set has to go somewhere other than Excel. That's my issue here. I get that saying "can you just change X" is something that can be a lot more problematic than seems to the user.
0
John TsioumprisSoftware & Systems EngineerCommented:
Yes this is an issue to the end user....Excel feels just natural...when you want something more ...is where the issues popup...
0
agwalshAuthor Commented:
Had really helpful comments here about the merits of Access etc. Food for thought. Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.