I85PREP
⌘ K
MOS Access Expert study materials
MOS Access Expert study resources

MOS Access Expert practice questions

One question at a time. Build your MOS Access Expert understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Manage databases

In Access, which object stores the actual data in rows and columns and is the foundation of every query and report?

  1. A table Correct answer.
  2. A form
  3. A report
  4. A macro

Explanation

Tables are the only Access object that physically stores records; queries, forms, and reports merely display or manipulate data drawn from tables. Every other object ultimately depends on a table or a query built on tables.

Question 2Manage databases

You want to enforce that every value in the CustomerEmail field is unique across the table. Which table property do you set?

  1. Set the field's Input Mask to a full mask
  2. Set Indexed to Yes (No Duplicates) on the field Correct answer.
  3. Set the field's Validation Rule to Is Not Null
  4. Set Required to Yes on the field

Explanation

The Indexed property with the No Duplicates setting creates a unique index, which makes Access reject any duplicate entry. Required only forces a value to exist, and a validation rule cannot compare against other rows.

Question 3Manage databases

A query's design grid shows a Total row with Group By under Department and Count under EmployeeID. What does the query return?

  1. A single row counting all employees in the table
  2. Only departments that contain more than one employee
  3. One row per department with the number of employees in each Correct answer.
  4. Every employee record with a running total appended

Explanation

A Totals query groups records by the Group By field and applies the aggregate function within each group. Because Department is grouped, the output has one row per distinct department with a count of its EmployeeID values.

Question 4Manage databases

Which Access feature lets you define a rule such as >0 And <=100 for a numeric field so bad data is rejected at entry time?

  1. Format
  2. Default Value
  3. Validation Rule Correct answer.
  4. Input Mask

Explanation

The Validation Rule property is evaluated when a record is saved and rejects values that fail the expression. Format and Input Mask only affect appearance or keystroke patterns, and Default Value merely prefills a field.

Question 5Manage databases

What is the main purpose of the Relationships window in an Access database?

  1. To define and enforce how tables are joined by primary and foreign keys Correct answer.
  2. To store the database's backup copies
  3. To arrange the order in which forms open at startup
  4. To list the macros that run when a record is saved

Explanation

The Relationships window documents and enforces referential integrity between tables by linking primary keys to foreign keys. Those joins drive default query behavior and prevent orphaned records.

Question 6Manage databases

You need to combine the results of two queries that share the same number and order of columns, keeping duplicate rows. Which operator do you use?

  1. GROUP BY
  2. INNER JOIN
  3. DISTINCT
  4. UNION ALL Correct answer.

Explanation

UNION ALL appends the rows of one query to another and retains duplicates, provided the column counts and data types align. Plain UNION would remove duplicate rows, and the other choices serve different purposes.

Question 7Manage databases

A table has a lookup field that stores the numeric DepartmentID but displays the department name. What is actually saved in the record?

  1. A pointer to the lookup table rather than any stored value
  2. Both the number and the name, stored in two hidden columns
  3. The department name as text, with the number shown only on screen
  4. The bound numeric value, with the name supplied from the lookup source Correct answer.

Explanation

A lookup field's bound column is what gets stored; the display column is only presented to the user. This is why changing a lookup's display source does not alter existing stored values.

Question 8Manage databases

You want the same query to return records for a customer name typed by the user each time it runs. Which criterion achieves this?

  1. "Enter customer name:"
  2. Like "*"
  3. Is Not Null
  4. [Enter customer name:] Correct answer.

Explanation

Square brackets in a criterion define a parameter prompt, so Access asks for the value at run time and substitutes it into the query. Quotation marks would compare against that literal text instead of prompting.

Question 9Manage databases

A database must reject a new order record whose CustomerID does not exist in the Customers table. Which setting enforces this?

  1. Referential integrity with the relationship enforced Correct answer.
  2. An input mask on the CustomerID field
  3. A validation rule on the OrderDate field
  4. A default value on the CustomerID field

Explanation

Enforcing referential integrity on the relationship prevents a foreign key value that has no matching primary key in the parent table. Field-level rules and masks cannot check against another table.

Question 10Manage databases

Which action best compacts and repairs an Access database file?

  1. Use the Compact and Repair Database command on the file Correct answer.
  2. Delete the Navigation Pane groups and rebuild them
  3. Rename the .accdb file and reopen it
  4. Export every table to Excel and reimport it

Explanation

Compact and Repair rebuilds the file, reclaiming space from deleted objects and fixing minor corruption. Renaming or round-tripping data through Excel does not perform the same structural cleanup.

Question 11Manage databases

You want a query to show only products whose Price is above the average price of all products. Which approach works?

  1. Use a subquery in the criteria that returns the average Price Correct answer.
  2. Group by Price and apply Count in the Total row
  3. Sort by Price descending and read the top rows
  4. Type the current average price as a fixed number in the criteria

Explanation

A subquery such as >(SELECT Avg([Price]) FROM Products) recalculates the comparison value each time the query runs. A hard-coded number becomes stale as soon as prices change.

Question 12Manage databases

What does the Cascade Delete Related Records option on a relationship do?

  1. Deletes matching child records when the parent record is deleted Correct answer.
  2. Copies deleted parent records to a backup table
  3. Deletes the parent record whenever any child record is removed
  4. Prevents deletion of any parent that has child records

Explanation

With cascade delete enabled, removing a parent row automatically removes the child rows that reference it, preserving referential integrity. Without it, deletion of a referenced parent is blocked.

Question 13Manage databases

A query groups orders by OrderMonth and sums OrderTotal. You want only months whose summed total exceeds 10000. Where does the condition belong?

  1. In the criteria row under OrderMonth before grouping
  2. In the criteria row under the summed field using HAVING semantics Correct answer.
  3. In the query's Order By property
  4. As a filter on the OrderTotal field before the sum is computed

Explanation

Conditions on aggregate results are applied after grouping, which in SQL is the HAVING clause. Filtering individual rows before grouping would exclude records from the sum and distort the totals.

Question 14Manage databases

Which field data type is the best choice for storing a monetary amount that must be calculated without rounding errors?

  1. Number with Field Size Double
  2. Currency Correct answer.
  3. Short Text
  4. Yes/No

Explanation

The Currency type uses fixed-point arithmetic and avoids the floating-point rounding drift that Double can introduce in money calculations. Text and Yes/No cannot hold monetary values meaningfully.

Question 15Manage databases

A report and several forms all need the same filtered set of active customers. What is the most maintainable design?

  1. Apply the filter manually each time an object opens
  2. Store the filtered rows in a separate copy of the table
  3. Create one saved query for active customers and base the objects on it Correct answer.
  4. Embed the filter separately in each form and report

Explanation

Centralizing the logic in a saved query means a single edit propagates to every dependent form and report. Duplicating filters or data copies invites inconsistency and extra maintenance.

Question 16Manage databases

What is the effect of setting a table field's Required property to Yes while leaving Allow Zero Length at No?

  1. The field is hidden from datasheet view until filled
  2. The field accepts an empty string but rejects Null
  3. A value must be entered, and an empty string is not accepted Correct answer.
  4. The field may stay blank but cannot contain an empty string

Explanation

Required blocks Null entries, and with Allow Zero Length set to No an empty string is also rejected, so a real value must be supplied. The two properties work together to control blanks.

Question 17Manage databases

You need a query that lists customers who have placed no orders. Which technique returns them?

  1. An outer join from Customers to Orders with OrderID Is Null as the criterion Correct answer.
  2. An inner join between Customers and Orders
  3. A parameter query prompting for a customer name
  4. A totals query counting OrderID per customer

Explanation

An outer join keeps customers with no matching order rows, and testing the child key for Null isolates exactly those unmatched records. An inner join would silently drop them.

Question 18Manage databases

