SQL for Microsoft Access

Learn how the where clause filters records by conditions, using operators like greater than, less than, between, not equal, and brackets to resolve ambiguity; understand semicolon usage and readability.

Learn how to use group by to summarize data in SQL queries, apply aggregates like sum, and decide what to group by, with examples from payment type and purchase orders.

Explore aggregations in Microsoft Access by using group by for non-aggregate fields, and apply sum, count, min, max, and average with the expression builder to name results.

Filter text fields with the where clause in Access using literal strings, brackets, and not equal; use left, right, and like with wildcards to match starts, ends, or contains letters.

Explore how nulls behave in Microsoft Access SQL, learn to use is null and is not null to find or exclude unknown values, and distinguish null from empty strings.

Explore text and math functions in SQL for Microsoft Access, including left, right, mid, trim, ltrim, case conversion, rounding, and financial functions in select and where clauses.

Explore conditional functions in SQL for Microsoft Access, including if, switch, and choose, with examples of immediate versus deferred payments and null handling.

Explore how SQL statements power lookups and data displays across Access objects by linking orders to employees and customers, using lookup fields, forms, and reports.

Join the orders and customers tables on orders.customer_id = customers.id; count each company's orders with group by, and use aliases to simplify inner join queries, noting nonmatching rows are excluded.

Explore how inner, left, right, and outer joins combine customers and orders in Microsoft Access, compute zero orders with counts, and why cross joins are rarely used.

Learn to extend an Access SQL query to three tables by joining orders with customers and employees, using brackets to separate joins and aliases to disambiguate fields.

Explore when to use subforms and subreports in Microsoft Access to display orders with related customers and employees, instead of relying on complex joins in queries.

Master inserting data into an existing table in Microsoft Access using an append query (insert into), preserving an audit trail and avoiding table replacement.

Learn to transform records with update queries in Access by replacing select with update, using set to change fields, applying where conditions, and handling joins and cascading updates.

Filter customers by the three least used job titles with a subquery in the where clause, or use a derived table; both approaches produce the same result in Access SQL.

Learn to create a cross-tab pivot in SQL for Access by transforming the month of order date into columns, grouping by employee, and counting orders as values.

Discover how to add and declare parameters in Access queries to enable interactive input from a form, using the three-part name forms!form input!txtOrderID and parameter types like integer.

Explore how to link Microsoft Access to an external data source like SQL Server, choosing between import and link, and importing the Person.Address table from AdventureWorks.

Create a pass-through query to send SQL Server code from Access through a DSN, allowing server-side processing with the target SQL dialect.

Learn to write sql in microsoft access using the six clauses of the select statement—select, from, where, group by, having, and order by— and apply joins, including inequality joins.