Modifying a Query in Microsoft Access

northwind database instructions screenshots

The process for modifying a Microsoft Access query is similar to the process for creating one in the first place. Queries may be changed using either Design View or SQL View, however—you cannot use Query Wizard to modify an existing query.

Begin by right-clicking your targeted query within the objects panel at the left of the screen within your database. In the pop-up menu, select Design View. The query opens in Datasheet View.

When you right-click the query's name in the tab row above the Datasheet View output, you can change the view mode. By default, you're in Datasheet, which cannot be structurally edited (although you can insert and remove data from this view). From either SQL or Design views, however, you can edit the query's structure and save or save-as the modified object as needed.

Design View

Design View opens a horizontally split screen. The top half shows rectangles representing every table or query feeding the query you're modifying. Key fields—typically a unique identifier—feature a small golden key next to them. Each of the rectangles joins to other rectangles by means of lines connecting fields in one table to fields in another.

These lines represent relationships. In Design View, right-clicking on the line lets you change the relationship. You can pick from one of three options:

  • Rows where the joined fields in both tables are equal
  • Rows where the left-most table returns all results and the right table shows matching results
  • Rows where the right-most table returns all results and left table shows matching results

These three join types (inner, left, right) are a subset of the full range of joins that a database can execute. To do more complex querying, you'll need to move to SQL View.

When you connect your selected tables with relationship lines, you'll see the bottom half of the screen shows a grid listing all the fields that the query will return. The Show box displays or suppresses the field when the query is run—you can filter a query based on fields that aren't displayed. You may also manually add or modify the sort order to order the results in ascending or descending manner, although Microsoft Access will process several sorts in left-to-right order along the fields. You can reorder the columns by dragging them left or right across the grid, to force a specific sort pattern.

The Design View's Criteria box lets you input limiting criteria, such that when the query is run, it only displays a subset of the data that matches your filter. For example, in a query about open product orders, you could add the criterion ='MI' to a state column to only show orders from Michigan. To add levels of criteria, use the or boxes within the column or add criteria to other columns.

SQL View

In SQL view, Microsoft Access replaces the datasheet with the Structured Query Language syntax that Access parses to determine what data to pull from a source, and with what business rules.

SQL statements generally follow a block form:

SELECT Table1.[Fieldname1], Table2.[Fieldname2]
FROM Table1 RIGHT JOIN Table2 ON Table1.[Key1] = Table2.[Key2]
WHERE Table1.[Fieldname1] >= "FilterValue"

Different database vendors support slightly different versions of SQL. The base standard, called ANSI-compliant syntax, should be able to work in every database environment. However, each vendor augments the SQL standard with its own tweaks. Microsoft, for example, employs the Jet Database Engine within Access. Microsoft also supports SQL Server. Other vendors use different approaches, so SQL generally isn't as interoperable as the standards support.

If you're not familiar with the syntax of the Jet Database Engine's implementation of SQL, then tweaking the SQL View can break your queries. Stick to Design View, instead.

However, for very quick tweaks, it's sometimes easier to adjust the underlying SQL than to modify the Design View schematic. If other analysts in your company want to know how you got a result, sending them a cut-and-paste of your SQL statement reduces confusion about query design.

Saving Your Work

In Microsoft Access 2016, you can save and overwrite the current query by right-clicking its tab and selecting Save. To save the revised query as some other name, allowing the current query to persist, click the File tab, select Save As and then Save Object As.