These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Friday, December 11, 2009

Jeremy Heckaman - Viking Pest And Plant Customer/Contract DataBase and Scheduler

Viking Pest and Plant, LLC is a pest control company that offers additional services, such as plant and tree disease diagnosis and treatment, tree pruning and trimming and landscape consulting. The company is owned and operated by my brother, primarily in the St. George, Utah metropolitan area. He principally operates the business out of an office in his home, where he keeps files and customer information, along with any other company documents and information. Customers will sign a contract which stipulates the services requested and the frequency of such services. Based on this information, services are scheduled and payment is collected at that time. Given the limited office space, and proximity to small children, my brother experiences some difficulty keeping his records organized and secured from potential destruction. In addition, scheduling out services cost effectively can be a time consuming and error prone process. Currently he needs to sort through a stack of paper contracts and write down a paper schedule for all customers. This process needs to be repeated periodically consuming unnecessary labor hours. The manual process is expected to become unmanageable as his customer base grows beyond about 250 records. Automation of these tasks will reduce labor needs and enhance his ability to track his customer activity and service requirements.

For my project, I designed a data base in Excel, using primarily VBA forms, which will allow my brother to input customer data, link multiple contracts to a customer record and link multiple service call histories to those contracts. All of this information is input through user friendly forms, and can be recalled and edited. In addition, the data base has a scheduling function that allows my brother to see upcoming services for the next week or month. This allows for safe and secure storage of all files in a safe, central location with easier and more accurate record changes to that information. In addition, it reduces scheduling time and difficulties because my brother only needs to plan a few weeks in advance, instead of planning out many months in advance or periodically reviewing all contracts. This program will also allow my brother to accurately record and report regulatory data, e.g., pesticide usage, required by State and Federal agencies, e.g., The EPA. Lastly, it provides a platform for additional functionality that I plan to program in the future, further increasing its value to my brother’s company.

Click Here for the Complete Report

Click Here to View the DataBase

No comments:

Post a Comment

Blog Archive