MICROSOFT CERTIFICATION

MO-500 Microsoft Access Expert (Office 2019) Practice Exam

Exam Number: 3147 | Last updated 16-Apr-26 | 600+ questions across 5 vendor-aligned objectives

The MO-500 Microsoft Access Expert (Office 2019) certification validates the skills of advanced users who demonstrate expert-level competency in Microsoft Access 2019 for database management. This exam measures your ability to work with Microsoft Access 2019, Database Design, Queries, Forms, Reports, Macros, VBA, demonstrating both conceptual understanding and practical implementation skills required in today’s enterprise environments.

The heaviest exam domains include Create and Modify Queries (25–30%), Create and Modify Tables (20–25%), and Modify and Manage Databases (15–20%). These areas collectively represent the majority of exam content and require focused preparation across their respective subtopics.

Additional domains tested include Modify Forms in Layout View (15–20%), and Modify Reports in Layout View (15–20%). Together, these areas round out the full exam blueprint and ensure candidates possess well-rounded expertise across the certification scope.

♥  Query creation and modification carry the most weight. Master action queries (append, update, delete), parameter queries, and calculated fields. Understand table relationships and referential integrity.

Every answer links to the source. Each explanation below includes a hyperlink to the exact Microsoft documentation page the question was derived from. PowerKram is the only practice platform with source-verified explanations. Learn about our methodology →

348

practice exam users

94%

satisfied users

93.3%

passed the exam

4.9/5

quality rating

Test your MO-500 Microsoft Access Expert knowledge

10 of 600+ questions

Question #1 - Modify and Manage Databases

A small business owner has an Access database that has grown to 1.5 GB. Performance has degraded with slow form loading and occasional corruption warnings.

Which database maintenance task should be performed first?

A) Upgrade to SQL Server immediately
B) Increase the PC memory
C) Delete old records immediately
D) Compact and Repair the database to reclaim space and fix minor corruption

 

Correct answers: D – Explanation:
Compact and Repair reclaims unused space from deleted records and fixes minor corruption, often resolving performance issues. Deleting records loses data. SQL Server migration is a larger project. More memory helps but does not fix fragmentation or corruption. Source: Check Source

A small business Access database has grown to 1.5 GB with slow forms and occasional corruption warnings.

Which maintenance task should be performed first?

A) Delete old records immediately to reduce the file size without any diagnostic evaluation
B) Compact and Repair the database to reclaim unused space and fix minor corruption issues
C) Upgrade to SQL Server immediately which is a major platform migration requiring planning
D) Increase the PC memory allocation hoping additional RAM resolves the performance degradation

 

Correct answers: B – Explanation:
Compact and Repair reclaims space from deleted records and fixes minor file-level corruption, often resolving performance issues in large Access databases. Deleting records loses potentially important business data without diagnosing whether that is the actual cause. SQL Server migration is a significant project requiring schema redesign, not an immediate maintenance fix. Additional memory may help but does not fix database file fragmentation or internal corruption. Source: Check Source

A nonprofit needs to split their Access database so data tables are on a shared network drive while forms stay local.

Which Access feature accomplishes this?

A) Copy the entire database file to the network drive which creates file-locking conflicts
B) Export all tables to SharePoint which changes the entire platform and data access methodology
C) Use the Database Splitter to create a back-end data file and front-end application file
D) Create linked tables to an Excel spreadsheet which loses relational integrity and SQL capability

 

Correct answers: C – Explanation:
The Database Splitter separates data tables into a shared back-end while forms, queries, and reports remain in a local front-end linking to the shared data. Copying the whole file causes locking conflicts when multiple users try to open the same file simultaneously. Excel linked tables lose referential integrity, relationships, and the full SQL query capability of Access. SharePoint migration changes the entire data platform and may not support complex Access-specific features and queries. Source: Check Source

A school registrar builds an enrollment database. Each student enrolls in multiple courses, and each course has multiple students.

