Access Expert 1

00. Intro (7:13) 01. Relational Database Concepts (17:06) What is a Relational Database Why are Relationships Important Flat File Databases Key Fields Primary Key Foreign Key Types of Relationships One-to-one Relationships One-to-many Relationships Many-to-one Relationships Many-to-many Relationships Junction Table Normalization 02. Relational Tables (14:53) Making our contact management relational Inserting a CustomerID in ContactT Removing duplicated fields Setup a Foreign Key Moving fields in table design Fixing Lead Sources Fixing Shipping Methods 03. Relational Queries (14:04) Add CustomerT to ContactQ Ad Hoc Query Relationships Automatic Join Line Delete Relationship Manually Create Relationship Asterisk from One Table Join Missing Customers Join Types Left Join / Outer Join Joined fields from both tables are equal Show All Records from CustomerT Only those records from LeadSourceT Where the joined fields are equal Fix ContactR Report 04. Relational Combo Boxes (19:34) Add Combo Box to Contact Form Combo Box Based on Table Combo Box Based on Query Wizard is Slightly Different Fix Lead Source Combo Fix Shipping Combo 05. Review (04:50)

Access Expert 2

00. Intro (7:04) 01. Normalization (21:06) What is Normalization First Normal Form (1NF) Flat Tables Atomic Fields Repeating Groups of Data Entities (Entity) Second Normal Form (2NF) Update Anomalies Third Normal Form (3NF) Remove Derived Fields Calculated Values Denormalization Improve Performance Storing History Data 02. Explicit Relationships (14:00) Global Relationships Relationships Window Add Tables Edit Relationships Window SubDatasheet Subtable Orphaned Child Records Edit Relationship Referential Integrity Unable to Delete Parent 03. Cascade Update & Delete (13:16) Cascade Updates Why you should never need them Cascade Deletes Automatic Delete of Children Backup Your Data! Can't Delete Tables in Relationships Arrange Tables in Relationship Window Hide Tables Delete Relationships Relationships Report Object Dependencies Subdatasheet Table Properties Subdatasheet Expanded Subdatasheet Height Subdatasheet Name Link Child Fields Link Master Fields 04. Values Across Forms (23:13) Find Contacts for Current Customer Command Button Open Form Find Specific Data To Display Save Form Position Set Values Between Forms Forms Collection Forms!FormName!FieldName Default Value Value of a Combo Box Combo Box Columns Bigger Notes Field in Form Footer Move Combo Box to Form Footer TabStop Property Brief Intro to Expression Builder Dirty Records Refresh Problem 05. Review (6:11)

Access Expert 3

00. Introduction (7:38) 01. SQL Primer (11:39) Combo Box Row Source SELECT FROM ORDER BY WHERE Change Combo Sort Change Combo Field Order Record Source for Form 02. Callbacks (19:26) Modify ContactT Default Value for Dates Today's Date Tomorrow's Date Tomorrow at Noon Tomorrow at 9 AM Modify Contact Form Custom Date Format ddd mm/dd/yyyy hh:nn am/pm Callback Query Sort by Date Callback Form Add button to Main Menu Add customer name to list Button to open Customer Form 03. Contact Subform (16:27) What is a Subform Master and Sub Forms Parent and Child Forms Copy and modify Contact Subform SQL Statement as Record Source Sort by Date Reverse Order SELECT ORDER BY DESC Subform Object Control Click and Drag to create Subform Subform Object Properties Subform Form Properties Access Autoform with Subform Link Master Fields Link Child Fields 04. Form Footer Totals (23:59) Product Category Table Product Table ProductSubformF ProductCategoryF Aggregate Functions SUM AVG MAX MIN COUNT Modify Embedded Macro Change Form Name in Macro Moving to Access 2013 05. Review (6:41)

Access Expert 4

00. Intro (7:37) 01. Extended Info Tables (19:26) Create CustomerExtendedT ShortText and LongText Fields in 2013 Yes/No NULL Value Password Input Mask Family Member Table Relative Type Table Force One-to-One Relationship Indexed No Duplicates 02. Extended Customer Form 1 (23:55) Create Extended Customer Form Option Group Yes as -1, No as 0 Unspecified option Change y/n to number Create List Box from Scratch Create Toggle Buttons Manipulate Toggle Colors Lighter Darker 50% Hover Color Pressed Color 03. Extended Customer Form 2 (26:05) Command Button Open Ext Form Default Value Forms!CustomerF!CustomerID Locked Property Change Form Properties Modal, Popup Navigation Buttons Record Selectors Caption & Trick Tab Controls Image Control Save Path Filename in Table Set Image Control Source Customer With Image Query Display Image on Report Customer Labels with Image 04. Family Member Form (14:15) Description Indexed No Duplicates Relative Type Helper Form SQL Recordsource Family Member Form Relative Type Combo Box Link Master Fields Link Child Fields List Items Edit Form 05. Toolbox Control Overview (11:20) Hyperlink Control Label with Hyperlink Address Show File Extensions Brief Discussion Of... Web Browser Control Page Break Control Chart Control Line & Rectangle Controls Attachment Control ActiveX Controls 06. Review (5:12)

Access Expert 5

0. Intro (06:52) 1. Contact Letter Query (9:51) Make Notes Field Rich Text Set fields to Rich Text Open Report to Specific Record Query Criteria Forms!FormName!Field Run Query Without Form Open Enter Parameter Value Prompt 2. Contact Letter Report (15:16) Format of a Printed Letter Create Blank Report Bring in Contact Query Fields Remove Borders Long Date Format Create FullName Field in Report String Concatenation in Text Box Can Grow, Can Shrink Text Box and Section Grow / Shrink Label for Return Address SHIFT-ENTER for New Line Paper Size Margins Put Logo on for Letterhead Line and Web Address in Page Footer 3. Print Letter Button (11:45) Command Button Print Preview Report Dirty Records Refresh Data Before Opening Report Edit Embedded Macro Build Event OpenReport Command Add New Action RefreshRecord Command Change Order of Macro Commands Match Size of Text Box with Letter Page Numbering Page N of M 4. Export or Email Letter (18:15) Export to Excel Export as Text File TXT Export as PDF or XPS Send as Email Attachment w Outlook Export as Word Document Export as HTML Document Email Report Command Button EMailDatabaseObject Command Object Type Object Name Output Format Anatomy of a Hyperlink Field HyperlinkPart Function 5. Customizing the Ribbon (9:06) Using ALT Keys Minimize the Ribbon Collapsing the Ribbon Customize Quick Access Toolbar Add to Quick Access Toolbar Access Options Customize Ribbon Popular Commands Commands Not in the Ribbon New Custom Tab New Custom Group 6. Review (5:54)

