Create a Query in Design View (Microsoft Access 365)

Design View is the most flexible environment for building queries in Microsoft Access 365. While the Simple Query Wizard is helpful for beginners or quick summaries, Design View gives you full control over tables, joins, fields, criteria, sorting, and grouping. In this lesson, you will learn how to create a query entirely in Design View and how the resulting datasheet behaves once the query is executed.

Understanding Query Design View

Design View displays two coordinated panes:

• Upper pane — the tables or queries you add, including their fields and relationships
• Lower pane (Design Grid) — the columns where you define which fields to display, how to sort them, and any criteria to apply

This interface provides a visual method of building SQL logic without typing SQL statements directly. It is especially useful for:

• Selecting fields from multiple related tables
• Filtering results using criteria
• Applying sorts
• Controlling visibility of fields
• Defining joins and grouping operations

Before You Begin: Using the Query Datasheet

After you run a query from Design View, the results appear in a query datasheet. A query datasheet behaves almost exactly like a table datasheet:

1. Entering data—New values added to the datasheet are written directly to the underlying table.
2. Editing data—Revisions in the datasheet update the original records.
3. Navigating—Arrow keys, scrollbars, and search work as in a table.
4. Printing—Use File → Print to print the datasheet view.
5. Filtering—Filters can be applied by selection, exclusion, or form. To make the filter permanent, you add criteria in Design View.
6. Sorting—Sort Ascending and Sort Descending commands operate on the active field.

In Access 365, datasheets also support column header sorting and right-click filtering similar to Excel.

Form Views (Continuous Forms Example)

While learning about datasheets, it is helpful to understand that Access forms can also display data in multiple ways. The image below shows a Continuous Forms layout, which displays several records at once—useful for contact lists, search interfaces, or any scenario where users browse multiple records visually.

Steps to Create a Query in Design View

Use the following procedure to build a query manually:

1. In the left Navigation Pane, select Create → Query Design. Access opens Design View with the Show Table dialog box displayed.
2. From the Show Table dialog, add the tables you need. For example, if your query should include fields from both the Clients table and the Projects table:
   • Select Clients → click Add
   • Select Projects → click Add
   Then select Close.
3. The upper pane now displays the two table boxes. If relationships exist, Access shows them with a connecting line. If tables are not related, your results may repeat rows or fail to join correctly, so ensure relationships are defined in Database Tools → Relationships.
4. Add fields to the design grid. Double-click any field to place it into the grid. For example, add:
   • FirstName (Clients)
   • LastName (Clients)
   • ProjectDescription (Projects)
5. Click View (Home tab → View → Datasheet View) to run the query. Access displays results in a datasheet.

You may switch between Design View and Datasheet View at any time using the View selector. The grid is where you add criteria, sorting, visibility choices, or grouping logic.

About the Design View Experience

Design View offers a precise, controlled way to build queries. It is similar to the Advanced Filter/Sort interface but far more capable. As you work in Design View, you can:

• Add or remove fields at any time
• Write criteria expressions (for example, >50 or Like "A*")
• Hide fields that are needed for filtering but not for display
• Join tables manually when needed

Once you understand the layout and purpose of each grid column, building even complex queries becomes a visual process followed by fine-tuning in SQL View when needed. Learn the different ways to save a query design.

Creating Query in Design View - Exercise