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

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.
D_wathiAsked:
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:
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

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
D_wathiAuthor 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 EngineerCommented:
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...
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You might look into PowerBI:

https://powerbi.microsoft.com/en-us
PatHartmanCommented:
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.
Rainer JeschorCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 EngineerCommented:
100+ users on my previous job...and probably even more on the mother company
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 SharePoint

From novice to tech pro — start learning today.