77-730: Microsoft Access - from beginner to Specialist

Open the Northwind database template in Access, explore forms, orders, invoices, and inventory, and preview tables, queries, and reports as a practical foundation for database management.

Open file > new to explore invoicing templates. Templates generate tables, queries, forms, reports, dashboards, and sample data to jumpstart your database design.

Practice activity demonstrates creating a time card database from the time card template in Microsoft Access, enabling content, and exploring menus and the navigation pane in Access 2016 and 2010.

Create a shopping list table in Access with item, date ordered, and quantity fields; fix column order, add an ID, then copy data and save as Shopping List.

Create a form from the customer table and explore form views in Access; switch between layout, form, and design views to edit data and save changes.

Explore creating Microsoft Access forms, including blank forms, data sheets, and form wizard layouts, and learn to base them on tables or queries, select fields, and apply styles.

Learn to create a query with the wizard by selecting fields from the customers table, choosing detail or summary, and using summary options such as count, min, and max.

Practice activity 3 solution shows creating a shopping list query from table, saving it as Shopping List Query, form and a report grouped by date ordered, illustrating Access’s four components.

Learn to use find and replace in Access tables, choosing current field or entire table, match options, and applying replace or replace all in the home tab, with undo cautions.

Learn to use advanced filter and sort in Microsoft Access, including filter by form, wild cards with like, and Boolean logic to refine queries on names and birth date.

Practice activity filters the shopping list by the date range of 3 March 2018 to 5 May 2018, then applies default values and validation rules for quantity and date ordered.

Apply input masks in Microsoft Access to enforce correct data entry in forms, using placeholders like 0 and 9, and compare this with validation rules for live validation.

Learn how to add lookup values in Access by using value lists, tables, or queries to power drop-downs, list boxes, and combo boxes for constrained country data.

Move the date ordered field in design view, convert quantity from integer to decimal with precision and scale, then verify a 2.25 entry in the datasheet.

Discover principles of good table design by introducing the first three normal forms, focusing on simplifying databases, reducing redundancy, and maintaining data integrity.

Explore first normal form by ensuring each field has one indivisible value, eliminating repeating groups, and organizing data into linked tables with primary keys and composite primary keys.

This lecture explains second normal form as avoiding non-key fields that depend on part of a composite primary key, illustrated by moving item data to an item table.

Explore how third normal form eliminates transitive dependencies by isolating size type from item data, creating a bike size table; ensure non-primary key fields depend only on non-primary keys.

Import excel data into access and add customer address, sales order header, detail, and product tables; create one-to-many relationships, enforce referential integrity, and apply cascade updates.

Explore how the where clause filters records and how group by aggregates by country or city, including excluding Hull and counting address IDs.

Learn to set filtering criteria in Access, showing records with three or greater counts and cities up to Hull. Use same-line criteria and apply date ranges with max date aggregation.

Learn to group by country and count records where address line two is null or blank, using is null and is not null in Access.

Practice modifying select queries and grouping in Microsoft Access by counting customers per salesperson on the customer table, with a modified date filter and format as MMM YY.

Explore date/time expressions in Access built-in functions, extracting year, month, day, hour, minute, second, and weekday; calculate date differences; and use DateSerial, DateValue, and now for dynamic dates.

Explore math and text functions in Access, including int for rounding, left, right, mid, trim, l and r trim, replace, lowercase and uppercase, format, len, and instra for string search.

Practice activity 8 builds an access query on the product table to compute max StandardCost by colour with group by and uses iif to separate IDs below and above 800.