Microsoft Access Mastery: Access SQL, Normalisation & Split Databases
Master the Access SQL dialect, IIf, Nz, crosstabs, action and parameter queries, normalisation to 3NF, data types, indexes, split front-end/back-end databases, linked tables, DAO recordsets and data quality on a fictional rental business, with every answer checked as you go.
- One-time payment
- Instant access right after payment
- Secure checkout with Stripe
About this course
This advanced course takes you deep into Microsoft Access on a fictional outdoor rental business. You work through the Access SQL dialect with IIf, Switch, Nz, date literals, LIKE wildcards and the TOP tie trap, then crosstab, action and parameter queries and the NOT IN Null trap. You normalise flat tables to 1NF, 2NF and 3NF and measure the anomalies, choose data types and estimate file size, enforce referential integrity, judge index selectivity, plan a split front-end/back-end database, audit linked tables, trace a DAO recordset loop and validate an import batch. Every answer is checked instantly, and the first lesson is free.
Lessons
- The Access SQL Dialect: IIf, TOP and Saved QueriesFree
- Dates in Access SQL: #Literals#, DateAdd and DateDiff Locked
- LIKE in Access: *, ?, # and the ANSI-92 Trap Locked
- Null, Nz and Aggregates: Where Rows Silently Vanish Locked
- Totals Queries: Group By, Where vs HAVING, First/Last Locked
- IIf and Switch: Conditional Logic Without CASE Locked
- Crosstab Queries: TRANSFORM … PIVOT Locked
- Joins the Access Way: Parentheses, Unmatched, Yes/No Locked
- Subqueries: the NOT IN + Null Trap and Correlated Queries Locked
- Action Queries: Update, Delete, Append, Make-Table Locked
- Parameter Queries: Typed PARAMETERS and Safe VBA Locked
- First Normal Form: Kill the Repeating Group Locked
- Second Normal Form: Partial Dependencies and Anomalies Locked
- Third Normal Form: Transitive Dependencies Locked
- Data Types and Sizes: Currency, Long Integer and the 2 GB Limit Locked
- Referential Integrity, Cascades and Orphans Locked
- Indexes and Performance: Selectivity, Seeks and Scans Locked
- Split Databases: Front-End, Back-End and Network Cost Locked
- Linked Tables: Connect Strings, UNC Paths and Relinking Locked
- VBA Essentials: Tracing a DAO Recordset Loop Locked
- Data Quality: Validation Rules, Unique Indexes, Duplicates Locked
- Final Project: The Monthly Net Revenue Report Locked