share point or ms access or microsoft SQL server or any other software for process automation and reports

D_wathi
D_wathi used Ask the Experts™
on
Dear Experts

We have marketing, sales, customer care, projects, legal, finance , accounts , admin, HR and many stake holders for each department and also management team who would like to know the daily/real time, fortnightly reports from every departments and from every branch office like region wise and all put together i,e  consolidated report and finally COO/CEO/CFO and MD get the numbers report,  though we have multiple systems like for sales and marketing SFDC and for some entry level ERP and few departments are operating with spreadsheets, we are looking for the solution where reports and associated data mainly reports from many systems like SFDC, ERP and spreadsheet all the reports of these are hosted in some location and by using some solution and create the reports and accessible to the stakeholders, please let me know for this process automation what solution would be a best while I understand we have to integrate applications/integrate with BI tool but this would take much longer time we are looking for some interim solution, at present we are using nextcloud for file share and sync can we ask users to palce their final versions of data in the folders and then use a solution like share point /ms access/Microsoft SQL server to achieve this process automation and reports for all the stake holders , not sure if sharepoint would be the solution, can you please suggest software solution for this so that we can go for that and search for software person skilled on those software, please suggest, thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
Well you posted a LOOT of info in a single question...if its a definite answer then that should Ms Access...with some "extra"...
Given the fact that you don't have a central system and with the lack of more precise info and detailed ...i will make a wild guess. and start..
I reckon that you have some kind of ERP/s that some departments use and some departments don't...here comes the 1st strike of Access...unless we are talking about strange situations then Access should connect to each and every one and "gather" the data.
You mentioned spreadsheets ...if they are used for anything else than simple calculations then the burden gets bigger you are asking for trouble...so again converting to Access will give you the power you want....
Real time info....well it depends and i wouldn't go that route at least for start....info in intervals it would be a piece of cake...
Reporting...well everybody knows that Access is a very good tool regarding reporting...
FileSharing and Cloud is something for the next Level...although Access can also help here ...
But this is for start...if you have such a great potential then probably later on you will have to switch to Enterprise Web solution...but (again) for the time being i reckon that Access is the tool you need....it can and it will scale to just about every direction you want ,,,and it will require a lot of work to get something better..
A timeline should be :
Access (FE+BE) ....an Access probably for Each department  ---> Access +BE (now the Access is "common" to all departments) ...the BE is a shared Access somewhere in your network ---> Access +SQL BE (Maybe Cloud) easy remote access ---> Web Solution + SQL BE

Author

Commented:
Thank you  very much for the reply, while I understand ms access is the choice now to start but I am sorry I did not understand the following , please help me understand the following, at present only spreadsheet keeps getting exchanged internally but until we go for the enterprise solution I understand from your inputs MS ACCESS can be solution , please help me understand the following, thanks in advance.
A timeline should be :
Access (FE+BE) ....an Access probably for Each department  ---> Access +BE (now the Access is "common" to all departments) ...the BE is a shared Access somewhere in your network ---> Access +SQL BE (Maybe Cloud) easy remote access ---> Web Solution + SQL BE
John TsioumprisSoftware & Systems Engineer

Commented:
Is a "timeline" from 0 you are now to a full global accessible solution
You start with simple Access applications per Department ...maybe even on user basis ...just a simple Access that solves the most demanding issues...e.g. we move a "troubling" spreadsheet to an Access application ..this can take little time..or a long time depending on necessities .
Then you move to Access applications that are accessed by everyone in the company...with some time using Access through out the departments ...we move ahead to a unified solution that is "common" to every department either physically or logically...the data are cross examined to identify the shared ones (the ones that are common to all/some departments) and the "unique" ones...
Due to size/complexity/versatility you move to a BE that is using  a  "real" database engine like MSSQL,MySQL..etc...Access can handle a lot of data no matter what but due to necessity mainly for outside access and given the fact that are some areas that simply other databases are better at (e.g. Firebird for storing large document file)...you plan to move ...this of course won't happen in a day...but it could start slowly and evolve...
Lastly due to the fact that everyone wants data access from anywhere even on the mobile ...the BE stays intact but we move to a Web based FE that replaces Access...the BE could even move to the cloud.
From the info you gave in your question we are looking at some years to spend as this is an evolving procedure...
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Top Expert 2014

Commented:
You might look into PowerBI:

https://powerbi.microsoft.com/en-us
Distinguished Expert 2017
Commented:
Access is a good solution for consolidating data but it is NOT a good solution for a distributed application.  It is possible to use SharePoint lists as the BE for an Access FE but SharePoint leaves a lot to be desired starting with - it is not a relational database and doesn't support tables exceeding a few thousand rows.

You could use "department" applications to consolidate data at that level and then send it to a corporate application for final consolidation and company wide reporting.  This will probably be the most practical solution for a company where all users are not on the same network.

Real time reporting will be difficult to support unless you can get everyone on the same network so they can share the same BE.  This particular issue can be solved very easily if your company runs a Citrix server.  The Access app can run on Citrix and all users regardless of location will be connected to the same BE.

Any solution other than Access will take significantly more time to implement.  So the trade off is time vs perfection.
Most Valuable Expert 2013
Awarded 2012

Commented:
Hi,

imho I would either use PowerBI (which can access data from various sources) or SQL Server - Integration Services and Reporting Services.

Finally you will have to have a full running BI solution to Extract, Transform and Load your data into a central database(s) - and SQL Server has some very smoothly running Integration Services where you can build that solution.
I would not start using Access - not really able to run multiple users, parallel updates ...

Just my 2ct.
Rainer
Most Valuable Expert 2012
Top Expert 2014

Commented:
I would not start using Access - not really able to run multiple users, parallel updates ...
That's funny ... I was just working on a client's system where they have 25 or so concurrent users, all running on an Access only system. I guess I should let them know they can't do that???

I'm joking of course, but you're simply wrong. Access can easily support multiple users. I'm not sure what "parallel updates' means, so can't comment on that.
John TsioumprisSoftware & Systems Engineer

Commented:
100+ users on my previous job...and probably even more on the mother company

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial