|
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)
|
