Microsoft Office Specialist: Access Expert (Office 2019) — Practice Set 1

Question 1

You are working in Access 2019 with a database that has grown to include dozens of tables, queries, forms, and reports. You want to temporarily hide several older, unused query objects from the Navigation Pane without deleting them, so the pane is easier to scan. What should you do?

Accepted Answer

Right-clicking an object in the Navigation Pane and choosing 'Hide in this Group' hides it from view while keeping it in the database; hidden objects can be shown again via Navigation Pane options (Show Hidden Objects). Deleting (B) permanently removes the object. Renaming with an underscore (C) has no hiding effect in Access. Exporting to another file (D) removes it from the current database entirely, which is not what was asked.

Question 2

You are building a new Orders database in Access 2019. A Customers table already exists in an external Access database file maintained by another department, and you need your Orders table to always reflect live edits made in that external file without copying the data locally. What should you do?

Accepted Answer

Creating a linked table keeps the data physically stored in the source database while displaying and allowing interaction with it from your database — edits in the source are reflected live. Importing (B) copies a snapshot of the data at that moment, so it will not reflect subsequent edits. Manual copy/paste (C) also produces a static, disconnected copy. Exporting your own table (D) does not bring in the Customers data at all.

Question 3

You are reviewing the relationships in a Sales database. An Orders table has a CustomerID foreign key referencing the Customers table's primary key, and you need every order to always reference a valid, existing customer — deleting a customer that still has orders should be blocked rather than silently orphaning the orders. Which two actions correctly achieve this? Choose 2.

Accepted Answer

Enforcing referential integrity on the relationship prevents entering a CustomerID in Orders that does not exist in Customers, and — critically — prevents deleting a Customers record that still has related Orders records unless Cascade Delete is enabled. Leaving Cascade Delete unchecked means Access blocks the deletion instead of silently removing related orders. Setting CustomerID as a primary key in Orders (C) is incorrect since it is a foreign key there, not the primary key. Deleting the relationship (D) removes integrity checking entirely. Changing the data type (E) does not affect referential integrity enforcement.

Question 4

A department head asks you to hand out a printed copy of the current month's Invoices report, but formatted so only the invoice number, date, and total columns print on a single page width, without printing internal notes fields visible in the underlying table. Where should you configure this before printing?

Accepted Answer

Reports are the correct object for controlling exactly what is printed and how, since a report's design (not the underlying table) determines which fields display and print, and Print Preview > Page Setup lets you configure margins, columns, and page options for that report specifically. Printing directly from the table (B) would show all table fields including notes, not the curated report layout. Exporting to Excel (C) is an unnecessary detour for something the report already controls. Format Painter (D) copies formatting between controls, not print layout.

Question 5

You need to bring in a one-time snapshot of last year's product data from an Excel workbook into a new Access table, and you will not need the two systems to stay connected afterward. What is the most direct approach?

Accepted Answer

Importing copies the Excel data into a new native Access table, exactly matching the 'one-time snapshot, no ongoing connection' requirement. Linking (B) is for when you want the table to always reflect the live Excel file, which is the opposite of what's needed here. Manual retyping (C) is inefficient and error-prone compared to the built-in Import Spreadsheet Wizard. Access cannot open an Excel file directly as a native table (D).

Question 6

Your organization's inventory system stores its master Products table in a separate Access database that IT updates nightly. You need your local database's queries and reports to always read the current Products data from that external file without duplicating or periodically re-importing it. What should you create?

Accepted Answer

A linked table connects to the external table so Access always reads current, live data from the source without copying it locally, satisfying the 'always current, no duplication' requirement. Scheduled or manual daily imports (B, C) create stale, duplicated data between refreshes. Access cannot query a table by referencing a file path as plain text within a query (D) — it must be linked as a proper table object first.

Question 7

You are consolidating records from three regional Access databases (each with an identically structured Customers table) into one central database. You want to bring the actual table structures and data from those other Access files into your current database as new tables. What should you use?

Accepted Answer

