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)

|