Which table design correctly represents this many-to-many relationship?

A) A junction table with foreign keys to both Students and Courses tables for proper normalization
B) Store course lists as comma-separated text strings in the Students table preventing proper queries
C) Add a multi-value field for courses in the Students table which violates standard normalization
D) Create one combined table merging student and course fields causing massive data redundancy

 

Correct answers: A – Explanation:
A junction table (Enrollments) with foreign keys to both Students and Courses is the standard relational approach enabling proper queries, referential integrity, and normalized data storage. Multi-value fields are non-standard, not portable, and complicate query construction and reporting. Comma-separated text breaks first normal form preventing individual course filtering, counting, or joining operations. Combined tables duplicate student data for every course enrollment creating massive redundancy and update anomalies. Source: Check Source

An inventory database requires unique SKUs and Unit Price values that are never negative.

Which table property configurations enforce these rules?

A) Create a query that filters out invalid records after they have already been entered into tables
B) Train users to enter data correctly trusting human attention to prevent invalid entries
C) Use a form-level validation check only which can be bypassed through direct table data entry
D) Set SKU as Indexed No Duplicates and add a Validation Rule on Unit Price requiring zero or above

 

Correct answers: D – Explanation:
Indexed No Duplicates enforces uniqueness at the engine level, and Validation Rules with expressions like >=0 prevent invalid entries regardless of whether data comes through forms, imports, or direct table editing. User training relies entirely on human discipline without technical enforcement of data rules. Form-level checks can be bypassed by users entering data directly into the table datasheet view. Post-entry query filtering identifies problems after invalid data is already stored rather than preventing it at entry. Source: Check Source

A warehouse needs products where quantity on hand is below reorder point, plus a calculated suggested order quantity.

Which query type and feature should be used?

A) An append query which adds new records to tables rather than filtering existing inventory data
B) A simple select query without any criteria which returns all products regardless of stock level
C) A crosstab query which pivots data into a matrix format rather than filtering by stock thresholds
D) A select query with criteria on OnHand less than ReorderPoint and a calculated field for quantity

 

Correct answers: D – Explanation:
A select query with criteria filters matching records, and a calculated field (ReorderPoint minus OnHand) computes the suggested order quantity dynamically in the result set. Queries without criteria return all products without identifying those below reorder point. Append queries add records to destination tables and are not designed for filtered inventory analysis. Crosstab queries pivot data by row and column headings which does not match the filtered list format needed here. Source: Check Source

An HR manager needs to increase all Marketing department salaries by 5% in a single bulk operation.

Which query type performs this bulk update?

A) A Delete query removing old salary records from the table rather than modifying existing values
B) A Select query displaying salary column values without modifying any of the underlying records
C) An Update query setting Salary to Salary times 1.05 where Department equals Marketing
D) A Make-Table query creating a new table of results without updating the original salary records

 

Correct answers: C – Explanation:
Update queries modify field values in bulk based on criteria conditions. The expression [Salary]*1.05 calculates the new value and applies it to all Marketing department employee records simultaneously. Select queries only display data in a result set without making any changes to the underlying table records. Delete queries remove entire records from tables rather than modifying specific field values within existing records. Make-Table queries create new tables from query results without modifying the data in the original source table. Source: Check Source

A finance department needs total revenue by product category, with categories as columns and months as rows in a pivot format.

Which Access query type produces this pivoted layout?

A) A Select query with GROUP BY producing row-based summaries without the column-pivoted format
B) A Crosstab query using months as rows and categories as columns with Sum of Revenue as values
C) A Union query combining result sets from multiple tables vertically without any pivoting logic
D) A Parameter query accepting user input for filtering without any cross-tabulation capability

 

