| Access 201 |
1. Introduction
2. Relational Database (RDB) Concepts
Why are RDBs important
One-to-one relationships
One-to-many relationships
Many-to-one relationships
Many-to-many relationships
Normalization
3. Relational Contacts Table
Making our contact management relational
Inserting a CustomerID in ContactT
Removing duplicated fields
What is a Foreign Key?
4. Contact Query
Manually creating query relationships
Deleting relationships
String concatenation
Dealing with duplicated fields
Correcting our contact report
Manual formatting of the phone field
5. Contact Form
Constructing a contact form
Using a combo box to pick the customer
Manually editing combo box properties
6. Miscellaneous
Add contact form button to main menu
Global relationships between tables
Creating and deleting links between fields
7. Review
|
| Access 202 |
0. Introduction
1. Contact Button
Command button to open contacts for current customer
Find specific data to display
Viewing filtered results
2. Setting Values Across Two Forms
Using Forms!FormName!Field notation
Get CustomerID for Combo Box on ContactF
Setting the Tab Stop property
Skipping fields in the Tab Order
3. Sorted Customer Combo
Creating a sorted query with customer info
Manually editing the Row Source property
Modifying the Column Count property
Column Widths property
Column Heads property
SQL Primer
Modifying the row source with an SQL statement
List Rows and List Width properties
Lesson 4. Callbacks
Adding callback and date to table/form
Creating a callback query
Creating a callback form
Buttons on the form to open customer/contact
Lesson 5. Contacts Subform
Learning about subforms
Creating the contact subform
Using the subform control and wizard
Format > Send to Back
Hiding text boxes with the Visible property
Lesson 6. Miscellaneous
Editing our Main Menu
Replacing button images with text
Coloring text on buttons
Phone AutoDialer
Lesson 7. Review
|
| Access 203 |
0. Introduction
1. Creating the Extended Customer Table
Building the table
Creating fields
Hyperlink field for Web Site
2. Using Option Groups
Option Groups
Option Buttons
List Box
Creating a Customer Type Table
3. Toggle Buttons
Toggle Buttons
Making a Check Box > Change To Toggle Button
Creating a Toggle Button manually
Changing Toggle Button Properties
Changing Option Group Colors
&Caption trick
4. ActiveX Calendar
Combo Box for Sales Rep
Combo Box for Service Tech
Picture OLE Field
Birthday ActiveX Control
5. Tab Control
Linking CustomerF to CustExtF
Tab Control
6. Object Controls
Bound Object
Pasting v. Linking Bound Objects
Unbound Object
Image Control
Boxes & Lines
Page Breaks
Force a 1-to-1 Relationship
|
| Access 204 |
0. Introduction
1. Contact Letter Query
Build a query that shows only the current contact
Getting the ContactID from the form into our query
2. Contact Letter Report
Designing our Contact Letter Report
Placing the Print As Letter button
Dirty records - data needs to be saved
3. Macro Basics
Constructing basic macros
Beep macro action
OpenForm macro action
OpenTable macro action
MsgBox macro action
On Dbl Click event
OpenForm WHERE condition
4. Refresh Macro
RunCommand, Refresh macro action
OpenReport macro action
Creating a new button using the macro
5. Formatting the Letter
Creating new name and address fields
String concatenation in a new text box
Formatting as a long date
Format > Vertical Spacing > Increase
Report width, page width, margins
Placing a logo in the page header
Making a multi-line label
|
| Access 205 |
1. Past Due Customers
Add fields to CustomerT: AmountDue, AmountDueDate
Create a past due query
Wilcard Field (*) in queries
2. Letter Table & Report
Copy letter to work with new query for collection letters
Create a Letter table to hold your letter text
Create a Letter form
Bringing data from an unbound form field into a report
3. More Letter Report
Force new page after section in reports
Create a greeting line
Place AmountDue and AmountDueDate in letter report
The difference between DefaultValue and ControlSource
Place report date in unbound text box on LetterF
4. Generic Letter Report
Making the letter writer generic so we can send to anyone
Add IncludeInMailing field to CustomerT
Create a Mailing List query
Create a generic letter report
Form design tricks with rectangles
Create a button to open generic letter report
Add IncludeInMailing field to customer list form
Add IncludeInMailing field to customer form
Update main menu with button to letter form
5. Post Cards
Create a blank report to use for post cards
Columns in reports
Sizing the post cards properly
Copying data fields and labels from other reports
|
| Access 206 |
Lesson 1. Setting up Task Tables
Create TaskT table
Create PriorityT table
Create CategoryT table
Create RecurringT table
Lesson 2. Setting up the Task Form
Create a TaskF simple form
Create combo boxes for Priority, Category, Recurring
Modify the Row Source for each to Sort the boxes
Create a macro to open each table when combo box dbl-clicked
On Dbl Click event
Macro Names (grouping macros)
Macro to open supporting tables
Lesson 3. Task List Form
Creating a Task List continuous form
Lesson 4. Synch Task List to Task Form
Creating a macro to open a specific form
Setting the OnCurrent event in the Task List Form
GoToRecord macro command (macro action)
GoToControl macro command
Creating a command button without the wizard
Assigning a macro to a command button
SetValue macro command
Using the Expression Builder
Creating buttons to set the due date to today
Lesson 5. More Date Buttons, Requery Button
Button to set due date to tomorrow
Button to set due date plus one, minus one day
Learn about adding fractions of a day
Button to set due date to "later today"
Creating a second bound date field with different format
Creating a button to requery your form results
|
| Access 207 |
Lesson 1. Task Query
Creating a TaskQ query
Create a TaskViewDate form field to limit the date
Base the data in the query on the date on the form
Run our requery macro in the On Open event
Run our requery macro in the AfterUpdate for the date
Copy date reset buttons from TaskF to TaskListF
Lesson 2. Recurring Tasks
Learn about Macro Conditions (groups)
Conditions are IF/THEN statements for Macros
If the Completed box is clicked on a recurring macro increment date
Deal with weekly, monthly, and other tasks
Allow Edits form property
Allow Deletions form property
Allow Additions form property
Locked control property
Lesson 3. Choose to Hide Future Tasks
Create a Show All Tasks check box
If the check box is unchecked, hide tasks by date
If checked, show all of the tasks regardless of date
Use the IIF() function to determine if task in future
Use the NOT keyword to negate values
Complex IIF() function with multiple conditions
Nested statements using AND and OR clauses
Feeding query results into a second query Task2Q
Lesson 4. Filtering Results
Manually filtering using built-in Access filters
Filter By Selection, Remove Filter/Sort
Creating our own filter text and combo boxes
Adding * values to our supporting tables
Review of the LIKE keyword in queries
Manually editing the Column Count and Column Width
Lesson 5. Miscellaneous
Dealing with missing data
Tasks without a category won't show. Fix this.
Compact and Repair Database
Add Tasks button to Main Menu
|
| Access 220 |
1. Sales Log Table
Create SalesLogT
Default SaleDate to =Date()
Setup PaymentMethodT
2. Sales Log Query
Calculated Query Fields
Multiply Two Fields Together
Calculate Extended Price
Mathematical Operators
Add, Subtract, Multiply, Divide
Integer Division
Exponents
Modulo (Modulus)
IIF Function
Determine If Taxable
Calculate Order Total
Query Field Properties
Round Function
Rounding Field Values
3. Sales Log Form, Part 1
Copy Customer Combo from ContactF
Bring in Calculated Fields from Query
Continuous Forms
Form Header and Footer
Move Labels to Header
Horizontal Line
4. Sales Log Form, Part 2
Create an Order Total in the Form Footer
SUM Function in Footer Field
Totals with Filters On
Show Sales for Specific Customer
Open a Form and Show Specific Records
5. Query Join Types
Only include rows where the joined fields from both tables are equal
Inner Join
Include ALL records from Table1 and matching fields from Table2
Outer Join
Show ALL Customers and Sales if they have them
Show ALL Sales and Customers if listed
6. Global Relationships
Enforce Referential Integrity
Cascade Delete
Tools > Relationships
Why I don't often use Global Relationships
|
| Access 221 |
1. Aggregate Queries
Summary Queries
Totals Queries
Group Different Records Together
Sums, Averages, Counts
Examples in Excel (for understanding)
Sorting in Excel
Data > Subtotals in Excel
Grouping in Excel
2. Build an Aggregate Query
List of Unique Customer Names Only
Essentially Eliminate Duplicates
Totals Button in Query Design
Show Total Amount Due by Customer
Max Value
Count of Records
Average (Avg) of Amounts
3. Sales Totals By State
Group by State
First Record
Unique Phone List of Customers
Sales Totals by State
Add a Date Criteria
Add Parameters
4. Most Recent Contact Date
When was the last time you contacted a customer?
Show all customers and contacts if they exist
Outer Join on CustomerID
Show Min of DateTime
Oldest Contact Date for Each Customer
Count of Contacts for Each Customer
Renaming Query Fields
Limit by State with Criteria
In() Function
In("TX","NY")
5. Sales Totals by State
Report Page Header & Footer
Sorting & Grouping Levels
Group Footer Visible
Report Section Footer
SUM Total in Group Footer
Visible Property - Hide Report Fields, Labels
6. Employee Time Sheets
Table to Track Time In, Time Out
Join WorkLogT to EmployeeT
Calculate Hours Worked
SUM Up Hours Worked per Employee
CDate Function - Convert To Date
7. Work Log Form
Employee Combo Box
Hours Worked SUM Total in Form Footer
Filter By Employee
Button On Main Menu
8. Time Sheet Form
Parameter Form to Select Employee and Date Range
Display Time Sheet Based on Parameters Entered
Forms!FormName!FieldName
Get EmployeeID From Time Sheet Form
Run Query from Command Button Wizard
9. Time Sheet Report
Employee Data in Group Header
Hourly Data in Detail Section
SUM Hours Worked in Employee Footer
Command Button to Preview Report
"Print Timesheet" Button
Enter Hourly Pay
Calculate Amount Earned
|
| Access 222 |
1. Intro to Action Queries
Different Types of Action Queries
What do Action Queries Do?
Update Query
Append Query
Delete Query
Make Table Query
Always Back Up Your Database!
Always Back Up Your Tables!
2. Update Query
Change a company name to a different one for multiple records
Build an Update Query with the Query Designer
Update To
Criteria
3. Another Update Query
Change company name only for companies in NY
Query to update all uncompleted Tasks to today's date
4. More Update Queries
Change all states to CAPS (ny to NY)
Change all names to CAPS (joe to JOE)
UCase() Function
LCase() Function
Proper Case with StrConv() Function
5. Yet Another Update Query
Length of a string: LEN() Function
Add an area code to phone numbers that are only 7 digits long
Change email domains from @xyz.com to @abc.com
INSTR() Function
Left() Function
Disecting the username from the email domain
6. Append Queries
Create a query to archive your old contacts
Copy all records older than 1 year to an archive table
7. Delete Query
Delete those archived records from the last lesson
Cascade deletes and global relationships
Cascade update related fields
Viewing related records in a table
Delete query also deleting all RELATED records
8. Make Table Query
Making a backup of a table with a Make-Table Query
AutoBackup Query
Exporting just a specific set of data
9. Automated Queries
Make a form to automate archiving contacts
Specify archive date in a form field
Macro with OpenQuery command
Command Button to Run Macro
Record Selectors to No
Navigations Buttons to No
Turning Off Warnings Permanently
Options > Confirm > Document Deletions, Action Queries
Turning Off Warnings Temporarily
SetWarnings Macro Command
MsgBox Macro Command
|
| Access 223 |
1. Crosstab Queries, Part 1
What is a Crosstab Query?
Similar to an Excel PivotTable
Format() Function "yy-mmm"
Sales Totals by Month
Sales Totals by State by Month
2. Crosstab Queries, Part 2
Create a Crosstab Query
Column Heading
Row Heading
Value
Adding Date Criteria
Defined Query Parameters
Breaking Down by Year
Specify Column Headings
3. Crosstab Query Wizard
Using the Wizard to Build a Crosstab Query
4. Other Query Wizards
Simple Query Wizard
Find Duplicates Query Wizard
Find Unmatched Query Wizard
Finding duplicate customer records
Find customers that have no contacts
5. Query Properties
Showing the Top X Values (eg Top 10)
Showing the Top X% Values (eg Top 10%)
Unique Values
Unique Records
6. Beginner SQL
SELECT field FROM table
Build a Query in the Designer
Switching to SQL View
Multiple Fields
Removing Clutter that Access Adds to SQL
INNER JOIN
WHERE clause
Placing SQL statements in combo box Row Source property
ORDER BY clause
ORDER BY DESC
UNION Query - can only be done with SQL
7. Query Tips & Tricks
IN() Function
Cartesian Product
Count(*) Function
Medium Date Format
|
