Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 

Access Work Order Seminar
Build a Database to Run Your Service Business

 

 

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.

Click on the videos below for more information on exactly what's covered in this seminar. The first video is a preview covering the general outline of the seminar. The second video is an in-depth lesson summary showing each lesson's topics.

Seminar Preview Lesson Summary
AccessLearningZone.com AccessLearningZone.com
10 minutes 15 minutes

   

After you watch the preview videos above,
click here to download the database we build in this
seminar so you can see everything that is included.

 


 

 
Seminars - Access Work Orders
Description: Learn how to build a database to run a service business.
Versions: I will use Access 2007, however most of the lessons are valid for all versions of Access back to 2000. I will show any differences between 2007 and 2003.
Pre-Requisites: Access 101 - 104 a must
Access 201 - 207 highly recommended
Access 301 - 329 very helpful
Running Time: 12 hours, 23 minutes
Cost: $219 - Order multiple courses to receive a discount up to 50% off
Includes a sample, customizable, working database, as built in class.

 

This seminar has several main goals. You will:

1. Track customers with multiple locations
2. Generate work orders with unlimited categories
3. Schedule employees and subcontractors, avoiding conflicts
4. Track job costs, including materials and labor
5. Invoice your customers, and calculate sales tax
6. Design a comprehenive Search Form
to find records
7. 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. 

All of the sample database files for this seminar are available on my Web site. They are available in Access 2007 and 2000 formats (Access XP and 2003 users can download the 2000 version which is compatible). You can download the sample databases here.

This seminar is available to view online in the Amicron Theater. You can click here to watch the first two lessons absolutely free.

Of course, if you have any questions about whether or not this seminar is for you, please contact me.
 

 

Access Work Order Seminar Outline

0. Introduction (10:49)

1. Plan Database (11:33)
Tables
Relationships
Reports

2. Customer Tables (11:58)
CustomerT
LocationT
UnitT

3. Customer Form (11:20)
Make Customer Form
Locations List Box
SQL RowSource
Custom WHERE Condition
Forms!CustomerF!CustomerID
OnCurrent Event
LocationList.Requery

4. Location Form 1 (12:27)
Build Location Form
Customer Combo Box
Open Location From CustomerF
DoCmd.OpenForm Code
Where Condition
OnDblClick Event

5. Location Form 2 (9:03)
Add New Location
Open Form to Add Records
Form Field Default Value
Tab Stop Property
Requery Across Forms
On Error Resume Next

6. Unit Form 1 (12:47)
Build Unit Form

7. Unit Form 2 (9:19)
Limit List of Locations
Change RowSource in VBA
Notes

8. Using Subforms (10:23)
Creating Location SubForm
Creating Unit SubForm
Continuous Forms
Form Header/Footer

9. Customer List (11:00)
Build Customer List Form
Open Customer Button
New Customer Button

10. Work Order Form 1 (7:33)
Build Work Order Table
Design Work Order Form

11. Work Order Form 2 (11:14)
Dynamic Combo Boxes
Locations by Customer
Units by Location
Enabled Property
DropDown Method
SetFocus Method

12. Work Order Form 3 (11:58)
Blanking Combo Boxes
OnCurrent Event
Enable/Disable if Needed
Requery Combo Boxes Again

13. Work Order Form 4 (9:22)
DLOOKUP Name & Address Info
Dim Variable

14. Work Order Form 5 (12:17)
NZ Function
Dealing with NULL problems
Double-click to Open Forms
DoCmd.OpenForm

15. Status, Priority (12:52)
Status Table
Sorted Status Query
Priority Table
Sorted Priority Query
Status, Priority Combo Boxes
Default Combo Values
Default Requested Date

16. Create Work Order (12:57)
Button on Customer Form
Button on Location Form
Button on Unit Form

17. Work Order Query (6:35)
Master Work Order Query
Relationships
Outer Joins

18. Work Order List 1 (18:57)
List of Work Orders
Listbox on Customer Form
Listbox on Location Form
Listbox on Unit Form
Limit to Current Record
Double-click Open Event
OnCurrent Event Updated

19. Work Order List 2 (10:26)
Requery Work Order List
On Error Resume Next
Triple State Check Box
Show Closed Work Orders
Show Open Work Orders
Show Both Closed and Open

20. Work Order List Form (19:48)
Master Work Order List
Show Closed Work Orders
Change Sort by Column Headers
Main Menu Form