Which statement about the difference between the WHERE and HAVING clauses is correct?

  1. Both clauses filter rows at the same stage of processing
  2. HAVING can only be used with the DISTINCT keyword
  3. WHERE filters rows before grouping, while HAVING filters groups after aggregation Correct answer.
  4. WHERE filters groups and HAVING filters individual rows

Explanation

SQL evaluates WHERE against individual rows before grouping, then forms groups and applies HAVING to the aggregated results. This ordering is why aggregate functions cannot appear in WHERE.

Question 19Manage databases

You want to enforce referential integrity between two tables whose related fields have different data types. What happens?

  1. Access creates the relationship but silently drops the index
  2. Access creates the relationship and converts values automatically
  3. Access refuses to create the relationship until the field types match Correct answer.
  4. Access creates the relationship only if cascade delete is enabled

Explanation

Referential integrity requires compatible field types on both sides of the join; mismatched types prevent the relationship from being saved. Correcting the data type first is the standard fix.

Question 20Manage databases

Which change most improves performance of a query that filters and sorts on a large table's OrderDate field?

  1. Change OrderDate to Short Text
  2. Add an input mask to OrderDate
  3. Set a default value for OrderDate
  4. Create an index on OrderDate Correct answer.

Explanation

An index on a frequently filtered and sorted column lets the engine locate and order rows without a full scan. The other changes affect entry behavior or data integrity, not retrieval speed.

Question 21Manage databases

A database uses a split design. Which file contains the tables while users work in a separate front-end copy?

  1. An exported Excel workbook
  2. The back-end database file Correct answer.
  3. A temporary .laccdb lock file
  4. The front-end database file

Explanation

In a split database the back-end holds the shared tables, and each user runs a front-end copy containing queries, forms, and reports linked to those tables. The lock file only tracks active users.

Question 22Manage databases

Which object would you open to change the startup form that appears when the database launches?

  1. Access Options, Current Database settings Correct answer.
  2. The Compact and Repair dialog
  3. The Relationships window
  4. The Linked Table Manager

Explanation

The display form and other launch behavior are configured under Access Options on the Current Database tab. The other dialogs manage schema, file maintenance, or linked data sources.

Question 23Manage databases

What does the Linked Table Manager let you do?

  1. Encrypt the back-end database
  2. Convert local tables into queries
  3. Define primary keys on linked tables
  4. Update the file paths of tables linked to external data sources Correct answer.

Explanation

When an external source moves, the Linked Table Manager relinks the tables by pointing them to the new location. It does not alter table structure or encryption.

Question 24Manage databases

A field's Validation Text property is set alongside a Validation Rule. What is its role?

  1. It stores a backup copy of the entered value
  2. It defines the format used to display the field
  3. It supplies the message shown when the rule is violated Correct answer.
  4. It sets the default entry for new records

Explanation

Validation Text customizes the error message users see when their entry fails the Validation Rule. It does not affect storage, display formatting, or default values.

Question 25Manage databases

Which query property would you set to limit a select query to the first 25 records returned?

  1. Output All Fields
  2. Top Values Correct answer.
  3. Unique Values
  4. Filter On Load

Explanation

The Top Values property caps the number of rows a query returns, optionally as a percentage. Unique Values removes duplicates, and the other properties control field output or form filtering.

Question 26Manage databases

You are preparing a database for distribution and want to reduce its file size while also checking the Access file for structural corruption. Which command accomplishes both goals in a single operation?

  1. Database Documenter
  2. Back Up Database
  3. Save As with the .accdb file type
  4. Compact and Repair Database Correct answer.

Explanation

The Compact and Repair Database command rebuilds the database file, removing unused space left by deleted objects and records, and it also scans the file for corruption during the rebuild. It is the standard maintenance command found on the Database Tools tab.

Question 27Manage databases

You want a query to return only the top 10 percent of orders by OrderTotal. Where do you configure this in query Design view?

  1. In the OrderTotal field's Total row, set to Percent
  2. In the query's Recordset Type property, set to Snapshot
  3. In the OrderTotal field's criteria row, typed as Top 10%
  4. In the query's Top Values property, set to 10% Correct answer.

Explanation

The Top Values property on the query (shown on the Design tab as the Top Values box) accepts a number or a percentage such as 10%. You combine it with a descending sort on OrderTotal so the highest-valued orders are the ones returned.

Question 28Manage databases

A table contains a field named Email that may be blank for some contacts. You need a query that returns every record where Email has been entered, regardless of its value. Which criterion on the Email field achieves this?

  1. Is Null
  2. Not ""
  3. Like "*"
  4. Is Not Null Correct answer.

Explanation

Is Not Null tests whether the field contains any value at all, so it returns every record where Email has been populated. This correctly treats a blank field as excluded whether it holds a Null or, in the stricter sense, you can combine it with a check for the empty string.

Question 29Manage databases

You need to add a field to an existing table that automatically stores the date and time each record was created, without the user typing anything. Which approach meets this requirement?

  1. Add a Date/Time field and set its Format to General Date
  2. Add a Date/Time field and set its Input Mask to a date pattern
  3. Add a Date/Time field and set its Default Value to Now() Correct answer.
  4. Add a Calculated field whose expression is Date()

Explanation

Setting the Default Value property to Now() populates the field automatically with the current date and time whenever a new record is created, and the user never needs to enter it. Now() includes the time portion, unlike Date(), which returns only the date.

Question 30Manage databases

A database has a one-to-many relationship between Customers and Orders with referential integrity enforced. A user tries to delete a customer who has existing orders while Cascade Delete Related Records is turned off. What is the result?

  1. Access blocks the deletion and leaves both the customer and the related orders intact Correct answer.
  2. Access deletes the related orders first and then blocks the customer deletion
  3. Access deletes the customer and all of the related orders
  4. Access deletes the customer and leaves the related orders orphaned

Explanation

With referential integrity enforced and Cascade Delete Related Records cleared, Access will not allow a parent record to be deleted while matching child records exist. The delete is rejected, so neither the customer nor the orders are removed.

Question 31Create and modify tables

In Access table Design View, which data type should you assign to a field that will store whole numbers used in arithmetic calculations and should be kept as small as possible?

  1. Attachment
  2. Number with a Field Size of Byte Correct answer.
  3. Currency
  4. Short Text

Explanation

The Number data type with a Byte field size stores whole numbers from 0 to 255 in a single byte, which is the most compact choice for small integer values. Short Text stores characters, Currency adds fixed-point precision for money, and Attachment stores files, so none of them suit small arithmetic integers.

Question 32Create and modify tables

You are designing a table and want Access to automatically enter the current date each time a new record is added, without the user typing it. Which field property setting accomplishes this?

  1. Required property set to Yes
  2. Input Mask set to a date pattern
  3. Validation Rule set to Date()
  4. Default Value set to Date() Correct answer.

Explanation

The Default Value property supplies a value automatically when a new record is created, so entering Date() there stamps the current date on each new row. Validation Rule only checks what the user types, an Input Mask only guides entry, and Required merely forbids blanks.

Question 33Create and modify tables

Which key type should you use when a table has no natural unique identifier and you want Access to generate sequential or random values automatically?

  1. A foreign key referencing another table
  2. An AutoNumber primary key Correct answer.
  3. A composite primary key of two text fields
  4. A primary key built on a Date/Time field

Explanation

AutoNumber generates a unique value for each new record automatically, making it the standard surrogate key when no natural key exists. Composite text keys, foreign keys, and date fields either require manual entry or are not guaranteed unique per record.

Question 34Create and modify tables

In table Design View, which property do you set to display a friendly caption in Datasheet View instead of the underlying field name?

  1. Format
  2. Description
  3. Caption Correct answer.
  4. Input Mask

Explanation

The Caption property controls the column heading shown in Datasheet View, forms, and reports while the actual field name stays unchanged in the design. Format affects how values display, Description appears in the status bar, and Input Mask controls entry patterns.

Question 35Create and modify tables

Which lookup property do you configure so that users pick a value from a list of choices rather than typing free text?

  1. The Lookup Wizard to create a combo box with a value list Correct answer.
  2. The Validation Text property
  3. The Indexed property
  4. The Unicode Compression property

Explanation

The Lookup Wizard creates a lookup field displayed as a combo box, letting users select from a list of values or a related table. Validation Text only supplies an error message, Indexed affects search performance, and Unicode Compression affects storage.

Question 36Create and modify tables

What is the main purpose of defining a primary key on an Access table?

  1. To sort records automatically in ascending order
  2. To uniquely identify each record and prevent duplicate or null values in that field Correct answer.
  3. To compress the database file size
  4. To hide sensitive fields from Datasheet View

Explanation

A primary key enforces uniqueness and forbids nulls, giving every row a reliable identifier that relationships and indexes rely on. It does not control sort order, file size, or field visibility.

Question 37Create and modify tables

You want to require that every record in a table has a value entered in the Email field. Which field property should you set to Yes?

  1. Format
  2. Indexed
  3. Allow Zero Length
  4. Required Correct answer.

Explanation

Setting Required to Yes prevents a record from being saved when that field is left blank. Allow Zero Length permits empty strings, Indexed builds a search index, and Format only changes display appearance.

Question 38Create and modify tables

Which data type should you choose for a field that stores hyperlinks to web pages so that users can click them from Datasheet View?

  1. Hyperlink Correct answer.
  2. OLE Object
  3. Short Text
  4. Long Text

Explanation

The Hyperlink data type stores a URL or path and renders it as a clickable link in Datasheet View. Short Text and Long Text store plain characters, and OLE Object stores embedded binary content.

Question 39Create and modify tables

You need to enforce that an OrderDate field is never earlier than January 1, 2020. Which property and expression accomplish this?

  1. Input Mask set to 00/00/0000
  2. Validation Rule set to >=#1/1/2020# Correct answer.
  3. Default Value set to #1/1/2020#
  4. Format set to Short Date

Explanation

The Validation Rule property accepts an expression that each entry must satisfy, so >=#1/1/2020# rejects any earlier date. Default Value only pre-fills a value, Input Mask shapes entry characters, and Format controls display only.

Question 40Create and modify tables

A table stores a State field, and you want users to choose from a fixed list of two-letter abbreviations stored with the table definition. Which approach is most appropriate?

  1. A Validation Rule of Is Not Null
  2. A lookup field whose Row Source Type is Value List Correct answer.
  3. A lookup field whose Row Source Type is Table/Query
  4. An Input Mask of LL

Explanation

A Value List row source stores the fixed choices directly in the field's lookup properties, which suits a small static set such as state abbreviations. A Table/Query source is better for lists maintained in another table, an Input Mask only restricts characters, and Is Not Null only requires an entry.

Question 41Create and modify tables

You want to ensure that a combination of FirstName and LastName plus HireDate is unique across the table, while still allowing an AutoNumber primary key. What should you create?

  1. A validation rule comparing the three fields
  2. A multiple-field unique index on the three fields Correct answer.
  3. Three separate single-field indexes
  4. A composite primary key on all three fields

Explanation

A multiple-field index with its Unique property set to Yes enforces uniqueness of the combined values while leaving the AutoNumber as the primary key. A composite primary key would replace the surrogate key, separate indexes only enforce per-field uniqueness, and validation rules cannot easily guarantee cross-record uniqueness.

Question 42Create and modify tables

A Contacts table must relate to a Calls table so that one contact can have many calls. Which design correctly implements this one-to-many relationship?

  1. Create a composite primary key of ContactID and CallID in both tables
  2. Put a CallID foreign key in the Contacts table and create the relationship
  3. Store the call details in a multi-valued field inside Contacts
  4. Put a ContactID foreign key in the Calls table and create the relationship in the Relationships window Correct answer.

Explanation

In a one-to-many relationship the primary key of the one side (Contacts) is stored as a foreign key in the many side (Calls), and the relationship is defined in the Relationships window. Reversing the foreign key, duplicating composite keys, or embedding data in a multi-valued field does not model the relationship correctly.

