agwalsh
asked on
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
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.
ASKER
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!
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...
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :-)
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 ...
ASKER
@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.
Yes this is an issue to the end user....Excel feels just natural...when you want something more ...is where the issues popup...
ASKER
Had really helpful comments here about the merits of Access etc. Food for thought. Thank you
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 ...