21. Labor Form 1 (9:01)
Form to Track Labor Items
Continuous Forms

22. Labor Form 2 (8:53)
Calculate Billable Hours
Rounding Time Values
SUM Total
Page Footer v. Form Footer
DateDiff Function

23. Labor Form 3 (9:41)
Recalculate Hours
Combo Box for Worker

24. Labor Form 4 (14:06)
Modal, Popup Form
Button to Open Labor Form
Calculate Hours on Work Order

25. Labor Form 5 (14:29)
Combo Box Default Worker
Minimum Billable Hours
End Time Earlier than Start Time

26. Materials Form 1 (9:39)
Table, Form for Materials
Calculate Line Totals

27. Materials Form 2 (9:04)
Product Combo Box
Add Product to Materials Form
Column() Values
DLOOKUP

28. Materials Form 3 (8:36)
Calculated Footer Values
Values on Work Order Form
Grey Out Calculated Values

29. Categories (11:42)
CategoryT
Category Junction Table
Many to Many Relationship
Category Subform on Work Order

30. Scheduling 1 (14:37)
Delete Scheduled Date Field
Change to IsScheduled
Schedule Table and Form

31. Scheduling 2 (12:27)
Filter Boxes
Dynamic Form Requery
SQL Statements for Recordsource

32. Scheduling 3 (11:00)
Filter Boxes for Dates

33. Scheduling 4 (16:05)
Schedule Button for Work Order
Mark as Scheduled
MsgBox vbYesNoCancel
Appt Conflict Resolution, Part 1

34. Scheduling 5 (16:37)
Appt Conflict Resolution, Part 2
Upcoming Appointment Preview List

35. Scheduling 6 (19:44)
Next Available Appt Time
Loop to Get Next Appt Time/Date

36. Fix Work Order List (12:32)
DateScheduled Now Missing
Add Show Scheduled Box
Link to Show Open, Unscheduled

37. Inactive Units (9:36)
IsActive Field
Show Active / Inactive Units

38. Search Form 1 (16:04)
Company Name Search
Location Name Search
Person Name Search Part 1
Union Query

39. Search Form 2 (11:51)
Search for Name Anywhere in DB
Custom VBA and SQL Code for Listbox

40. Search Form 3 (9:15)
Open Corresponding Form
Location, Customer, or Unit

41. Search Form 4 (7:06)
Add Phone Number Field to Search

42. Billing 1 (16:15)
MyCompanyT
Default Hourly Rate for All
Default Hourly Rate Per Customer
Add Billable, Invoiced to WO List
Show Completed, Not Invoiced Yet

43. Billing 2 (10:47)
Invoice Table
Invoice Detail Table
Billing Decisions

44. Billing 3 (13:49)
Make Invoice Button
Check to see if Billable
Check for labor or materials
Create InvoiceT record
Recordsets
Create a Recordset
AddNew
Set Field Values

45. Billing 4 (14:29)
Add Line Items to Invoice
Recordset to Loop through Materials
Add Materials to Invoice

46. Billing 5 (8:46)
Add One Line Item for Labor

47. Billing 6 (12:12)
Invoice Detail Form
Invoice Form
Show Invoice Button
Change Caption Dynamically
OnCurrent Event

48. Sales Tax 1 (13:30)
DefaultTaxRate
Adding TaxRate to Invoice
Add IsTaxable to Materials
Taxable Labor

49. Sales Tax 2 (7:27)
Add SalesTax to VBA Invoicing Code

50. Sales Tax 3 (14:57)
TaxableTotal
NonTaxableTotal
IIF Function
Form Footer Calculations
Order Total

51. Order List Form (18:46)
Order List Query
Aggregate Query
Show Totals
Group By
Sum Function

52. Update Status (12:16)
Update the Status Combo
On Error Resume Next
On Error Goto 0

53. Work Order Report 1 (16:53)
Work Order Report Query
Outer v Inner Joins
Work Order Report
Work Order Category Subreport

54. Work Order Report 2 (15:51)
Labor Subreport

55. Work Order Report 3 (25:26)
Materials Subreport
Button to Print 1 Work Order
Limit Work Order List by Dates
Print Batch of Work Orders

56. Printable Invoice (21:49)
Master Invoice Query
Report Grouping
Force New Page
Sum Totals
Printing Invoices

57. Printable Schedules (20:17)
Schedule for All Workers
Schedule for One Worker
Schedules Between Two Dates

58. Review (3:25)

 


 

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP