Build a Database to Run Your Service Business
Welcome
Although the title of this course says "Work Order Seminar," it's much more than that. You will learn everything you need to build a database to run a service-oriented business using Microsoft Access.
This seminar came about because one of my customers had a need to track work orders for his business, managing maintenance for apartment complexes. All of my Access database lessons to date have been designed for more of a retail-type establishment (basic point of sale and inventory). So, I decided to build a database seminar tailored for service businesses. While many of the concepts are similar, the construction of the database is quite different.
Resources
This seminar has several main goals. You will track customers with multiple locations, generate work orders with unlimited categories, schedule employees and subcontractors, avoiding conflicts, track job costs, including materials and labor, invoice your customers, and calculate sales tax, design a comprehenive Search Form to find records, create printable reports for your business.
We will begin by planning our database, and determining all of the different tables, queries, forms, and reports that we'll need. Each of our customers can have multiple locations, and each location can have multiple units. Think of a company that manages different apartment complexes. The company is the client. Each apartment complex is a location. Each unit in that apartment can be tracked separately - with a complete history.
Then we'll create the Work Order Form so that we can track work orders for each customer, location, or even unit separately. The work orders can have an unlimited number of categories (plumbing, electrical, painting, etc.) plus a status, priority, separate contact information, and so on. We'll track whether each work order is billable, has been scheduled, and has been invoiced.
We'll generate a printable copy of the work order:
We will track Labor for each work order. Your workers can enter a start time and an end time, and the database will automatically calculate the number of hours (which you can edit if you want). You can enter a description, notes, and whether or not each bit of labor is taxable.
You can also enter Materials for each work order. You can type them in manually, or select from a table containing your commonly used products which will store unit price, cost, and other information. And, of course, you can track which items are taxable.
We develop a comprehensive Work Order List showing all of the work orders, their statuses, completion dates, and other information. You can filter this list based on multiple criteria including dates and invoicing status. We'll also create links to perform commonly-used filters, such as "show me all of the work orders that are completed but have not been invoiced yet."
We will learn how to schedule appointments for all of our workers, whether they're contractors or employees. You can select a worker, then the form will show you all of his upcoming appointments - so you don't schedule a conflict. You can click on the "Next Available Appointment" button to automatically select his next free time slot. Of course, if you do double-book a worker, the database will yell at you. And of course, we'll make printable schedules you can hand out to your workers.
We will create a comprehensive Search Form for our database, so you can search for records based on company name, location, first name, last name, or phone number. Using the techniques I will show you in class, you can search on ANY fields that you want to. Just add them to the form and update the code.
You will learn how to generate invoices with the click of one button. Once the data has all been entered into the work order form, just click the "Make Invoice" button and all of the information will be transferred to the invoice form.
Again, just click one button, and you can print the invoice. Now you're ready to fold it, put it in an envelope, and mail it on its way.
Now, this seminar does stand alone. You don't need any other resources to build the database that I build in this class. However, there is a good deal of VBA (Visual Basic for Applications) programming in this seminar. I will explain everything that I cover enough so that you can follow along, however it will help you tremendously to have a solid background in developing Access databases before taking this course. I would recommend taking at least my Basic and Intermediate Access courses before this one. See the pre-requisites listed in the box above.
Again, this seminar is perfect for anyone who wants to learn how to build a Microsoft Access database to run a service-oriented business. You will be able to track customers, work orders, scheduling, and more, when you're finished with this seminar. If you are interested in building a database for a retail business (customers, contacts, invoicing, products, inventory control, etc.) then you should take my normal Access classes.
This seminar is very long - over twelve (12) hours - but it's broken up into easily managed lessons of about 10 minutes each. You can sit down, watch a lesson, review the material, test the code out yourself, and experiment. Do a little bit each day. It's long, but it's comprehensive - you won't miss a single step as I've recorded everything from start to finish. Of course, if you have any questions about whether or not this seminar is for you, please contact me.
Pre-Requisites
This is a Developer-Level Seminar. There will be a lot of VBA. It is strongly recommended that you have completed my entire Access Beginner and Expert series. My Developer 1 class is highly recommended so you understand the basics of programming in VBA. If not, at least watch my free Intro to VBA video.
Version
I am using Access 2007 in this seminar, however the lessons are perfectly valid for all versions of Access from 2003 and later. It's currently 2022 and I just recently verified that everything in this seminar still works with Access 2019 and Office 365.
Enroll Today
Enroll now so that you can watch these lessons, learn with us, post questions, and more.
Questions?
Please post any questions or comments below.
