• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Opinion on a work project

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.
 
It sounds like a project for a Power BI person
Power BI can handle millions of rows of data

You could also think about using ODBC, SQL or Access to import summary data into Excel

Most, even small, accounting packages have ODBC, Access or SQL access available

Which accounting system do you need to query?
 
Sure it can be done. Though I prefer to use MySQL or MS SQL for the job since it's more robust and easier to maintain user access levels, deploy it across different network etc.

MS Access is certainly capable of what you intend to do.

What I would recommend is following.

1. Have your IT department involved
2. Get buy-in from stakeholders
3. Build redundancy and succession planning
4. Set up user access level through ActiveDirectory instead of in Access. (Hence recommendation to get IT involved).

Network folder that house the file should be set to Read/Write access to everyone that's going to access data. Then file should be set to Read-Only for majority, and Read/Write for admins.

Summary, criteria etc can all be handled via query. I'd suggest you set up Parametarized Query.
https://support.office.com/en-us/ar...-a-query-C2806D3D-D500-45A8-8507-EC6AF351B6ED
 
Hi Hui,

It's SAP, but I only have the user interface, I can run reports and that's about it. I don't have good IT privileges, certainly can't install whatever software I wish and it's not really an option anyway, that's why I'm thinking of using Access. I was conscious from the start that as I envision it, because I am only a front-end user I will need to download these reports and import them in every month, but I think that won't be too much hassle I can certainly make it easier for myself with some macros maybe bring it in. If the process actually ends up saving time for others, I believe I can ask IT to download the extracts for me as is usually done for established processes.

Chihiro,

I am fairly junior and absolutely new on the job, I don't have much in terms of management (don't ask how, I wouldn't know how to explain it) so I think that's out of the question. I feel that given a few months I can work this into a parallel process, show it to someone senior and then gain a buy-in, IT support and succession planning. So to say I think I need to prove myself and show that the idea has merit.

I am confident my co-workers will love it. They are very excel-savvy, but boil whenever they have to get through SAP. This information will, regardless, eventually end up in excel because people need to marry it to other data (which I will also build in)

So uh, for now, I am on my own. And that's ok. It's a learning exercise as well! I learned a lot about VBA the past 2 months creating the prototype and I've no regrets there. I'm very proud of it, I just grew greedier now. I want it to cover the entire organization.
 
Summary, criteria etc can all be handled via query. I'd suggest you set up Parametarized Query.
https://support.office.com/en-us/ar...-a-query-C2806D3D-D500-45A8-8507-EC6AF351B6ED
Chihiro, can I ask you, if I was to do this but want to use parameters from excel ranges, would that be possible? Name ranges within VBA, pass them onto the SQL code to run?

For example, I have a range, A2:A10 where all the divisions are,
whether user wants Division A
Or
Division A
Division B
Division C

And the macro filters the dataset for these. Could I achieve that with SQL, without actually moving the selection process to SQL? Still type in Divisions in range A?
 
I'm sure you could. But why not create a view that stores only the pertinent/summarized info?

Then have users query views to their heart's content. They can use any standard SQL query including date range in Where clause.
 
Oh and if it's SAP (HANA or BW) then PowerBI should be able to connect to it.

Unfortunately MS removed ability to do export to Excel from non Pro subscription. But you can still connect to it, build report in PowerBI desktop, then copy paste data to Excel.

PowerQuery/Get & Transform with ProPlus subscription to Office 365 will have ability to connect to SAP HANA (though not BW).
 
I'm sure you could. But why not create a view that stores only the pertinent/summarized info?

Then have users query views to their heart's content. They can use any standard SQL query including date range in Where clause.
I think I would like to offer both- totals by ledgers, cost centres, vendors whatever users want, but also transaction level data to let them dive in. For that purpose, I think if I had an access database with all the transactions, I myself can have a macro to once monthly populate the summary information from this database. Users (me being one of them) can then use summaries or the access data without actually going into access.

I do have PowerBI, some sort of corporate subscription it might be pro. I would however definitely need the summaries and transaction level data in excel. A lot of reporting is done off it. Anyones first question would be how to pull this data to excel and I can't blame them it's just a very awesome intuitive tool. I would definitely look to use it once the dataset is complete. Maybe plug it into access.
 
Once I have a database with all transactions, and a way to extract out of it, I am confident I can manage the rest anyway
I already more or less have this done for the vba a few sample reports that run on the data and save it to individual folders, let users navigate between them that sort of stuff. I just need to replace the current step that has Excel workbooks with SAP data, files by month, and brings them to merge for whatever period user has selected, with one that has it all in a single database, access, and is hopefully quicker and more intuitive.
 
Back
Top