Access Expert 6

00. Intro (7:10) 01. Collection Letters 1 (11:32) Create OrderT Table Create OrderCollectionLetterQ 02. Collection Letters 2 (12:17) Copy current contact letter OrderCollectionLetterR Change record source Detail section properties Force New Page After Section Turn off Alternate Background Color Add order info fields to letter 03. Collection Letters 3 (13:29) LetterT Table LetterF Form Enter multiple letters Text on report set by form BeginDate and EndDate for query Can't edit with Date() in Control Source 04. Collection Letters 4 (8:55) Put LetterF on Main Menu Change date range based on letter BeginDaysAgo, EndDaysAgo in LetterT Set BeginDate and EndDate Automatically 05. Generic Letters (19:19) Send letters by various criteria Letter to Active customers AllowEdits vs. Locked Field CustomerActiveQ Query LetterActiveR Report Letters by Lead Source LeadSourceCombo LettersByLeadSourceQ 06. Review (5:21)

Access Expert 7

00. Intro (8:08) 01. Lookup Values in Tables (17:46) Add Group Field to CustomerT Lookup Data Type I will type in the values Limit to List Cons of Lookups in Tables NOT Supported by SQL Server Allow Value List Edits Allow Multiple Values Row Source Item List Semicolon Delimited Items Two column text value list Bound Column Column Count Lookup from another table SQL Statement directly in table Multi Valued Fields Allow Multiple Values Cons of MultiValue Lists Delete Relationship to GroupT 02. Many to Many Relationships 1 (13:10) Multiple groups per customer Many-to-Many Cross-reference table Junction table Customers to Groups Vendors to Products GroupXCustomerT Group Subform on Customer Form 03. Many to Many Relationships 2 (6:25) Group Form Show Customers in Group Copy CustomerGroupSubF Create GroupCustomerSubF SHIFT-ENTER line break in label Groups button on Main Menu 04. Letters By Group (16:36) IncludeInMailing Field in GroupT GroupMailingQ LettersByGroupR GroupSubF RefreshRecord in Macro Duplicate customers in mailing Quick overview of Aggregate Queries Group By Min Value 05. Post Cards (12:02) Create Report Design Shape Outline Transparent Landscape v Portrait Page Setup Dialog Number of Columns Row Spacing Column Spacing Column Size Same as Detail Down, then Across Across, then Down Margins Resizing with the Rulerbar Button from Letter Form 06. Vendors to Products (17:16) Build ProductF Form Vendor Table VendorXProductT Junction Table UnitCost DateUpdated Profit Calculation 07. Review (5:10)

Access Expert 8

00. Intro (8:22) 01. Products to Vendors (24:01) Homework Review Button to Open Product Form VendorF Form VendorProductSubF Button to Open Vendor from Product Button to Open Product from Vendor Give Names to VendorCombo, ProductCombo Query to add UnitPrice to Junction VendorXProductWithProfitQ Add Price and Profit to Vendor SubF 02. Order Details Table (11:42) Create OrderDetailT Delete AmountDue from OrderT Possibility of breaking stuff! Create OrderDetailQ Pull in data from ProductT 03. Calculated Query Fields (12:29) Calculate ExtPrice String Concatenation Reviewed Assignment Operators * / + - \ ^ MOD Integer Division Modulus Order of Operations PEMDAS Comparison Operators < > <= >= <> = AND OR NOT XOR -1 or 0 for True False Boolean Values Format Property in Query Column Format True/False Format Currency 04. Sales Tax (16:32) IF THEN Statements IIF Function Function Review SUM AVG MAX MIN COUNT DATE NOW TRIM SalesTax IIF Function ROUND Function Rounding Numbers Bankers Rounding Traditional v Bankers Rounding Nested Functions Nesting Functions Fractional Penny Problem INT Function Round Down 05. Order Detail Subform (14:44) Create OrderDetailF Subform Product Combo Box Lock UnitPrice Field Order Relationships Customers to Orders Do NOT Cascade Delete Ordsrs to Details DO Cascade Delete 06. Order Form (22:18) Create OrderF Customer Combo Box Show Name if Company Null Is Null vs IsNull() Add OrderDetailSubF Auto Default CustomerID Auto Default IsTaxable Form Footer Calculations Sum of ExtPrice Sum of SalesTax Grand Total 07. Review (5:06)

Access Expert 9

00. Intro (8:39) 01. Customer Discount Rate (20:26) Discount Rate for Orders Discount Rate for Order Details Different Validation Rules Recalculate Sales Tax Tab Cycle Property LineTotal 02. Multiple Sales Tax Rates (17:42) SalesTaxRate for Customers SalesTaxRate for Orders SalesTaxRate for Order Details Values Propagate Down Forms Disable Close Button Disable Control Box System Default Values Table 03. Non Taxable Products (12:01) IsTaxable for Products Show IsTaxable in Product Combo SQL ALIAS IF Product Tax Exempt Another IIF Example 04. Printable Invoice 1 (20:27) Order Detail Subreport Report Header / Footer Page Header / Footer Order Info with Customer Data OrderQ Customer Bill To Info Insert Subreport 05. Printable Invoice 2 (19:58) Company Info in Header Missing Company Name Gap Can Shrink Problem Adjacent Control Problem Overlapping Controls TRIM Function Open Report to Specific Record Query Criteria Company Return Address Info 06. Review (6:08)

Access Expert 10

00. Intro (7:02) 01. Quote or Invoice (28:09) Add My Company Info to Defaults Company Logo Size to Tallest Paid or Unpaid Text on Invoice Refresh Dirty Record IsQuotation Field on Order Form Quotation or Invoice Text Nested IIF Functions Hide Paid Text if Quotation 02. DLOOKUP Function (12:10) Move System Defaults to Own Form DLOOKUP Field, Table Get SalesTaxRate from DefaultT Edit Invoice to use DLOOKUP 03. More with DLOOKUP (25:35) Sales Rep for Customer SalesRepID ServiceTechID Related Fields with Different Names Sales Rep Combo Box Service Tech Combo Box SalesRepID on Order Form Display Sales Rep's Phone Number Criteria Format "EmployeeID=" & SalesRepCombo @@@[email protected]@@[email protected]@@ Phone Number Format Strings Inside Criteria Double Double Quotes "" Dates in Criteria need # signs AND Condition in Criteria Add Rep Name and Phone to Invoice 04. Calculated Table Fields (8:31) Expressions in Tables vs Queries Problems with Calculated Table Fields When to use them Example: TimeToShip in Days 05. Review (5:35)

