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.

Subscribe to Access Work Order Seminar
Get notifications when this page is updated

Intro

In this video, we'll talk about building a complete service business management database in Microsoft Access, focusing on tracking customers with multiple locations and units, creating and managing work orders, handling labor and materials, scheduling workers, and generating invoices. We'll design tables and forms for customers, locations, and units, develop a work order system with categories and profit tracking, create search tools, set up appointment scheduling, and learn how to process billing. This seminar is ideal for anyone looking to run a service-oriented business efficiently using Microsoft Access.

Transcript

Introducing the Microsoft Access Work Order Seminar brought to you by AccessLearningZone.com. I am your instructor Richard Rost.

While the title of this seminar is the Work Order Seminar, it is much more than that. Our goal today is to build a complete database to run an entire service-oriented business. Many of my previous database classes have been designed for retail-type businesses, so in today's seminar I am going to show you how to build a database to run a service business.

Today's seminar will cover tracking customers. Those customers might have multiple locations, such as different office buildings, and we will track the different units inside each location. For example, if you are dealing with an apartment complex, the customer might have multiple apartments, and you can track each unit inside each apartment complex.

We will create service work orders. We will track labor and materials for each work order, as well as the costs associated with your labor and materials, so you can track profit and loss. We will schedule workers, whether those workers are contractors or employees, and of course we will handle all the invoicing and billing. As you can see, we cover pretty much everything that a small service-oriented business needs to run efficiently.

We will begin by planning our database and determining all the different tables, queries, forms, and reports that we are going to need.

Next, we will design our customers, locations, and units tables and forms. What does this mean? Basically, we will have a list of our customers. I can double-click to open any one of these customers, and now each customer can have multiple locations. This ABC property, for example, owns UX North Campus and UX South Campus. I can double-click on one of their campuses, which will open up that location, and each location can have its own contact name, phone number, and address. You can see all the different units inside that location, such as Cam Lab, Physics Lab, etc., based on room numbers. Open up one of these, and now you can see the details on that specific unit.

This is great for a service company that services large corporations that have multiple offices, or apartment complexes, or any kind of situation where you have customers with multiple addresses or locations.

Then we will create the work order form so we can enter work orders for each of our customers, based on the location or even the unit. Here is our work order form. You can see it tracks the customer, the location, and the unit. You can have a status and a priority, as well as descriptions and notes. Each work order can have its own contact information. We will track whether or not it is billable, whether it has been scheduled and invoiced. A work order can fall into multiple categories, so we can track what types of work are necessary for a specific job. This work order, for example, requires plumbing and electrical work, and you can have as many categories as you want.

We will track labor and materials, and of course, be able to print the work order. There is the printed version of the work order. Each work order will track labor and materials so we can generate a total sale price, cost, and profit for each job.

Tracking labor is easy. Just click on the labor button. The labor form appears. You can track the start time and the end time. It will calculate the number of hours worked. You can enter the worker, the description, notes, and any other information you want.

Materials work in much the same way. You can track the product, the quantity, the unit price, and the unit cost. The system will calculate the totals for you. If you want to add a new product, we will have a database of products you can just pick from and then add the product. It will automatically be added to your materials tracking, and your totals will be updated when you return to the work order form.

When it is time to create a new work order, all you have to do is open up a customer, and you can create the new work order based on the customer, or open up a location and create the work order based on the location, or even based on the unit. When I click on the create work order button, all of that information is filled in for me automatically.

We will develop a comprehensive work order list showing all of our work orders. On this list, of course, we will be able to double-click on a work order to open it. We will learn how to use triple state check boxes, so I can come up here and say show me all of the invoiced work orders, or all of the not invoiced work orders, or all of them. We can add those kinds of check box fields to any of the columns.

We will be able to filter the list between two dates; for example, if I want to see all the work orders between 4.5 and 4.6, we can do that. We will make some shortcut links over here to show all the open unscheduled, completed not invoiced work orders, or print all the open work orders between the dates shown. And there is our printed work order.

We will learn how to schedule appointments for all of our work orders, whether for contractors or for employees. From my work order list, I can say show me all of the open unscheduled work orders, open one of them up, and then come down here and click on the schedule button. I can then pick a worker, like JK handyman service. Down here on the bottom I will see all of the upcoming appointments for JK handyman service, so I do not double-book him somewhere.

You will like this: I can click on the next available appointment button, and it will automatically fill in his next appointment time. I have defined appointment times as Monday through Friday from 9 a.m. to 4 p.m. It is currently 4.21 at 5:30 p.m., so his next available appointment will be tomorrow 4.22 at 9 a.m., and the system can figure this out.

