|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Work Order Lessons Welcome to the Access Work Order Seminar. Total running time is 12.5 Hours. Lessons
Resources
Questions?Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you! Subscribe for UpdatesIf you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.
IntroIn this lesson, you will learn how to build a complete Microsoft Access database for managing a service-oriented business. We will cover how to track customers with multiple locations and units, create and manage service work orders, record labor and materials, handle scheduling for contractors and employees, process billing and invoices, and design search and reporting features. We will also discuss planning your tables, queries, forms, and reports, and include tips on working with VBA code in Access. This course uses Microsoft Access 2007 but is suitable for all modern versions.TranscriptWelcome to 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. 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, like 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 the profit and loss. We will schedule workers, whether those workers are contractors or employees, and of course we will handle all of 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. We will determine all the different tables, queries, forms, and reports that we are going to need. Next, we will design our customers, locations, and unit tables and forms. What does this mean? Basically, we will have a list of customers here. I can double-click to open up any one of these customers. Each customer can have multiple locations. This ABC Properties, for example, owns UX North Campus and UX South Campus. I can furthermore double-click on one of their campuses to 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, the Cam Lab, the 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 the service company that services large corporations with 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 in work orders for each of our customers, based on the location or even their 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. 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 put in the worker, the description, notes, and whatever other information you want. Materials work pretty 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, 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 a 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, 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 types 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 here to show all the open unscheduled, completed not-invoiced work orders, or print all the open work orders between the dates shown. 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 and click on the schedule button. I can then pick a worker, like JK Handyman Service. Down here at the bottom, I will see all of the upcoming appointments for JK Handyman Service, so I do not double-book him. You can click on the next available appointment button, and it will automatically fill in his next appointment time. Now I am defining appointment times as Monday through Friday from 9 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. Then that worker's schedule 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. Let's say LMN, if you are looking for LMNOP Corp, hit go, and that will then open up the customer's record. Let's say the West Village Apartments calls, and you do not know what company they belong to. Type in West Village or just West, and then hit go for the location. That will bring up the location, and 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 down here. You might have people associated with companies, locations, or 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 Smith; I will put SMI for last name and hit go. Now 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 that link. Here I can see the one work order that has not been billed yet. I can open it up by double-clicking on it, then come down and click on the make invoice button. All of the information from the work order - the labor, the hours, 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. 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. While the seminar does stand alone, and I will cover all the concepts in detail, it is recommended that you have a solid background in Microsoft Access before taking this class. You can find my beginner, intermediate, and advanced level Access classes on my website at AccessLearningZone.com. There will be a good amount of VBA programming in this seminar, and while I will go over everything that you need to know, having at least some VBA programming experience will help you understand this better. I will be using Microsoft Access 2007 in this class. However, the lessons are valid for every version of Access from 2000 up. There are a few minor differences in 2003 and earlier, but I will show those to you in the class. The sample database files for this course can be found online at AccessLearningZone.com/workorderseminar. If you are watching this course online or in my special video player software, you will see the student interaction forum window appear in a small browser window next to the videos. If you have any questions about the material covered in today's seminar, please feel free to post it in the student forum. QuizQ1. What is the primary focus of the Work Order Seminar?A. Building a database for a retail business B. Building a database for an e-commerce business C. Building a database for a service-oriented business D. Building a personal finance tracking database Q2. What key feature does the seminar's database include for handling customers? A. Tracking only a customer's basic contact info B. Tracking customers with multiple locations and units within each location C. Only handling single-location customers D. Limiting each customer to one contact Q3. What types of work can a work order include in this database? A. Only cleaning work B. Only electrical work C. Multiple categories, such as plumbing and electrical D. Only administrative work Q4. How does the database handle labor and material tracking for work orders? A. Tracks only material B. Tracks only labor C. Does not track either labor or materials D. Tracks both labor and materials, including quantities and costs Q5. What does the work order form allow you to track? A. Only customer and description B. Status, priority, contact info, and if it is billable, scheduled, or invoiced C. Only invoice details D. Only materials used Q6. How can users create a new work order in the system? A. Only by entering all details manually B. By copying another work order C. By selecting a customer, location, or unit and using a button to auto-fill related details D. By importing from Excel only Q7. What feature is used to help avoid double-booking workers when scheduling appointments? A. Worker scheduling history B. System locks the schedule C. Upcoming appointments list and next available appointment button D. Color coding by worker Q8. What search options are available in the database? A. Only by customer name B. By company name, location, first name, last name, or phone number C. By zip code only D. By work order date only Q9. How does the system facilitate the invoicing process? A. Users must manually copy data to invoices B. Information from the work order is automatically transferred to the invoice C. Invoices are created in Excel D. There is no invoicing feature Q10. What programming language will be used for additional automation and logic in the course? A. JavaScript B. Python C. VBA (Visual Basic for Applications) D. PHP Q11. What version of Microsoft Access is used in the seminar? A. Access 97 B. Access 2007 C. Access XP D. Access 2019 only Q12. Is prior knowledge of VBA required to benefit from this seminar? A. No background is helpful B. Yes, expert level required C. Some VBA experience is helpful but not required D. No VBA is used at all Q13. What resources are provided to supplement the seminar? A. Free Access hardware B. Printed workbooks only C. Sample database files available online D. Access add-in installation Q14. What is a recommended prerequisite before taking this seminar? A. Background in Microsoft Word B. Previous experience with Microsoft Access C. Web design skills D. Bookkeeping knowledge Q15. How does the seminar handle different versions of Access? A. Only works with Access 2007 B. Teaches only for Access 97 C. Covers differences and is valid for Access 2000 and later D. Uses web-based Access only Answers: 1-C; 2-B; 3-C; 4-D; 5-B; 6-C; 7-C; 8-B; 9-B; 10-C; 11-B; 12-C; 13-C; 14-B; 15-C 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. SummaryToday's video from Access Learning Zone covers the Microsoft Access Work Order Seminar. I am Richard Rost, your instructor, and in this seminar, our aim is to develop a comprehensive database designed to manage every aspect of a service-oriented business.While the main focus is work orders, the database we build will go far beyond just that. Many previous Access courses have centered on inventory and retail businesses, but today, our discussion will focus entirely on the unique requirements for companies that offer services rather than products. We will begin by setting up the essential components of our database to handle customers who might operate across multiple locations, such as various office buildings or apartment complexes. Within each location, we will also keep track of individual units or rooms. This structure is particularly useful for service providers working with large organizations, landlords, or property managers with multiple sites and many individual spaces to maintain. As we proceed, we will build a system for managing work orders. This involves recording all the services requested by your customers, assigning them to the correct location or even a specific unit within that location, and tracking the status and priority of each work order. Each work order will have its own notes, contact information, and be marked as billable or not, along with statuses indicating if it has been scheduled or invoiced. Work orders can also belong to one or more categories, allowing you to flag jobs based on the kinds of work required, such as plumbing or electrical. For each work order, we will track labor and materials. You will be able to identify the worker, their hours, and any materials used on the job, including quantities, prices, and costs. This allows you to calculate your sale price, costs, and profits for every job. Adding new products to the materials list will be streamlined, and all totals will update instantly. Scheduling is another important section. You will learn how to assign work orders to either contractors or employees, view upcoming appointments to avoid double-booking, and let the system suggest the next available appointment slot based on predefined business hours. The system can print out schedules for either all workers or specific individuals for any date range. To navigate the system efficiently, we will also create a robust search feature. This allows you to quickly find customers, locations, units, and people using partial names or other information such as phone numbers. Everything in the system is interconnected; looking up a location will show you the parent customer, and from any unit you can get back to its location or customer. We will assemble a detailed work order list that can be filtered by criteria such as invoice status or date range. You will also learn how to use triple-state check boxes and shortcut links to quickly find relevant work orders, such as those that are open and unscheduled, completed but not yet invoiced, or to print all work orders within specified dates. For the billing process, we will cover how to manage completed work orders that are ready to be invoiced. With a single step, all details - labor, materials, and hours - will transfer from the work order to the invoice automatically. You can then print the invoice and view a full listing of all invoices within the system. The seminar is detailed and stands on its own, but I recommend having a solid understanding of Microsoft Access before jumping in. If you are new to Access, I also offer beginner, intermediate, and advanced courses, all available on my website. Throughout this seminar, there will be a significant amount of VBA programming discussed. While I guide you through the necessary steps, some prior experience with VBA will help you follow along more easily. This course is demonstrated in Access 2007, but everything shown applies to Access 2000 and later versions. I will point out any small differences for earlier versions where needed. You can find the sample database files for this seminar on my website. If you have questions about the material, you are welcome to participate in the student forum, where you can discuss topics with me and fellow students. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListPlanning the service business database structureDesigning customer, location, and unit tables Creating forms for customers, locations, and units Linking customers to multiple locations and units Developing the work order entry form Tracking work order status and priority Capturing work order contact information Associating work orders with categories Recording labor details for work orders Recording material usage for work orders Building a products database for materials Automatically calculating work order totals, costs, and profits Creating work orders directly from customer, location, or unit records Developing a comprehensive work order list with filtering Using triple-state checkboxes for invoicing and status fields Filtering work orders between specific dates Printing individual and batches of work orders Implementing appointment scheduling for workers Preventing double-booking with worker appointment review Auto-filling next available worker appointment times Printing worker schedules for defined periods Building a search form to find customers, locations, and people Searching for people by various criteria Processing billing and invoicing for work orders Transferring work order details to invoices automatically Printing invoices and generating invoice lists |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||
| Keywords: Access Work Order Seminar PermaLink How To Build a Complete Service Business Work Order Tracking and Billing Database in Microsoft Access |