Access Expert 11

00. Intro (8:38) 01. Simple Aggregate Queries (13:13) Aggregate Query Totals Query Summary Query Grouping Query What they are Popular Aggregate Functions SUM, AVG, COUNT FIRST, LAST MAX, MIN Statistical Analysis STDEV, STDEVP VAR, VARP Covered in future class SUM in Form Footer Total SUM in Table Footer (last row) Totals Button Simple SUM of all credit limits Group By State Default sort on Group By field Sort by Credit Limit descending Add criteria Is Not Null Notice Field Name is Changed SumOfCreditLimit AvgOfCreditLimit One query with SUM, AVG, MAX, MIN ALIAS field names Count of customers from each state Only NON-NULL values are counted Count ID field to include NULLs Simple Query Wizard Aggregate Options in Wizard Summary Information 02. Complex Query Criteria (7:44) Credit Limit by State After 1999 Brief overview of date functions MONTH, DAY, YEAR Functions HOUR, MINUTE, SECOND Function Break into Two Queries WHERE Field in Total Row 03. Sales Totals by Month (10:50) Format to show "yyyy mm" Format Property in Query Field Format() Function Week of Year ww 04. Lowest Product Cost (12:46) MIN Cost for each Product DLOOKUP Vendor ID at Lowest Cost DLOOKUP Vendor Name, Unit Price Calculate Most Profitable Products 05. Last Customer Contact (6:36) MAX of Contact Date DLOOKUP Contact Notes Dealing with NULL Values IIF Function Review 06. Employee Work Log (26:02) Create Work Log Table Time In, Time Out Timeclock Difference Between Two Times Calculate Hours Worked Caution with Dates and BETWEEN BETWEEN #1/1# and #1/2# Does NOT Include #1/2 5pm# >=Start AND CDATE() Function Brief Mention Work Log Lookup Form Employee Combo Box Command Button Run Query Forms!WorkLogLookupF!StartDate Homework for Next Class 07. Miscellaneous (16:13) Expression Option DSUM Sales Last 30 Days Refresh Form Data Button Can't Refresh Unbound Form 08. Review (5:52)

Access Expert 12

00. Intro (8:01) 01. Order List Form (22:57) Create Order List Aggregate Query IIF to display PAID / UNPAID IIF to display QUOTE / INVOICE OrderListQ AllowEdits AllowDeletions AllowAdditions Sum of Order Total in Footer Sort by Order Date Descending Refresh Form Data Button Doesn't Work Refresh vs Requery Refresh vs Refresh All Buttons Button Build Event RunMenuCommand Refresh Requery Macro Command 02. Work Log Entry Form (35:28) Create a Button Without the Wizard Copy Existing Button Edit Embedded Macro Form Name Copy Button for Query Resize to Tallest Create Work Log Form Employee Combo Box Tab Order Bound Combo in Form Footer Unbound Combo in Form Footer Combo Default Value DefaultEmployeeCombo Default Date Textbox TodaysDate =[TodaysDate]+#9 AM# Populate Defaults with Unbound Fields Work Log for Current Employee Add to Main Menu Track Employee Activities Activity Table Add Activity Combo to Work Log 03. Fix Collection Letters (5:57) Parameter Prompt from Missing Field Update Order Collecton Letter Query Aggregate Total for Amount Due 04. Report Grouping 1 (15:08) Customers Grouped by State Group & Sort Add a Group With a Header Section With a Footer Section With Totals Group Footer Totals Horizontal line Line thickness Showing a Value if Header IsNull IIF IsNull Display [None] Circular Reference 05. Report Grouping 2 (17:56) Add Credit Limit SUM of Credit Limit Grand Total No Aggregate Functions in Page Footer Page of Pages Page Footer Below Report Footer Section Visible Property Hide Sections Do not keep group together on one page Keep whole group together on one page Keep header and first record together With title label - BUG Force New Page After Section Multiple Grouping Levels No Country display United States 06. Employee Work Log Report (16:56) Work Log Report Group by Employee Subgroup by Week of the Year Access has no Week or WeekNum Function DatePart Function Calculate First Day of the Week Week Starting Date SUM of Hours Worked by Week Total Hours for Employee 07. Number & Currency Grouping (17:55) Show Sales Grouped by Rep Subgroup by Month Change to Numeric Grouping Orders Grouped Every $500 Range of Values for Header $0 to $499.99 Integer Division \ Format(X,"Currency") 08. Miscellaneous (7:23) Group by Expression Use a Query Expression Instead Simple Report Wizard Homework! 09. Review (5:21)

Access Expert 13

00. Intro (7:20) 01. Open Report WHERE Condition (28:04) Buttons on Main Menu for Reports Replace Invoice with Grouped Report Same field name on two tables OrderT.F1 and OrderDetailT.F1 OrderID Header Section Open Report to Specific Multiple Records Print Multiple Invoices Macro WHERE Conditions Print All Invoices for a Customer Print All Invoices Between Dates 02. Types of Action Queries (5:49) What are Action Queries Update Query Append Query Delete Query Make-Table Query Backup your data! How to backup your database file Online backup recommendations Backing up a table 03. Update Queries, Part 1 (14:48) Update Queries allow Complex Criteria Change Select to Update Query Update To Property RUN vs Datasheet View Buttons Preview Records to be Updated Running the Update Query Multiple Criteria Change "XYZ Corp" in "PA" Save Query Nested IIF in Update Query Assign Reps Based on Territory 04. Update Queries, Part 2 (10:26) Capitalize States UCASE() Function Can't Delete a Table with Relationships Manually Delete Relationships Delete and Rename Backup Table Restore Relationships LCASE() Function STRCONV() Function for Proper Case Exceptions: Don't change McDonald 05. Update Queries, Part 3 (9:26) Add 5% to all Product Prices UnitPrice = UnitPrice * 1.05 Update all Paid Orders as Shipped 06. Review (6:03)

Access Expert 14

00. Intro (9:13) 01. Scrubbing Data (15:49) Cleaning Data with Update Query Inconsistent Data Formats Fax Numbers with Different Formats Clean them to look uniform Backup Your Data! REPLACE() Function Replace one string with another Watch Field Names Carefully Make sure you use [Brackets] Null values can cause problems LEN() Function Length of String Show FaxNumbers 7 digits long 02. Change Email Domains (12:39) Hyperlink fields are not simple text CSTR() Function Convert to String HyperlinkPart() Function Change Domain Name amicron.com change to 599cd.com NZ() Function Null to Zero 03. Separate First and Last Names (19:29) Create Person Table Put First and Last Names Together INSTR() Function Determine the position of the SPACE char LEFT() Function RIGHT() Function MID() Function (Discussion) Check for Prefix, Suffix, Middle Initial 04. Fix Non-Relational Table (9:51) Employees and Departments Turn Department Names into IDs Query Join on Text Values Update to IDs from other table 05. Daily Student Attendance (18:34) Create Student Table Attendance Table Make Append Query Append To Table Name Current Database Another Database (Discussion) Only Students from One Class Criteria in Append Query 06. Review (8:14)

Access Expert 15

00. Introduction (8:27) 01. Student Attendance 1 (20:12) Types of Different Classes ClassTypeT Individual Class Sessions ClassT ClassQ with Type Name Track Customers in Each Class ClassXCustomerT Class Form with Enrollment Subform ClassF ClassXCustomerSubF Add Multiple Students to Class Each Company Sends Multiple Students 02. Student Attendance 2 (31:12) Create Attendance Table AttendanceT Master Attendance Form Student Subform Modal Popup Default Values from Other Forms AttendanceQ With Supporting Data Left Joins Create Attendance Subform Data Entry Property Attendance Append Query Add Static Date Value to Append Query CDate Function 03. Student Attendance 3 (34:59) Create Button to Run Append Query SetWarnings OFF Turn on Potentially Unsafe Actions Show All Actions Requery Short Date Format for Date Picker Show Attendance for Any Date Attendance Report By Class By Date Sorting & Grouping Attendance Report by Student then Date 04. Contact Archive (15:37) Backup Contact Table Create Contact Archive Table ContactArchiveAppendQ Remove Indexing Primary Key from Archive Change AutoNumbers to Numbers 05. Review (6:31)

Access Expert 16

00. Intro (8:25) 01. Macro to Run Multiple Queries (9:21) Command Button Wizard Open Query Delete Queries Not Listed Archive Contacts Button Macro Builder OpenQuery Command Add New Action Show All Actions SetWarnings On/Off MessageBox Command (MsgBox) 02. Delete Query Multiple Tables (8:56) Referential Integrity Delete Queries and Cascade Deletes Delete Contacts from Customers in CA Two Queries Needed Customer By State Query 03. Make Table Queries (16:05) Create a Customer Mailing List Table Make Table Query in Same Database Create a new blank database ACCDB file Make Table Query into a Different Database Backup Customer Table Multi-Valued Fields Not Allowed Long Text with Append Only Property On Automated Backup of Contact Table Include in Command Button Macro OpenQuery Command 04. Multiple Parent Mailing List (18:25) Create Student Table with 2 Parents Include In Mailings Field Make Table Parent 1 Append Parent 2 Show Another Example Relational Use a Query with a Join 05. UNION Query (10:30) Merge Contacts with Archive Don't use the Union button UNION SQL statement Homework Assignment 06. Review (7:25)

Access Expert 17

00. Intro (9:05) 01. Archive Old Unpaid Orders 1 (13:30) Add ToBeArchived y/n Field Create ToBeArchivedUpdateQ Query Button to Archive Old Orders on Main Menu Database Level Macro Primer Macro to Open Main Menu Putting Macros on Quick Access Toolbar 02. Archive Old Unpaid Orders 2 (21:22) Make OrderListF Show All Order Add ToBeArchived to Order List Form TBA Tool Tip To Be Archived checkbox on Order Form Macro WHERE Condition ToBeArchived=TRUE Create OrderArchiveT Create OrderDetailArchiveT Set Autonumbers to Long Int Append Orders Create AppendOrderDetailArchiveQ Create AppendOrderArchiveQ Duplicate output destination Create OrderToBeArchivedQ to avoid error 03. Archive Old Unpaid Orders 3 (21:27) Backup tables with MakeTable query MakeCustomerBackupQ MakeOrderBackupQ MakeOrderDetailBackupQ Calculated columns are not allowed in SELECT INTO statements Another reason I don't like calculated table fields Remove DaysToShip from OrderT Update customer record "Order Writeoff" UpdateCustomerOrderWriteoffQ Append "Order Writeoff" to ContactT Put writeoff notice in contact table Add OrderID to writeoff notice AppendOrderWriteoffQ Update the order description with "WRITEOFF!" UpdateOrderWriteoffQ 04. Archive Old Unpaid Orders 4 (13:02) Delete orders and details from main tables DeleteArchivedOrderDetailQ DeleteArchivedOrderQ Copy and modify append queries Delete FROM and WHERE Make button on TBA form to perform archive Copy button with macro in it CloseWindow macro command Macro commmand to Close Form UNION query to show orders and archive together SQL View Fields must match exact with UNION and * all fields 05. Crosstab Queries (13:16) What is a Crosstab Query? Similar to an Excel PivotTable Show example of a PivotTable Add some new orders Edit OrderListQ Add State Field Create Crosstab Query Crosstab Query Wizard Built Crosstab Query Manually Set up Helper Query Orders by Month by State OrderByMonthByStateCrosstabQ 06. Review (5:38)

Access Expert 18

00. Intro (6:26) 01. Crosstab Query Parameters (16:45) Sales by Sales Rep by Month Crosstab Fixed Column Headings Manual Row Headers Multiple Row Headings OK Multiple Column Headings NOT OK Criteria in Crosstab Queries Date Criteria in the form yyyy-mm Declaring Explicit Query Parameters Declare Parameter Even If Form Field 02. Sales Rep by Quarter Crosstab (06:11) Query Wizard Date Groupings Total Row Headings Count Row Headings Crosstabs Used With Other Queries 03. Partition Function (7:29) Tiers of Products PARTITION() Parameters Group Products into Price Brackets Count of Products in Each Tier Product Category Row Header Product Tier Column Header Value is Count of Products in Tier 04. Find Duplicates Query (21:19) Create Find Duplicates with Wizard IN() Function Review SQL HAVING Clause, Brief Discussion Of Manually Create Find Duplicates Query Use an Aggregate Query Count > 1 Find Duplicates Multiple Fields Unique Values Unique Records Field List Properties Query Properties Field Properties Output All Fields Recordset Type Dynaset vs. Snapshot Prevent Editing of Records in Query Order By Property Filter Query Property Filter On Load Order By On Load What is an ODBC Database ODBC Timeout Max Records Record Locks, Brief Introduction Record Locking All Records Edited Records Orientation Right-to-Left, Left-to-Right Subdatasheet Height Subdatasheet Expanded 05. Find Unmatched Query (11:36) Use Wizard Create Unmatched Query Manually Left Outer Join Important Is Null Cartesian Product Cross Product Softball Schedule Each Team Plays Each Other Team Once 06. Review (5:23)

Access Expert 19

00. Intro (6:15) 01. Conditional Formatting (28:13) Format large orders green Conditional Formatting Rules Manager New Formatting Rule Check values in the current record or use an expression Field Value Is Greater than, Less than, etc. Highlight orders over $500 in green Add Views to Quick Access Toolbar Conflicting Rules Order of Applied Rules Using Functions like Date() in Conditions Formatting Date Values Formatting Text Values Exact Find Text Inside of Field Expression Is InStr Function Format Customers with "Inc" in name Apply to Multiple Fields at Once Format Entire Row Format if Field Has Focus Disable Changes if Order Marked Paid Conditional Format Subform Value Disable Editing Products on Order if Paid Compare to Other Records Data Bars Lowest, Highest value Numbers, Percentages Max 50 Conditions 02. Exporting to Excel (16:37) Reasons to Export Data Export Table to Excel Different Versions of Excel Data Export data with formatting and layout Open the destination file after the export Export selected records Copy and Paste from Access to Excel Copy Without Column Headers (Field Names) Add an Export Checkbox to Table Export Specific Records Query Hide the Export Field Save Export Steps Create an Outlook Task with Reminder Saved Exports Manage Data Tasks Run Saved Export Routine 03. Exporting to Word (15:05) Export Mailing Labels Export RTF File to Word Mail Merge Access Data to Word Document Word Merge Microsoft Word Mail Merge Wizard Link your data to an existing Microsoft Word document Create a new document and then link the data to it Mail Merge Wizard Edit Recipient List in Word Insert Address Block Insert Merge Field Preview Your Letters Word Doc Automatically Updates from Table Copy and Paste Table Data to Word 04. Exporting Text Files (10:14) Export Text File Export Windows Text MS-DOS Text Notepad Application Basic Access Text Export is Garbage Export Without Formatting Export Text Wizard Fixed Width vs. Delimited Text Changing Width of Field Field Delimiter Text Qualifier Date Order Date Delimiter Time Delimiter Four Digit Years Leading Zeros in Dates Decimal Symbol Save Export Specification Include Field Names in First Row 05. Export Misc (18:23) Export to XML Export to Another Access Database Database must be closed Access needs exclusive read/write Queries Export as Queries NOT Tables Copy Multiple Tables Export to Sharepoint, Brief Discussion Export to ODBC, Brief Discussion Export to SQL Server, Brief Discussion Export to HTML Document Formatted v. Unformatted HTML Coming Up 06. Review (5:32)

Access Expert 20

00. Intro (5:43) 01. Send Bulk Email via Word (29:13) Query for Customers to Email Active Customers with Email Addresses CustomerForEmailExportQ External Data > Export > Word Merge Create a New Document Mail Merge Wizard Use the Current Document Use an Existing List Edit Recipient List Format Document Insert Pictures Preview your Email Messages Complete the Merge Merge to Email To, Subject, Mail Format HTML Check Names Error Message Outlook Cannot Handle Hyperlink Fields Separate Email Address from Hyperlink HyperlinkPart Function Word: Undefined Function HyperlinkPart Make Table Query for Email Table CustomerForEmailExportT Find Data Source Step by Step Mail Merge Wizard Finish & Merge Email Active Customers Button Macro: Show All Actions SetWarnings On/Off OpenTable Command RunMenuCommand WordMailMerge Switch to Email Messages 02. Importing & Linking from Excel (21:27) Copy and Paste Cells to New Table Does first row contain column headings Importing Proper Data Types External Data > Import & Link > Excel Import Excel Sheet Data Import Spreadsheet Wizard Field Name, Data Type, Indexed, Skip Let Access add primary key Save Import Steps F5 to Refresh Navigation Pane Saved Imports > Run Import Errors Append a copy of the records to the table Linking to an Excel Spreadsheet Link to the data by creating linked table 03. Importing Text & HTML (16:18) Discussion of Different Import Features Linked Table Manager, Brief Discussion ODBC Database, Brief Discussion Sharepoint List, Brief Discussion Data Services, Brief Discussion Importing Text Files Advanced Import Specification Importing HTML Documents Importing HTML from a Web Page Table Saving a Web Page as HTML 04. Exporting & Importing XML (14:43) What is XML Why is XML Better than Text or HTML Exporting a Table in XML Data Only Exporting Data with Schema XSD File Exporting XML with Embedded Schema Data 05. Linking to Outlook Folders (11:21) Link to Outlook Inbox Read and Display Email in Access Editing Email in Access Linking to Outlook Contacts Displaying Contacts in Access Editing Contact Info in Access 06. Review (10:38)

Access Expert 21