Then you can print the schedule, either for all workers between two dates, or just the current worker. That worker's schedule then prints out.

We will make a comprehensive search form for our database, so we can look customers up based on different criteria. You can search by company name by typing in any part of the company name. For example, if you are looking for Element OP Corp, type LMM, hit go, and that will open up the customer's record.

Suppose the West Village Apartments calls and you do not know what company they belong to. Just type in West Village or just West, and then hit go for the location, and that will bring up the location. Here we can see they are part of the XYZ Apartments Company. I can double-click on this field to go back to their customer record. As you can see, everything is related to everything else.

Likewise, we have a long list of all the different people in the database. You might have people associated with companies, locations, and units. You can search by first name, last name, or phone number. For example, if I put a W in the first name and hit go, it finds me all the first names that contain a W. I could further filter the list by looking for Smiths; I will put SMI for last name and hit go. There I find all the first names that contain a W and have last names with SMI in them. You can do the same thing with phone number.

We will learn how to process the billing for our work orders. From my work order list, I can say show me all the completed work orders that are not invoiced yet by clicking on that link. Here, I can see the one work order that has not been billed yet. I can open it by double-clicking on it, and then come down and click on the make invoice button.

All of the information from the work order, the labor, the hours, and the materials are all transferred over to the invoice automatically with a little bit of VB code. Then I can click on the print invoice button, and there is my invoice.

Likewise, from my main menu, I can click on the invoice list button and generate a listing of all the different invoices in the system.

So that, in a nutshell, is what is covered in this database. As you can see, the seminar is quite comprehensive.

For more information on the Access Work Order Seminar, visit my website at AccessLearningZone.com and look for the Work Order Seminar. You will find a complete listing of all the topics covered and a sample database you can download.

Quiz

Q1. What is the main focus of the Microsoft Access Work Order Seminar?
A. Creating a database for a retail store
B. Building a database to run a service-oriented business
C. Designing a website for small businesses
D. Learning advanced Excel techniques

Q2. How does the seminar address businesses with customers having multiple locations?
A. By creating a single address field for each customer
B. By tracking different units inside each location and allowing multiple locations per customer
C. By only tracking customers' main headquarters
D. By using spreadsheets for each location

Q3. Which of the following features will the database allow you to track for each work order?
A. Only materials used
B. Only time worked
C. Labor, materials, costs, and profit/loss for each job
D. Only work order number

Q4. What can you track about each worker scheduled for a work order?
A. Only their name
B. Start and end times, worker identity, and number of hours worked
C. Only their phone number
D. Only their hourly wage

Q5. What will the materials tracking system allow you to do?
A. Track product, quantity, unit price, and unit cost, with totals calculated automatically
B. Only track the names of products used on jobs
C. Track materials using paper forms only
D. Add materials without calculating total costs

Q6. What is one benefit of creating work orders directly from customers, locations, or units?
A. You must enter all information manually each time
B. Information fills in automatically, reducing data entry
C. You cannot track details for each unit
D. Only one work order can be created per customer

Q7. What purpose do triple state check boxes serve in the work order list?
A. To allow users to select between invoice statuses and filter the work order list
B. To enable printing for each column
C. Just to look decorative
D. To sort work orders by customer

Q8. How can users prevent double-booking workers when scheduling appointments?
A. By randomly assigning work orders
B. By reviewing upcoming appointment lists for each worker before scheduling
C. By not tracking appointments at all
D. By using sticky notes

Q9. What does the seminar teach about searching the database?
A. You can only search by full company name
B. Comprehensive search by company name, location, unit, first or last name, or phone number
C. Searching is not covered
D. Only phone numbers can be searched

Q10. How are invoices generated in the seminar's database system?
A. They must be created manually from scratch
B. All work order information is transferred automatically to the invoice using VB code
C. Invoices are written on paper only
D. Users must type all materials into the invoice separately

Q11. Which report can be printed for workers in the seminar database?
A. Only sales reports
B. Schedules for all workers or just the current worker between selected dates
C. Only customer billing statements
D. Annual tax summary

Q12. What is a highlighted feature regarding adding new materials to a work order?
A. You must close the work order to add materials
B. You can add a product from the product database and totals update automatically
C. Materials cannot be tracked in the database
D. You may only use a fixed list of pre-approved materials

Q13. What is the intended audience for the seminar database solution?
A. Retail store owners
B. Service-oriented businesses with complex tracking needs
C. School administrators
D. Personal finance enthusiasts

