My name is Wyman, I am working as a human resources professional in Hong Kong with experience in reward management, servicing, HR analytics and system implementation. I spend a lot of time studying Excel and Access because they are transferable skills across any job functions and industries while many other HR skills are industry and system specific. I love designing Access Query in the way that users only need to double click to complete a regular task that originally takes hours of effort.
I was awarded Microsoft Community Contributor title in 2014 for my contribution to the Microsoft forum, Microsoft Most Valuable Professional title in 2015 and 2016 for my overall contribution to the community, and I am a certified Microsoft Office Specialist for Excel, Microsoft Office Specialist for Access.
History of Access-excel.tips
In 2013 October, I created my first blog (http://wymamwmwong.synology.me/wordpress/) that recorded everything I learned from Access VBA, Excel VBA, Peoplesoft HCM, Human Resources, because I wanted to build a knowledge base for my own reference. They were simple notes to remind myself of the stuff I learned. Then I had an idea to develop Human Resources focused Functions, Templates, Macro and share with all HR fellows.
In 2014 December, my blog crashed during my NAS upgrade due to compatibility issue with wordpress (luckily I can still access the contents at the back end), and I decided to start over in a new website in a new domain, migrating and rewriting the old contents.
Goal of this website
In this website, you will learn all the skills required for business analytics. I will share everything I learn in Excel, Access, VBA, SQL, statistics, SPSS, HRIS, employment ordinance, and hopefully financial analysis and project management.
I am trying to do is to build a a well categorized, 1000+ topics knowledge base for people to easily search what they need. I have visited a lot of websites that rank top in search engine, but I had difficulty searching things easily by topic.
Understanding the best solution
Every system has its strengths and weaknesses. While you can have a lot of choices to achieve the same tasks, you have to decide which is the most efficient way. Unlike other Excel websites, I only write the most efficient tutorial, that could be an Excel VBA solution, Access Query solution, Excel Form solution.
I have written a lot of custom VBA Functions not because I love VBA, but because I evaluated the efficiency among Access, Excel and VBA, and come to a conclusion that VBA is the best among the available choices. Excel worksheet Functions are powerful. If you are creative enough, you can always make a workaround by joining Excel Functions instead of writing VBA. However, the more Functions you join together, the higher risk you take for the exceptional cases especially when you Auto Fill the formula.
False belief 1: VBA is more complicated than Worksheet Formula
You cannot compare something unless you know both things. For many cases VBA is just a simple IF ELSE logic putting in VBA instead of in Excel formula.
False belief 2: Excel VBA solution is better than Access in terms of skill transfer
Please remember, Excel VBA is hard coded. If you don’t have any knowledge in VBA, you cannot maintain it when the guy who made the Macro leaves you.
For Access, it is easier to maintain because of its user friendly interface. Even if you don’t have any programming knowledge, you can still maintain it easily given a short training.
False belief 3: Excel is the best tool to do reporting
Basically all reporting systems that you generate reports from use SQL, not Excel. Access is powered by SQL, and more importantly it is probably the only SQL tool that you are allowed to install in your company computer if you are not in an IT role. Below are some advantages of using Access over Excel:
1) Excel recalculates the formula when you update a Cell value, which makes Excel extremely slow in large data set. Access only runs when all your data are ready
2) Excel has limit on number of rows of data, Access does not.
3) Excel requires you to spend hours of manual data conversion in order to create a regular report, in Access you just need to setup the conversion process once and then you can generate the report immediately by updating data source
4) You need VBA to create a user form (or write complicated formula) if you want to allow users to select criteria for the report, in Access you don’t need any VBA knowledge to create a powerful user form very quickly