How to Handle the Subtotal and Total Problem

Excel to Access: Intro to Microsoft Access for Excel Users

Section 1: Introduction - Course Layout and Objectives

Section 2: Why You Need Access

Section 3: The TABLE Object

Intro to Tables and Tabbed vs Overlap View (6:35)
Editing, Adding and Deleting Records (7:25)
Sorting a Table (3:13)
Basic Filters (4:07)
Advanced Filters (7:42)
Using Find and Replace with Tables (6:12)
Create a Table and Add a Primary Key and Fields (4:52)
Create Table 2 - Add the Rest of the Fields to the Table (5:28)
Create Table 3 - Why are there So Many Numeric Field Sizes? (1:46)
Table Properties 1 - Formats and Input Masks (5:05)
Table Properties 2 - Defaults and Validation Rules (6:09)
Table Properties 3 - Add Lookups to a Table (5:26)
Modify Table Design - Add, Delete and Move Fields (5:23)

Section 4: The REPORT Object

Introduction and Create a Report (5:52)
Using the Report Wizard (6:08)
What are Controls? (4:46)
Report View and Layout View (4:46)
Modify the Report Design and Selecting Controls (8:22)
Entering Formulas Into Text Boxes (10:08)
Sorting and Subtotals in Reports (7:14)
Conditional Formatting (3:48)
The Properties Box (6:46)
Using The Properties Box (12:55)
Conclusion and Why We Need Queries (2:20)

Section 5: The QUERY Object - SELECT Queries

Overview of Select Queries Database (3:15)
Intro to Select Queries (6:15)
Sorting in Select Queries (4:00)
Using Criteria in Queries - And vs OR (4:14)
Using Criteria in Queries - Wildcards (4:21)
Using Criteria in Queries - Formulas in Criteria (3:01)
Calculations in Select Queries (5:01)
Multiple Tables in Queries (6:54)
Parameters in Queries (4:52)
A Parameter Query Trick (4:29)

Section 6: Reports Revisited

Section 7: Unique Value, TOP and Total Queries

Unique Value Queries - Part 1 (4:22)
Unique Value Queries - Part 2 (5:54)
TOP Queries (6:15)
Total Queries - Part 1 (6:56)
Total Queries - Part 2 (3:50)

Section 8: ACTION Queries

Intro to Action Queries with a Make Table Query (5:26)
Append Query (6:51)
Delete Query (3:38)
Update Query Part 1 - Increase Prices (2:54)
Update Query Part 2 - Add an Order Total Field (9:05)
A Quick MACRO to Automate Updating the Order Totals (5:09)

Section 9: Migrating from Excel to Access 1 - Get the Data into Access

Section 10: Migrating from Excel to Access 2 - Design the Correct Structure

Combine Data in Excel Before Bringing into Access (4:35)
Planning Your Database - 3 Best Practices - NUMBER 1 (3:47)
Planning Your Database - 3 Best Practices - NUMBER 2 (4:20)
Planning Your Database - 3 Best Practices - NUMBER 3 (2:14)
How to Print your Table structures (1:47)
How to Use the Relationships Screen (4:41)
Why I Don't Use the Relationships Screen (6:39)
Why You DON'T want to Use Analyze Table! (3:12)

Section 11: Migrating from Excel to Access 3 - Building the New Tables

Using Queries to Create the Product Table (8:43)
Create the Customer Cross Reference Table (3:01)
Find the Potential Problem Customer Records (5:05)
Manually Decide on How to Handle Each Duplicate (4:38)
Create the Customer Table Structure and Starting Number (3:45)
Populate the Customer Table and Create the Customer IDs (3:54)
Plug the New Customer IDs into the XLData Import Table (3:58)
Create the Order Products Table (4:35)
Create the Orders Table Structure (3:06)
Create a Query to Append Orders from XLData (13:16)

Section 12: The FORMS Object 1 - Create a Customer Form and an Orders Subform

Overview of the Five Forms Sections (1:45)
Overview of the Forms1 Database Tables (5:43)
How are Forms Different from Reports? (5:50)
Create the Customer Form (8:11)
More on the Customer Form: Aligning Controls (3:09)
More on the Customer Form: Stacking Controls (3:28)
Finish the Customer Form: Tab Order and Input Masks (3:09)
Create the OrderList Form with the Form Wizard (4:11)
Modify the OrderList Form Layout (5:32)
Add the Subform to the Customer Form (4:19)

Section 13: The FORMS Object 2 - Form Building Shortcuts and More on Subforms

Use Shortcuts to Build the Orders Form (8:08)
Create a Subform for OrderProducts (7:13)
Add a Product Code Lookup Combo Box (5:48)
Populate the Product Name and Total Fields (5:34)
A Tiny Bit of VBA - ProdCode AfterUpdate (7:20)
Create a SubTotal Field for Form (3:12)
Add the OrdProdList Subform to the Orders Form (8:50)
How to Handle the Subtotal and Total Problem (3:06)

Section 14: The FORMS Object 3 - Moving Between Forms

Section 15: The FORMS Object 4 - Adding Customers and Orders

Section 16: Create an Invoice with One Click!

Remove the Subtotal and Total Fields (1:39)
Create the Invoice Query (4:54)
Initial Layout of the Invoice (2:49)
Format the Invoice Header (7:18)
Format the Product Area (9:18)
Add the Calculated Fields (5:58)
Create the Macro and Tie to a Button on the Orders Form (6:42)
Fix the OrderList Form Subtotal and Total (6:54)

Section 17: Automate the Opening and Closing of the Database

Section 18: Add Tab Controls to Forms

Section 19: Conclusion and Where to Go From Here

Lesson content locked

If you're already enrolled, you'll need to login.