Q14. If you need to see all completed but not invoiced work orders, the system allows you to:
A. Search manually through all records
B. Click a predefined link to filter the work order list accordingly
C. Export all data to Excel for analysis
D. You cannot isolate these work orders

Answers: 1-B; 2-B; 3-C; 4-B; 5-A; 6-B; 7-A; 8-B; 9-B; 10-B; 11-B; 12-B; 13-B; 14-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone introduces the Microsoft Access Work Order Seminar, an in-depth course designed to help you build a robust database for managing a service-oriented business. While the seminar is focused on work orders, it extends much further, covering all aspects needed to efficiently run a service business.

In the past, many of my courses have targeted retail environments. However, in this seminar, I show you how to structure a database for businesses that provide services. We start by focusing on tracking customers, especially those who may have multiple locations. For example, if your client is an apartment complex, the database allows you to manage multiple units within each location.

We cover the process of creating service work orders. You will learn how to record labor and materials for each job, as well as calculate the associated costs, helping you determine profitability. Scheduling is an important part of the seminar, so you will see how to manage both employee and contractor schedules. You will also learn how to handle invoicing and billing, ensuring that your business operations run smoothly from start to finish.

The seminar starts with the planning phase, outlining the necessary tables, queries, forms, and reports. Once the structure is clear, we design tables and forms for customers, locations, and units. Customers can have several different locations, and each location can have unique contact information and multiple units. This structure is ideal for businesses that service organizations with numerous offices or apartment complexes with many units.

Work orders are at the core of the seminar. You will learn how to create work orders for each customer, tied to specific locations or units. Each work order tracks key details such as status, priority, descriptions, and relevant notes. You will be able to indicate whether the job is billable, has been scheduled, and if it has been invoiced. The database also allows categorization of work orders by the type of service required, such as plumbing or electrical work, and you can assign as many categories as needed to any job.

When it comes to tracking labor and materials, the seminar demonstrates easy methods for logging time spent on jobs and materials used. The labor tracking form lets you record the worker's name, start and end times, and a summary of the work performed. The material tracking works similarly, allowing you to select products from your inventory, record quantities, pricing, and costs, automating the calculations for you.

Creating new work orders is streamlined—you can start from a customer, a location, or a unit, and the system carries over relevant information automatically. You will also develop a comprehensive work order list, complete with filters and shortcuts to help you find and manage your jobs easily. For instance, filters can show work orders based on date range, invoicing status, or scheduling needs. Working with triple state checkboxes, you can quickly toggle views for invoiced or non-invoiced work orders.

A major feature in this seminar is the scheduling system for appointments. You learn how to assign workers to jobs, check worker availability, and prevent double-bookings. The system helps find the next available time slot for each worker, based on predefined working hours. You can then print schedules for individual workers or for your whole team over a selected period.

Efficient search capabilities are also covered. The seminar teaches you how to look up customers, locations, and people in the system using flexible search criteria, such as partial company names or phone numbers. The interconnected design means you can quickly navigate from a location to its customer, or from a unit to its related contact details.

The billing process ties everything together. The course shows you how to find all completed but unbilled work orders and convert them into invoices. The system automatically carries over all details, including labor, hours, and materials, streamlining the creation and printing of invoices. A dedicated invoice list lets you review and manage all invoices generated.

In summary, the Access Work Order Seminar is a comprehensive guide to building a full-featured database for service businesses. It handles everything from customer management and appointment scheduling to work order processing, materials and labor tracking, and billing.

To watch the complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below.

Live long and prosper, my friends.

Topic List

Planning the database structure for service businesses
Designing customers, locations, and units tables
Creating forms for customers, locations, and units
Tracking customers with multiple locations
Work order form design and setup
Assigning work orders to customers, locations, and units
Managing work order status, priority, and categories
Tracking labor details and calculating hours worked
Logging labor costs for work orders
Building a labor tracking form
Tracking materials used in work orders
Selecting materials from a product database
Calculating material costs and totals
Comprehensive work order list creation
Filtering work order lists by date and status
Implementing triple-state checkboxes for filters
Creating shortcuts for common work order views
Printing work orders and work order lists
Scheduling appointments for workers
Viewing and avoiding conflicting worker appointments
Automatic assignment of next available appointment slots
Printing worker schedules for defined date ranges
Building a comprehensive customer search form
Searching customers, locations, and units by multiple criteria
Searching people by name or phone number
Processing billing for completed work orders
Transferring work order data to invoices with VB code
Generating and printing invoices
Creating and managing invoice lists