00. Intro (7:14) 01. Import & Link Access Objects (14:45) Import by Click-and-Drag Import by External Data > Access Browse to Database Folder Import Objects Dialog Import Options Import Table Relationships Import Menus and Toolbars Import/Export Specs Import Nav Pane Groups Import All Images and Themes Table Definition AND Data Table Definition Only Import Queries as Tables Importing vs. Linking Link to Customer Table Custom Forms for Front End 02. Multi-User Database Theory (14:53) Sharing a Database Why Split Your Database Front End vs. Back End Databases Pros and Cons of Split Databases 03. Splitting Your Database 1 (20:38) Compact & Repair First Backup Your Data Database Tools > Move Data > Access Database Splitter Wizard The database engine could not lock table Already in use by another person Notice Linked Tables Set up Shared Folder Linked Table Manager Rename or Move Backend Database Refresh Location of Backend Links Setting up a Shared Folder Folder Properties > Sharing Assign Shared Folder Name Setup Folder Permissions Select Users or Group UNC Path Mapping a Drive Letter 04. Splitting Your Database 2 (21:15) Storing Images on Server Share Creating a 2nd Back End Database Move a Secure Table to Different File Creating a 2nd Front End Database Custom Databases based on User Role Distribute Encrypted Front End Users Cannot Make Design Changes File > Save As > Make ACCDE Trusted Location - Shared Folder 05. Record Locking (28:42) How Default Record Locking Works Write Conflict Error Message Save Record, Drop Changes Copy to Clipboard Form Properties > Record Locks No Locks, All Records, Edited Record What is an LACCDB file? Database Client Settings Page Level vs. Record Level Locking Default Open Mode: Shared / Exclusive File Open for Exclusive One Time Use Default Record Locking Open databases by using record-level locking Changes take effect when first user opens db Delete LACCDB file if record still locked 06. Review (6:15)

Access Expert 22

00. Intro (9:09) 01. Fixing Non-Relational Data (24:25) Create Bad Spreadsheet Import Sheet into Access Aggregate Query for Unique Customer List Customer Query with Most Recent Info DLOOKUP Most Recent Address & Phone Remove Customer Data from OrderT Add CustomerID Foreign Key Query to Change CustomerID Delete Automatic CustomerID Join Create New Join on Customer Name Make Table Query to Create CustomerT 02. Update Vendor Pricing (41:41) Price Sheet Changes from Vendor Create Spreadsheet with Product Costs Import Data into Access Link Our Data with Vendor Data Joined Query is Too Complex for Update Make a Simpler Query Discontinued Products have Null Price Create Macro to Automate Import of Data Make Button to Run Import ImportExportSpreadsheet RunSavedImportExport 03. Fix the Order Entry System (42:21) Fixing the Order Entry System Current DB alters orders on UnitPrice change Add ProductName, UnitPrice to OrderDetailT Fix the OrderDetailQ Unbind Product Combo Box, Move to Footer Add ProductID, ProductName Lock ProductID Unlock UnitPrice Create Embedded Button Macro Build Event > Macro Builder IF Macro Command MessageBox Macro Command StopMacro Macro Command GoToControl Macro Command GoToRecord Macro Command Show All Commands Button Potentially Unsafe Macro Commands SetValue Macro Command Set SalesTaxRate with IF Statement 04. Review (5:28)

Access Expert 23

00. Introduction (7:58) 01. Remote Copy For Data Entry (36:44) Copy PC Resale Main Database Customize for Remote Use Lock Customer Form Don't Allow Edits, Deletions, Additions Only Allow Additions on Contact Form IsRemote Yes/No Field Link to Main Database Tables Not Connected to Network Error Message Append New Records to Server Delete Data in Remote Database Copy All Records from Server to Remote Macro to Perform Synchronization 02. Import Paypal Transactions (25:37) What is Paypal Download Paypal History File Import Paypal Data Into Access Set up my Transaction Table Prevent Duplicate Records 03. Composite Keys (15:44) Prevent Duplicate Values Multiple Fields Create Store Locations Table Do Not Want Two Stores in Same City Indexed, No Duplicates Indexes Dialog Box Create the Composite Index Also Called a Compound Index Make Sure Store Doesn't Report Sales Twice Create StoreSalesT Prevent Duplicate Store Sales Figures Allowing Duplicate Values Find Duplicates Query Wizard SQL Statement Explained Briefly 04. Transfer Multiple Tables XML (7:23) Send Multiple Tables with Related Data Exporting Customers with Orders Export XML Menu Select Tables Import Tables Into a New Database 05. Review (7:29)

Access Expert 24

00. Intro (9:47) 01. Import RSS Feed Data (14:21) Pull News from Web Site RSS Feeds are XML Files Really Simple Syndication Get the 599CD News Feed External Data > Import XML ImportErrors Table Channel and Item Tables GUID: Globally Unique Identifier Field truncation Can't Save Import Steps if Error Import Latest News from MSNBC.com Import Tech News from CNN.com 02. Import Real-Time Weather (14:02) How to find an RSS Feed not advertised Weather.com RSS Feeds Weather Underground API Register for an API key Import Weather XML Feed 03. Display Real-Time Weather (20:56) Convert RFC822 to Access DateTime Value Query Sorted by Observation Date Form to Display Current Conditions Button to Run Macro for Import RunSavedImportExport Requery Form Data WebBrowser Control Display Images from a Web Site Event Timer Timer Interval On Timer Event 04. Online SQL Server Database (28:39) Share Access Data via Web Server Use Access Front End, SQL Tables Set up SQL Server DB on GoDaddy Database Friendly Name Username and Password Export to ODBC Database File Data Source Machine Data Source User Data Source Create New Data Source > SQL Server Set up a File DSN Complete Export of CustomerT Link to the CustomerT on SQL Server 05. Review (5:47)

Access Expert 25

00. Intro (5:44) 01. String Functions 1 (26:27) Function Categories Left() Right() Mid() Separate Phone Number Parts Area Code, Prefix, Suffix InStr() InStrRev() Len() Separate First and Last Names Get Last Name even if Middle Name UCase() LCase() StrConv() Convert to Upper Case Convert to Lower Case Convert to Proper Case Trim() LTrim() RTrim() Remove Extra Spaces from String Replace() Replace One String with Another String Concatenation & 02. String Functions 2 (14:51) Str() CStr() Convert Number to String StrComp() String Comparison Greater, Less, Equal, NULL ASC() CHR() ASCII Code Table Non-Standard Characters Space() Monospace Fonts Kerning, Kerned Fonts String() Repeat a String X Times 03. Logical Functions 1 (14:23) IsDate() IsNull() IsNumeric() IsError() NZ() Replace NULL Value VarType() TypeName() Determining Data Types Data Type Names AND, OR, NOT, XOR Logical Operators 04. Logical Functions 2(26:33) IIF() Nested IIF() Student Grades Pass/Fail Student Letter Grades Switch() Choose() Shipping Methods Calculating Employee Overtime Pay Separating First, Middle, Last Names 05. Review (5:44)