Question 43Create and modify tables

You want Access to automatically delete related child records when a parent record is deleted. Which relationship option must be enabled?

  1. Enforce Referential Integrity only
  2. Join Type set to Include ALL records from the parent table
  3. Cascade Delete Related Records Correct answer.
  4. Cascade Update Related Fields

Explanation

Cascade Delete Related Records removes matching child rows when the parent row is deleted, provided referential integrity is enforced. Cascade Update only propagates key changes, referential integrity alone blocks deletion of referenced parents, and the join type affects query results rather than delete behavior.

Question 44Create and modify tables

You are importing a CSV file into Access and want to control data types during the import rather than accepting defaults. Which approach gives you that control?

  1. Use the Linked Table Manager to link the CSV
  2. Use External Data and paste the CSV into Datasheet View
  3. Use the Compact and Repair Database command
  4. Use the Import Text Wizard and specify field data types on the wizard pages Correct answer.

Explanation

The Import Text Wizard lets you choose delimiters, field names, and data types for each imported column before the table is created. Pasting, linking, or compacting does not give per-field type control during import.

Question 45Create and modify tables

You need to change an existing Short Text field to Long Text because stored notes exceed the character limit. What is the most reliable method in table Design View?

  1. Delete the field and recreate it with the Long Text type
  2. Change the Format property to Long Text
  3. Change the Data Type property of the field to Long Text and save the table Correct answer.
  4. Add a new field and copy values manually into it

Explanation

Changing the Data Type property in Design View converts the field in place and preserves existing values, which is the correct approach. Deleting and recreating or adding a new field risks data loss, and Format only affects display.

Question 46Create and modify tables

A table stores product prices, and calculations must avoid floating-point rounding errors for currency. Which data type is designed for this purpose?

  1. Single
  2. Double
  3. Currency Correct answer.
  4. Short Text

Explanation

The Currency data type stores fixed-point values with four decimal places, avoiding the binary floating-point rounding that affects Single and Double. Short Text cannot be used for arithmetic reliably.

Question 47Create and modify tables

You want a field to accept only values between 1 and 100 and to display a custom error message when the rule is violated. Which two properties must you set?

  1. Input Mask and Caption
  2. Indexed and Required
  3. Validation Rule and Validation Text Correct answer.
  4. Default Value and Format

Explanation

Validation Rule holds the expression such as Between 1 And 100, and Validation Text supplies the message shown when the rule fails. Default Value, Format, Input Mask, and Caption do not enforce range limits.

Question 48Create and modify tables

You need to add an index to speed up searches on a LastName field that allows duplicates. Which Indexed setting is appropriate?

  1. Yes (No Duplicates)
  2. Primary key
  3. Yes (Duplicates OK) Correct answer.
  4. No

Explanation

Yes (Duplicates OK) builds a non-unique index that speeds searches while permitting repeated values, which matches a LastName column. Yes (No Duplicates) would reject common surnames, No disables the index, and Primary key is a separate designation.

Question 49Create and modify tables

You want to link an Access table to data in an Excel workbook so the data stays live without being copied into the database. Which command should you use?

  1. Create a query with a Crosstab
  2. Paste the worksheet into a new table
  3. Link to the Excel workbook using the External Data tab Correct answer.
  4. Import Excel Spreadsheet

Explanation

Linking creates an external table that reads from the workbook, so changes in Excel appear in Access without re-importing. Importing copies the data, pasting creates static data, and a crosstab query summarizes existing data.

Question 50Create and modify tables

A table contains a multi-valued lookup field listing skills for each employee. Which statement about querying that field is accurate?

  1. The field cannot be used in any query criteria
  2. The field can only be filtered in Datasheet View, never in a query
  3. A query on the field returns one row per selected skill value because Access expands the multi-valued field Correct answer.
  4. Each record can hold exactly one skill value

Explanation

Access stores multi-valued fields so that queries can expand them into one row per value, allowing filtering and joins on individual selections. The field can be queried and can hold multiple values per record.