MO-500 Microsoft Access Expert Certification

Import objects or data from other sources into a new Access database by selecting import options for tables, queries, forms, reports, macros, and modules, and save import steps for reuse.

Delete database objects with caution, view object dependencies and referential integrity before deletion, and avoid impacting forms, reports, or queries tied to related tables such as employees and card tables.

Explore how to hide and display objects in the Access navigation pane, filter by object type or creation and modification dates, and customize views with navigation options.

Explore relationships in relational databases, including one-to-one, one-to-many, and many-to-many types. Learn how foreign keys and a junction table model these relations and enforce referential integrity in access.

create and manage one-to-one relationships in access using the relationships canvas; enforce referential integrity with car id as a foreign key, and explore cascade updates and deletes for data consistency.

Understand one-to-many relationships between parent and child tables using customers and orders. Learn about primary and foreign keys, referential integrity, cascade options, and basic join types in Access.

Create and edit one-to-many relationships in access by dragging keys and configuring referential integrity and inner joins. Use lookup fields to display meaningful names while storing IDs for data integrity.

View the database schema in Microsoft Access to examine one-to-many and many-to-many relationships, with primary and foreign keys. See how a junction table models customer and product relationships.

Set a primary key to uniquely identify each record, prevent duplicates and nulls, and choose a single field or composite key, using auto number when appropriate.

Enforce referential integrity when creating relationships by checking the box between related tables, such as orders and products, yielding a one-to-many link; verify enforcement by selecting the relationship line.

Explore how foreign keys connect child and parent tables by referencing primary keys, and ensure compatible data types and field sizes across related tables like customers, products, and orders.

Configure print options for records, forms, and reports in Access, using print preview and export to Excel or PDF to control layout, orientation, and page setup.

Learn how to export Access objects to alternative formats, with a focus on exporting to Microsoft Excel. Use the export wizard to specify the file name, location, and formatting options.

Develops hands-on skills in creating and configuring Access databases: from blank templates, importing Excel and text data, building relationships, creating navigation forms, and exporting and securing data.

Import data from Excel into Access using a wizard that can create a new table, append, or link data, and define fields, primary keys, and data types.

Link to external data sources instead of importing to view data without copying, and learn how linked tables reflect source changes with one-way updates and read-only access.

Import tables from other databases using Access’s data import dialog, then decide whether to import a query as a table or as a query, and configure structure and data options.

Learn to tailor a datasheet in Access by hiding and un-hiding fields, resizing and reordering columns, freezing headers, and saving the datasheet layout.

Add total rows in a datasheet and apply aggregate functions to a column, including average, count, maximum, minimum, standard deviation, and variance, with totals shown from the home tab.

Learn to view and edit a table's description in Access, using the design tab and properties, then save and view it in the navigation pane's detail view.

Open employee table and use the find and replace dialog to specify what to find and replace, then choose to replace one by one or replace all with a warning.

Sort records in the datasheet by the primary key or any field, using ascending or descending orders, including multi-column sorting; remove sorts with the ribbon or right-click.

Apply filters to isolate specific records using criteria and view a subset via the filter icon, without deleting data, with options like equals, does not equal, contains, and between.

Explore how to add and remove fields in Microsoft Access, using design and datasheet views, define data types, set primary keys, and apply QuickStart fields for rapid table creation.

Learn how to add, delete, rename, and reposition fields in Access tables using design and datasheet views, including inserting rows and preserving data integrity.

Apply field and record validation rules to ensure data quality before saving records. Use the expression builder to craft rules, test examples like 100, and enforce date logic.

Configure field properties and learn how to change field captions in design and datasheet views, and understand why field names differ from captions when building queries, forms, or reports.

Learn to change data types and field sizes in Microsoft Access by selecting the data type from the dropdown and adjusting the field size, including text, number, and currency.

Configure auto increment and default values to speed data entry in Access. Set new value to increment or random, and use the expression builder for default dates.

Learn to build and test input masks in Access, including three sections separated by semicolons, placeholders and literals, and uppercase/lowercase transforms to enforce formats like dates and phone numbers.

Develop practical skills in creating a new blank Access database, building tables with primary keys, importing and appending staff list from Excel, and establishing relationships and validations across linked tables.