Microsoft Access: Access Level 1 & 2 (GUI, Macros, VBA)

Explore how to open and navigate a completed Access table, add and delete records, and switch between datasheet view and design view.

Learn to edit records by selecting a row, deleting or editing fields, undoing mistakes, and navigating with tab, home, end, and arrow keys; add new records.

Master the find and replace feature in Microsoft Access by selecting the field, pressing ctrl h, and replacing all with the full name; use the asterisk wildcard for partial matches.

Create your first table using table design, set customer I.D. as the primary key with auto number to ensure unique values, then add first and last name as short text.

Create and save your first table in Access, name it, and understand automatic primary key numbers that ensure uniqueness, enabling links to related tables like customers and orders.

Learn how to set default values and apply validation rules in Access, using length checks for company names and prices greater than zero to ensure data integrity.

Use lookups to limit field values and prevent data entry errors by converting a field to a combo box with limit to list.

Open the customer table in design view to move, add, or delete fields with mouse actions. Use shift-click to select multiple fields, insert above a field, and drag to reorder.

Move, resize, and align report components in Access by adjusting text inside controls versus moving the control itself, and align data with labels.

Explore unbound controls in Access, especially labels that display without a data link, and learn why simple labels in a report header appear without linking to a table.

Bound controls in Access link a control to a data source, such as a text box with a control source tied to a field like customer id.

Create a professional Access report using the report wizard, add a calculated total (unit price × quantity), and adjust labels, alignment, and borders for a polished layout.

Add a grand total to an Access report using SUM on unit price and quantity, placed in the report footer, formatted as currency, with a clear grand total label.

Learn to sort and group reports in design view, add a group by order number, and set subtotals. Adjust headers and layout options to manage group paging.

Explore creating and designing access queries, select the customers table with all fields using the asterisk, view in data sheet view, and edit field order or deletion in design view.

Learn to apply criteria in queries by filtering for owner and Madrid with the criteria box, using design view and datasheet results to narrow records.

Master wildcards and special characters in access criteria to filter city names, using like, question marks, and asterisks with quotes to match patterns such as M* and ?o*.

Design a query from the December 2019 orders table to use expressions and calculations on order date, unit price, and quantity, with criteria to filter results.

Create on-the-fly calculated fields in Microsoft Access queries using unit price and quantity to compute totals in datasheet view. Compare order date to delivery date without altering base table data.

Learn how to build parameter queries in Access to prompt users for an order date and a minimum unit price, filtering results accordingly.

Learn to create top records queries in Microsoft Access, selecting customer id and quantity, sorted by descending quantity, and using top values or percentages on the December 2019 orders.

Learn to create a cross tab query in Access to display order date by unit price with quantity as the value, a spreadsheet-like data view.

Learn how update queries—an action query—modify multiple records at once in Access, using the product name field to change 'we'll complete kit' to 'complete wheel kit' in the orders table.

Explore the make table query to copy selected product codes into a new table for a new store, design and run the query, and inherit data type and size.

Learn how append queries differ from update and make table queries, and how predefined fields and validation rules improve data integrity, performance, and normalization when adding records.

Learn how forms in Microsoft Access serve as input, enabling the creation of new customers and customer orders, with form controls performing actions beyond mere labeling.

Create a transactions form using the form wizard, selecting all fields from the transactions table with a tabular layout, then add the transactions list to the customer form.

Create a subform to display each customer's transactions by embedding the transactions list into the customer form, linking on customer ID, adjusting layout, and controlling add/delete permissions.

Bridge your spreadsheet mindset to Access by learning that how data is stored is separate from how it is displayed. Discover displaying data in a spreadsheet-like form in Access.

Compare how end users operate Access, running queries, creating reports, and adding records. Developers edit tables, design the relational database with normalization, document actions, and test for end user success.

Leverage the graphical interface in access to avoid coding everything, easing maintenance and future changes. Relying on code increases clutter and manual edits, burdening new developers.

Demonstrate moving the customer field, dependent on order number, from orders table into its own table, with order number as the foreign key, illustrating the 2nd rule for normalized tables.

Learn the third normalization rule: a field depending on another non-key field, like customer address, and use a customer ID for uniqueness.

Place the primary key as the first field. It's usually a single field but can be multiple; use a meaningless value, usually an auto number, to uniquely identify records.

Identify when duplication is necessary over normalization, considering if a field changes over time and whether such changes matter to the business, such as price history or customer addresses.

Learn to transform non normalized spreadsheet style data into normalized tables in Access, using a year field and related data type fields to store records efficiently.

Maintain field type consistency across tables by using the same auto number data type for primary and foreign keys, as shown in design view, to reduce errors.

Explore relationships in access by linking tables through fields like contact I.D., enable referential integrity, and implement cascade updates and deletes to maintain related data.

Explore many-to-many relationships in Access by creating a junction table to link orders and products and also connect authors to titles, maintaining unique keys.

Identify a one-to-many relationship by examining how country id in countries is a primary key, becomes a foreign key in regions, and links regions to cities.