Access Expert 26

00. Intro (06:20) 01. Math Functions 1 (27:09) Abs - Absolute Value Sgn - Sign Postive, Negative, Zero Round Banker's Rounding Round to Evens Don't Want Banker's Rounding Round to Nearest Multiple Int - Round to Integer Fix - Negative Int Rnd Up Cint - Convert to Integer Val - String to Number Val to Convert HEX to DEC Remove Thousands Separator Power of Tens: 2E14 02. Math Functions 2 (12:16) Sqr - Square Root e - Euler's Number Mathematical Constant Exp - Exponential ^ Number to Power Log - Natural Logarithm Calculate Log of Any Base 03. Math Functions 3 (11:40) Calculating PI Inverse Functions Missing Degrees to Radians Radians to Degrees Sin, Cos, Tan, Atn Calculate Building Heights 04. Math Functions 4 (29:21) Mod - Modulus \ - Integer Division Is Number Divisible By X Rnd - Random Numbers Dice Roller Form Reroll Button Requery Macro Command Display Random Records Open a Random Customer Semi-Random Customer Codes 05. Type Conversion (9:42) CBool - Boolean CByte - Byte CCur - Currency CDate - Date CDbl - Double CDec - Decimal CInt - Integer CLng - Long Integer CSng - Single CStr - String CVar - Variant 06. Review (5:11)

Access Expert 27

00. Intro (5:46) 01. Date Time Now 1 (24:49) Inserting DateTime Shortcut Keys Insert Date with Ctrl-; Insert Time with Ctrl-: Insert Date from Previous Record with Ctrl-' Date Time Functions Date(), Time(), Now() Functions Display Current Date in Unbound Form Field Use as Default Value in a Table Field Use as Default Value in a Form Field Format Function Parameters Show Date as "Monday, December 15, 2014" Two Digit Year Cutoff - 2030 Problem Automatically Timestamp New Records Show Records From Today Show Records in the Past Show Records in the Future Show Records on a Specific Date Show Records NOT on a Specific Date Show Records Between Two Dates Show Records NOT Between Two Dates Show Records Outside of a Date Range Show Records on Specific Dates with IN() Function 02. Date Time Now 2 (37:17) DateTime Mathematics Date Stored as a Number Number of Days Since 12/30/1899 Good Enough for Most Circumstances Watch for Dates with Times with Between Show Records From Today With Times Show Records Between Two Dates With Times Show Records on a Specific Date With Times Show Records For Tomorrow Show Records from Yesterday Show Records Within one week from today Birthdays From Now until Next Week Orders Less than One Week Old Aged Accounts Receivable Query with Orders, OrderDetailQ, CustomerT Need DueDate, CompanyName, OrderTotal Order must be an Invoice and Not Paid Aggregate Totals Group By ID, SUM Total Put Aging in Second Query (for clarity) Use IIF Function to Add Aging Data Current Orders Less than 30 days old Between 30 and 60 days old More than 60 days ago Number of Days Late Accounts Receivable Report Conditional Formatting to Hide Zero Amounts Totals in Report Footer 03. Date Time Now 3 (25:24) Hours as Fractions of a Day One Hour in the Future Five Minutes in the Past CDate() Function Convert Numbers or Text as Date Calculating Age "Good Enough" Div by 365.242 Calculating Age in Whole Years Display Total Time Worked in hh:nn Display as 8:30, 8.5, and "08h 30m" Timesheet Spanning Midnight Original Worklog used Date and Time What if you have only Time in your log? Showing 24:00 in WorkLog instead of 00:00 Format two digits 04. Review (6:09)

Access Expert 28

00. Intro (8:30) 01. Break Apart Dates (21:59) Day(), Month(), Year() Hour(), Minute(), Second() Is date in current year Format property display -1/0 as True/False or Yes/No Is date in previous year Is date in next year Is date in current month Is date in current year to date WeekDay() Function WeekDayName() Function MonthName() Function What Day of the Week is Today? First Day of Week Last Day of Week Tuesday Following Date Weeks that Start on a Different Day (like Monday) What's today's Weekday if week starts on Monday? What's the date of the Monday before today? Is Date a Work Day (Mon-Fri) Is Date a Weekend Day (Sat, Sun) 02. DateAdd Function (9:32) Query Insert Columns DateAdd() Function One day from Date DateAdd Format Codes One week from Date One month from Date One year from Date How DateAdd handles leap years One week before Date Exactly 9 months from Date Exactly 21 years before Date 1.5 years from Date Within one calendar month from Date Less than one calendar month before Date Ten Minutes from Date 03. DateDiff Function (15:32) Calculating Difference Between Two Dates DateDiff() Function Number of days between two dates Number of days since order placed Number of months until mortgage is paid Someone's age (not 100% reliable) Number of weeks since Jan 1st of current year Number of minutes worked First Day of Week Optional Parameter First Week of Year Optional Parameter 04. DatePart Function (16:52) Working with individual date components DatePart() Function Show orders from this year Similar things with DatePart as with the Day, Month, Year functions Date in a specific month Date in a specific quarter Date in current week of year Invalid formulas on Microsoft's web site Date in previous week Date in next week Be careful of suspicious behavior around the end of the year Use optional parameters for DatePart Date in previous month Date in next month Date in current quarter Date in previous quarter Date in next quarter 05. DateSerial Function (17:03) First Day of Month Last Day of Month First Day of Previous Month Last Day of Previous Month First Day of Following Month Last Day of Following Month First Day of Quarter Last Day of Quarter First Day of Year Last Day of Year How Many Days in Month How Many Days in Quarter How Many Days in Year What Day of the Year is it? How Many Days Remaining in Year? Exact Age / Anniversary Calculations TimeSerial() Function Just like DateSerial DateValue() Function TimeValue() Function CDate() Function - BOTH date AND time Useful to convert strings to DateTime values 06. Ordinal Dates (08:40) Displaying 1st, 2nd, 3rd, 4th, etc. Discuss VBA Function for Ordinals Learn how to calculate with IIF Functions 07. Miscellaneous (33:02) Calculate Someone's Next Birthday Date List of Birthdays in Next 60 Days How many days until your next birthday List of Birthdays from Next Month Use the NZ function to substitute a date Not all customers have CustomerSince If NULL, assume start date of business Field Level Validation Rules (Field-Level) Table Level Validation Rules (Table-Level) Validation Rule ShipDate > OrderDate Validation Rule OrderDate <= Now() 08. Review (6:36)

