For the past 1 year plus, i have been forced by the monthly routine task to come out with something that can ease my work and reduce time spent as well as minimizing errors. I have learn a lesson today.
Macro in excel will not be as good as Ms Access. Ms Access is systematic database and can be incomparable to excel. Excel is convenient in terms of flexibility in formatting but there's no control whether the data entered is correct. to edit an error in Excel can be time consuming and always keep me lost!!! i am not a careful person when it comes to figures, you can imagine the time to warm up, edit, and recheck.
Scenario #1 (this is real experience when i had to deal with bunch of worker salary, and i need to distribute the salary to different project category. Meaning one worker salary can be distributed across project sites. and the number of sites per person can be like 20++). Access works perfectly, i just need to assign the staff to to different sites, and i can compute the salary ratio with ease.
I can do this with Excel, and i need to drag and drag, many manual work involved.
Scenario #2 (real too. I have to insert many conditions in to set of plan production data and output must be in accordance to the day limit. Conditions like, overall monthly limit, has to be able to start in same month and finish in same month, bla..., bla, customized monthly conditions... totally unpredictable. I did consider Excel Macro at first due to the unpredictable conditions for the past 1 year. I thought it could help, but editing is just too time consuming .. therefore, spent 1 weekend to solve the problem using Ms Access with defined constant and editing will be easier for my future work....
I'm so glad.. that i accomplish this with help of Ms Access, something i should have done earlier.
My lesson learnt: Ms Access is good for huge data (effort need to setup the relationship of tables), Ms Excel + Macro will help if dataset is minimal (relationship can be ignored).
