I am soon due to be given SQL access to our CRM system, which will allow me to automate some reporting processes. At present I use Access which I update with Excel imports.
As one example I have created a report which is used to assess sales team performance. To do this I manipulate the data using queries in Access and then link Excel to the final query. My queries have many steps which reference each other - this seemed to be the only way to make this work in Access; use a query to select/re-order some data before you can apply the next query to it, and so on.
I am playing around with some partial data in MySQL with the idea of completely re-creating this database (and others like it) and I have some questions:
Is it worth doing this, or should I simply use the SQL connections to automate the Access data update and continue to run the report from it? There are no problems with it at present, it is just tedious to update it manually.
My approach in MySQL so far is to use Views in place of the multi-layered queries I use in Access, as they seem to be the closest thing available. Is this appropriate/advisable? I will be making new reports using MySQL and would automatically approach it in this way but I'm completely unsure if this is suitable. Views seem easier to keep track of than subqueries etc.
Any help would be great.