Access Expert 29

00. Intro (6:23) 01. Aggregate Functions (12:56) Sum, Avg, Count, Max, Min, First, Last Will cover StDev Later Will cover Variance Later Use in a Form Footer Use in an Aggregate Query Use Aggregate as Query Criteria 02. Domain Aggregate Functions (17:53) DLookup, DSum, DCount, DAvg DMax, DMin, DFirst, DLast Domains & Criteria Number, String, Date Criteria No Criteria Double Double Quotes for Strings Largest Autonumber in Table How Many Records in Table Criteria on the Current Form Criteria on Another Form Concatenation of Criteria Multiple Criteria Parentheses in Multiple Criteria 03. Domain Aggregate Examples (18:14) DLookup Sales Rep Phone Number DSum Orders From Last 30 Days System Default Values Table DCount Orders in Last 30 Days DMax Show Last Order Placed DAvg to Show Average Sales Calculate Average Daily Sales Forecast Future Sales 04. Create Your Own Counter (26:39) Creating Editable Counters Can't put DMAX or MAX in Table Default Expression Builder List of Functions Try Using Form Default Value Field Order Of Evaluation Before Insert Event Can't use Max in Macro Use DMax to Find Largest Current Value Increment Counter Value Test Referential Integrity Cascade Updates for ProductCategoryID Use NZ for initial seeding Show DMax VBA Code 05. Running Balances (24:48) Set up Bank Account Table Check Register Deposits are Positive Amounts Checks are Negative Amounts Running Sum Property in Report Running Sum over Group: Month No Running Sum in Forms Use DSUM for Running Balance DSUM Based on the ID DSUM Based on the Transaction Date 06. Excel VLOOKUP Replacement (14:19) Student Letter Grade Example Assign "A" for Grade 90, etc. DMAX and DLOOKUP Combination Look up Customer Credit Bracket Gold, Silver, Bronze, etc. 07. Review (6:36)

Access Expert 30

00. Intro (6:00) 01. Functions & Interest (9:08) Access Financial Funtions Functions Covered Today: PV, FV RATE, NPER PMT, PPMT, IPMT Functions NOT Covered: NPV, DDB, IRR, MIRR SLN, SYD Simple v. Compound Interest Rates APR Annual Percentage Rate 02. Loan Calculator (18:08) Calculate Mortgage Payments PMT Function Amount of Loan Number of Periods Interest Rate Monthly Payment Total Amount Paid Total Interest Paid Rounding Issues Extra Payment at End of Loan 03. Investment Calculator (7:37) Calculate Future Value of Investment Amount of Investment APR Annual Percentage Rate Number of Years to Invest Extra Payments Future Value FV Function 04. Interest Rate Calculator (11:30) What was my rate of return ROI Intial Investment Amount Amount of Return Number of Years to Invest What was my Interest Rate RATE Function Why sometimes divide by 12 Annual vs. Monthly Compounding 05. Millionaire Calculator (9:30) How long until I'm a millionaire Initial Deposit Amount Monthly Payment Amount Goal Amount Interest Rate Calculate Number of Months Convert to Whole Years INT Function NPER FUnction 06. Initial Deposit Calculator (7:06) College fund calculator How much deposit to hit goal return Current Balance Amount Interest Rate Years to have account open Extra annual payments PV Function 07. House Value Calculator (5:55) Monthly payment you can afford Interest rate from bank How many years for your mortgage Calculate house you can afford PV Function 08. Loan Amortization (22:47) How Amortization Works Interest and Principal Portions Build LoanF Form Create AmortT Table for Periods Build AmortF Subform Link Child and Master Fields Calculate Interest Paid IPMT Function Calcaulte Principal Paid PPMT Function Rounding Issues IIF Function to Hide Errors 09. Review (6:51)

Access Expert 31

00. Intro (4:55) 01. Charts 1 (11:22) What is a Chart Why are Charts Useful Chart Area Data Series Data Points Chart Axes Horizontal X Axis Vertical Y Axis Chart Title & Legend Chart Gridlines Create a Sales Chart Chart Wizard Add Chart to Form Chart Types 02. Charts 2 (10:52) Chart Formatting Options Chart Datasheet Components Dropdown Edit Chart Title Format Data Series Change Colors Fixing Distortion Clip, Stretch, Zoom 03. Charts 3 (10:40) Transparent Chart Background Multiple Data Series Preview Chart Button 3-D Column Chart 3-D View & Effects Elevation, Rotation 04. Charts 4 (12:03) Data Series Shape Data Labels, Value Gap Width, Depth Chart Depth Format Walls, Floor Aggregating Data Add Trendline Pie Charts Angle of First Slice Data Labels Leader Lines 05. Charts 5 (9:28) Review PCResale Database Sales by Rep previous 30 days Chart Dashboard Setup Queries 06. Charts 6 (11:13) Build Dashboard Chart in a Report 07. Review (4:49)

Access Expert 32

00. Intro (4:10) 01. Single Letter Report Header (18:05) Contact Report Creating Group for Single Letter Group by First Character Display LEFT 1 Character Group by Contact Name IsNull to Show No Company Name 02. Report List Form (17:19) List of All Reports in Database MSysObjects Table List of Type Values Custom Report List Table Report List Form and Listbox Macro to Open Report Build Event On Click Event 03. Rank, Median, Mode (25:04) SubQueries, SubQuery Query Customers Less Than Average Employees Rank Formula Alias for a Table Median Value "Good Enough" Mode Value Totals Query, Top 1 Value Percentile Ranking 04. Intro to Macros (36:18) Customer Mailing Labels Create LabelT Table Create LabelR Report Insert Blank Records at Top Cut and Paste Create LabelF Form Create View Labels Button Print Preview Report Macro to Insert Blank Records Command Button Macro GoToRecord New Record SetValue Show All Actions Unsafe Actions Form Macros v External Macros Button to Run External Macro Prompt For Loop Count Value InputBox Function Insert Blanks on Top of Label Sheet RunMenuCommand SelectRecord, SelectAllRecords Cut, Paste Button on Customer Form to Add Label OpenForm 05. Review (5:12)