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.



Once I discovered MySQL, Excel was relegated to being just a fancy calculator. It will take you some time to understand SQL, but in the long run, it will probably be well worth it. If you end up with performance problems, you might need to avoid Views.

  • Thanks Rick. I have read about the performance issues around views, but this is not a large report/dataset so I should be ok here. – agwah Jan 27 '17 at 8:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.