The Import Access Database wizard, reached via External Data > New Data Source > From Database > Access, lets you select and import specific tables (and other objects) directly from another Access database file into the current one. Manually retyping data (B) or routing through Excel (C) is unnecessary and error-prone when a native Access-to-Access import path exists. Compact and Repair (D) is a maintenance operation and does not import data.

Question 8

A colleague who occasionally works in your Employees table finds the SalaryGrade and InternalNotes columns confusing to view but should not have them deleted since other users and processes rely on them. You want those two fields to simply not display when the table is opened in Datasheet view, without affecting any queries, forms, or reports built on the table. What should you do?

Accepted Answer

Hide Fields in Datasheet view visually hides selected columns from that table's datasheet display while the underlying data, and any queries/forms/reports that reference those fields, remain completely unaffected; the columns can be restored with Unhide Fields. Deleting the fields (B) would break dependent objects and lose data. Changing the data type (C) or the Required property (D) does not hide a column and would alter how the field behaves elsewhere.

Question 9

Your manager wants a quick running total of order amounts visible at the bottom of the Orders table when it is opened in Datasheet view, without building a separate query or report. What should you add?

Accepted Answer

The Total Row feature (Home > Records > Totals) adds a row at the bottom of Datasheet view where you can choose an aggregate function like Sum for a numeric column, giving an immediate, no-design-required total. Adding a per-record calculated field (B) does not produce a single summary total and clutters the data. Building a report (C) or a macro-driven form control (D) is unnecessary extra design work for a simple datasheet total.

Question 10

Six months from now, a new database administrator will inherit your Projects table and needs to understand, at a glance from Access itself, what the table is used for and any special handling notes — without opening separate documentation. What should you do?

Accepted Answer

Access lets you set a Description property directly on a table object (visible in the Navigation Pane's Details view and in table properties), which is the built-in, in-application way to document a table's purpose for future users. Renaming with a long name (B) is impractical and doesn't explain handling notes. Adding a Notes field to every record (C) duplicates the same text unnecessarily across rows and isn't metadata about the table itself. An external document (D) is not accessible from within Access.

Question 11

You are redesigning the Customers table. You need the CustomerID field to automatically generate a new unique sequential number for each new record, and you need a PhoneNumber field to always guide data entry into the pattern (999) 000-0000 without users needing to type parentheses or the dash themselves. Which two actions correctly achieve this? Choose 2.

Accepted Answer

Setting a field's data type to AutoNumber makes Access automatically assign the next sequential unique value for each new record, exactly matching the auto-increment requirement. Applying the built-in Phone Number input mask formats entry into the (999) 000-0000 pattern and inserts the literal parentheses/dash characters automatically as the user types digits. Default Value with =Now() (C) inserts the current date/time, not a sequential number. Setting PhoneNumber to Number type (D) would reject formatting characters and is inappropriate for phone numbers. 'AutoIncrement' (E) is not a valid Validation Rule value.

Question 12

You are refining the Employees table. The HireDate field must never accept a date later than today's date (no future hire dates), and if someone tries to enter one, Access should show the message 'Hire date cannot be in the future.' What should you configure on the HireDate field?

Accepted Answer

The Validation Rule property lets you define a condition a field's value must satisfy (here, <=Date() restricts entries to today or earlier), and the paired Validation Text property defines the exact message Access displays when that rule is violated. An Input Mask (B) only controls the entry pattern/format, not an upper-bound date restriction, and Caption just relabels the field, it does not show as an error message. Default Value (C) only pre-fills a value and does not block a manually entered future date. Field Size and Format (D) control storage/display, not validation.

Question 13

You need a query that lists every order from the Orders table where the OrderAmount exceeds $500, showing only the OrderID, CustomerID, and OrderAmount fields. What is the most direct way to build this?

Accepted Answer

A simple select query built in Query Design view, with the needed fields added and a criterion like >500 typed under OrderAmount, is the direct way to filter and display specific fields meeting a condition. A crosstab query (B) is for summarizing/pivoting data by two dimensions, not a simple filtered list. An action query (C) modifies data rather than displaying it. A parameter query (D) is for prompting user input, which isn't needed for a fixed threshold like 500.

Question 14

Your manager wants a summary grid showing total SalesAmount with SalesRep names down the left side as rows and Quarter (Q1-Q4) across the top as column headings, with each cell showing the summed amount for that rep/quarter combination. Which query type should you create?

Accepted Answer

A crosstab query is specifically designed to summarize data in a spreadsheet-like grid with one field as row headings, another as column headings, and an aggregate (like Sum) as the values at each intersection — exactly matching a rep-by-quarter summary grid. A simple select query (B) cannot pivot data into that grid shape. A delete query (C) removes records rather than summarizing them. A make-table query (D) creates a new table from query results but does not perform the row/column pivot needed here.

Question 15

Customer service staff need to look up a customer's outstanding balance on demand by typing in any CustomerID at the time they run the query, rather than the query always being hard-coded to one customer. What should you build?

Accepted Answer

A parameter query uses a criterion written in square brackets, such as [Enter CustomerID:], which prompts the user for a value each time the query runs, letting staff look up any customer on demand. A crosstab query (B) pivots data into a grid and does not prompt for a single lookup value in this way. A fixed criterion (C) hard-codes one customer and defeats the on-demand requirement. An append query (D) copies matching rows into another table rather than displaying results interactively.

Question 16

You built a query joining the Orders and OrderDetails tables to list every order line item, but the OrderStatus field is not needed in the results, and the results should be sorted by OrderDate from newest to oldest, filtered to only orders placed in the current year. Which two actions correctly finish this query in Design view? Choose 2.

Accepted Answer

Unchecking the Show checkbox hides a field from the result set while keeping it available in the query grid (useful if it's still needed for a criterion elsewhere) — this satisfies 'not needed in the results' without necessarily removing the column. Setting Sort to Descending on OrderDate and adding a Year()-based criterion satisfies both the newest-to-oldest ordering and current-year filter. Deleting the column entirely (C) also hides it, but the question asks for the field to remain available for filtering by status, so unchecking Show is more correct than deleting. Ascending sort (D) would order oldest to newest, which is the opposite of what was asked. Adding a Count total (E) turns this into a summary query and would remove the individual line-item detail rows the question requires.

Question 17

You need a query pulling matching fields from both the Orders table and the Customers table (joined on CustomerID) so a single result set shows each order alongside its customer's name and city. What kind of query should you create?

Accepted Answer

A multi-table query in Query Design view lets you add both tables, have Access use the existing relationship (or create a join) on the common CustomerID field, and pull fields from both tables into one combined result set. Two separate single-table queries (B) would not combine the data into one row set as requested. A crosstab query (C) summarizes rather than lists individual order/customer details. An update query (D) modifies data rather than retrieving a combined view.

Question 18

While viewing the CustomerForm in Layout view, you notice the Email text box control is positioned oddly and needs to move slightly to align with the Phone control above it. What is the correct approach in Layout view?

Accepted Answer

Layout view is specifically designed to let you select and reposition (or resize) form controls such as text boxes while seeing actual live data, making fine alignment adjustments easy and visual. Datasheet view (B) does not apply to individual form control positioning. Report view (C) is for reports, not forms. Deleting and rebuilding the entire form (D) is a drastic, unnecessary overreaction to a minor alignment issue.

Question 19

On the OrderForm, data entry staff currently must press Tab several times through unrelated fields (like an internal audit ID they never touch) before reaching the CustomerID and OrderDate fields they fill in first. You want pressing Tab to move focus directly through CustomerID, then OrderDate, then Notes, in that order, skipping the audit fields entirely during data entry navigation. What should you configure?

Accepted Answer

The Tab Order setting controls the sequence in which focus moves between controls when pressing Tab, independent of their visual position, and each control's Tab Stop property can be set to No to remove it from the Tab key cycle entirely while still keeping it visible on the form. Moving fields physically (B) changes appearance but tab order must still be set separately and doesn't skip fields. Deleting the fields (C) removes needed functionality, not just navigation order. Renaming (D) has no effect on tab order.

Question 20

You want every printed or previewed copy of the CustomerForm to display today's current date automatically in the form footer, updating each time it's opened, along with your company logo in the header. What should you do in Layout view?

Accepted Answer

The Date and Time command inserts an expression-based control (typically =Date()) that automatically shows the current date whenever the form is opened, and Insert Image lets you place a logo image control in the header — both accessed from the Design tab while working in Layout or Design view. Manually typing a static date (B) would not update automatically. Adding a date field to the table (C) is unrelated to a form's cosmetic header/footer display. A background theme color (D) cannot represent a logo image.

Question 21

On the OrdersForm, you added a new unbound text box control that will display a calculated total, but it currently shows a generic caption like 'Text24' above it. What should you do to give it a meaningful label such as 'Order Total'?

Accepted Answer

Each bound control typically has an associated label whose Caption property holds the visible text shown to users; selecting that label and editing its Caption (either via the Property Sheet or by double-clicking and retyping directly on the label in Layout/Design view) changes the displayed text. Renaming the text box's Name property (B) only changes its internal object name, not the visible label text. The form's own Caption (C) affects the form's title bar/tab text, not an individual control's label. Deleting the label (D) removes the label entirely rather than updating its text.

Question 22

You are refining the SalesByRegion report. You need records grouped first by Region, then within each region sorted by SalesRep name alphabetically, with a subtotal of SalesAmount shown at the end of each region group. What should you configure in Layout view?

Accepted Answer

The Group & Sort pane (Design tab > Grouping & Totals) lets you add grouping levels (Region), nested sort levels within a group (SalesRep), and configure a Total (Sum) that displays automatically in the group footer — exactly matching a grouped, sorted, subtotaled report. Sorting the underlying table (B) does not create report-level grouping or automatic subtotals. Conditional formatting (C) only changes visual appearance, not grouping/totals. A manually typed subtotal (D) will not recalculate as data changes and is not a real total.

Question 23

The ProductCatalog report currently prints in Portrait orientation, but the number of columns needed (Product Name, SKU, Price, Description, Supplier) makes the content feel cramped on a narrow page. You want the report to print in Landscape instead. Where should you make this change?

Accepted Answer

Page orientation (Portrait vs. Landscape) is controlled in Page Setup, accessible from Print Preview's Page Setup command on the Page tab, and changing it to Landscape gives more horizontal space for wide, multi-column reports. Manually narrowing controls (B) or shrinking fonts (C) are workarounds that don't address the fundamental page width and can hurt readability. Converting to a form (D) changes the object type entirely and forms are not the appropriate object for printed multi-record catalogs.

Question 24

You are finalizing the EmployeeDirectory report, which needs to print as a two-column layout to save paper (since each record is short), and each printed page must show the current page number and total page count (e.g., 'Page 1 of 4') in the footer. Which two actions correctly configure this? Choose 2.

Accepted Answer

Page Setup's Columns tab is specifically where you configure a report to print in multiple newspaper-style columns per page (setting Number of Columns to 2), and the Page Numbers command inserts an automatically updating expression control in the format 'Page N of M' that recalculates as pages render. A static text box (C) would not update correctly across different runs or record counts. Manually resizing report width (D) does not by itself create a true multi-column print layout — the Columns tab setting does. Cascade Delete (E) is a table relationship option unrelated to report pagination.

Question 25

On the ProductCatalog report, you want each product's photo to appear next to its details, pulled dynamically per record from an Attachment field in the underlying Products table, rather than one single static logo image repeated on every record. What should you do in Layout view?

Accepted Answer

Dragging the Attachment/Photo field from the field list onto the report (or using Add Existing Fields) creates a bound image control tied to that field, so each record's report instance displays its own corresponding picture from the table. Insert Image (B) places a single static picture that is the same for every record, which does not vary per product. A report background image property (C) is a single unbound cosmetic image, not per-record bound data. A hyperlink (D) would only link to a location rather than display the image inline.

MO-500 — Microsoft Office Specialist: Access Expert (Office 2019)