Correct answers: B – Explanation:
Crosstab queries pivot data into a grid with configurable row headings, column headings, and aggregate values matching the spreadsheet-style summary format described. Select with GROUP BY produces row-based grouped summaries without the column-pivoted cross-tabulation layout. Union queries combine rows from multiple queries or tables vertically without any pivoting or aggregation. Parameter queries accept runtime input for filtering but do not restructure data into a pivoted cross-tabulation format. Source: Check Source

A data entry form needs a dropdown letting clerks select a customer name while storing the CustomerID.

Which form control configuration achieves this?

A) A combo box with Customers as Row Source, CustomerID as Bound Column, and Name displayed
B) A label control showing the customer name as static display text without any selection capability
C) A command button that opens the Customers table for browsing without data selection integration
D) A text box where clerks type customer names freely without validation or lookup functionality

 

Correct answers: A – Explanation:
A combo box with the Customers table as row source displays names for user selection while storing the bound CustomerID value in the underlying order record for referential integrity. Text boxes allow unrestricted free typing without validation against existing customer records or ID association. Labels display static text and do not provide any interactive selection capability for data entry operations. Command buttons trigger actions like opening forms but do not integrate selected values back into the current record. Source: Check Source

A service desk form should auto-display the current date and logged-in username in read-only fields when creating new records.

Which form configuration populates these default values?

A) Use a macro that runs only when the form closes which is too late for data entry context
B) Set Default Value to Date() for date and CurrentUser() for username with Locked property enabled
C) Leave both fields blank expecting users to fill them in when they remember during data entry
D) Ask users to manually type the current date and their name into the fields for every new record

 

Correct answers: B – Explanation:
Default Value expressions auto-populate fields with system values when new records are created, and the Locked property prevents modification ensuring consistent automatic values. Manual typing introduces inconsistent date formats and misspelled usernames across different data entry staff. Blank fields risk missing required audit information when users forget to complete them during busy periods. Form close macros execute after the record is already saved, too late to populate fields needed during the data entry process. Source: Check Source

Get 600+ more questions with source-linked explanations

Every answer traces to the exact Microsoft documentation page — so you learn from the source, not just memorize answers.

Start free 24-hour trial — no credit card

Exam mode & learn mode · Score by objective · Updated 16-Apr-26

Learn more...

What the MO-500 Microsoft Access Expert exam measures

  • Modify and Manage Databases (15–20%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
  • Create and Modify Tables (20–25%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
  • Create and Modify Queries (25–30%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
  • Modify Forms in Layout View (15–20%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.
  • Modify Reports in Layout View (15–20%) — Evaluate your ability to implement and manage tasks within this domain, including real-world job skills and scenario-based problem solving.

  • Review the official exam guide to understand every objective and domain weight before you begin studying
  • Complete the relevant Microsoft Learn learning path to build a structured foundation across all exam topics
  • Get hands-on practice in an Azure free-tier sandbox or trial environment to reinforce what you have studied with real configurations
  • Apply your knowledge through real-world project experience — whether at work, in volunteer roles, or contributing to open-source initiatives
  • Master one objective at a time, starting with the highest-weighted domain to maximize your score potential early
  • Use PowerKram learn mode to study by individual objective and review detailed explanations for every question
  • Switch to PowerKram exam mode to simulate the real test experience with randomized questions and timed conditions

Earning this certification can open doors to several in-demand roles:

Microsoft provides comprehensive free training to prepare for the MO-500 Microsoft Access Expert (Office 2019) exam. Start with the official Microsoft Learn learning path for structured, self-paced modules covering every exam domain. Review the exam study guide for the complete skills outline and recent updates.

Related certifications to explore

MO-200 Excel Associate (Office 2019)
MO-400 Outlook Associate (Office 2019)
MO-111 Word Expert (Microsoft 365 Apps)
Cisco CCNA
AWS Solutions Architect Associate
CompTIA A

Related reading from our Learning Hub

Visit Our Learning Hub
PowerKram vs FocusOnForce
Visit Our Learning Hub
PowerKram vs Exam Topics