Nightlytic
Member
Looking for opinions 
So, I've been running a little side project at work, we have an accounting system with multiple reporting tools none of which play with excel, and this means multiple processes and very confusing procedures to get sometimes very basic information...
So I imported it all to Excel, arranged in workbooks, by month lest the file size would be absurd, and produced a VBA ran process that can extract a lot of information out of these files, based on what the user wants. Having done so I now have a tool that takes about 20-80 seconds to run and it produces lovely result for one division of the whole organisation. In thinking of how to bring this across to work for other divisions, I now realise there may have been a better approach.
I am clueless on SQL and Access, but I think I could technically download the excel data every first day of the month, input necessary helper fields using excel macros, import it to an Access database, and then on demand I or others can run an SQL query (based on what user selects in excel/vba interfaces) to pull any information required.
Do you think this would make a clean process? Efficient? Could it handle, say,
-2-5 million rows of data, c.20-40 columns wide
-around 10 various filters , some on date values, some to narrow down on a range of values for examples a large list of vendors to filter for.
-Can Access summarize information? Like given query, give back the report as a summary total for the values?
Now, if you think this is something to pursue, this will require me studying up on Access and probably ways to tap into it, if you can think of any place where I can start please let me know
I don't have any resources yet.
So, I've been running a little side project at work, we have an accounting system with multiple reporting tools none of which play with excel, and this means multiple processes and very confusing procedures to get sometimes very basic information...
So I imported it all to Excel, arranged in workbooks, by month lest the file size would be absurd, and produced a VBA ran process that can extract a lot of information out of these files, based on what the user wants. Having done so I now have a tool that takes about 20-80 seconds to run and it produces lovely result for one division of the whole organisation. In thinking of how to bring this across to work for other divisions, I now realise there may have been a better approach.
I am clueless on SQL and Access, but I think I could technically download the excel data every first day of the month, input necessary helper fields using excel macros, import it to an Access database, and then on demand I or others can run an SQL query (based on what user selects in excel/vba interfaces) to pull any information required.
Do you think this would make a clean process? Efficient? Could it handle, say,
-2-5 million rows of data, c.20-40 columns wide
-around 10 various filters , some on date values, some to narrow down on a range of values for examples a large list of vendors to filter for.
-Can Access summarize information? Like given query, give back the report as a summary total for the values?
Now, if you think this is something to pursue, this will require me studying up on Access and probably ways to tap into it, if you can think of any place where I can start please let me know