How to modify query in Access

While there are a number of things you can do using queries (visit Introduction to Queries for a broad overview), we will focus just on the most basic type: Select Queries. Select Queries allow you to look at a subset of data from a given database that fits criteria you choose.

Creating a basic Select Query

1. With a database open, go to the “Create” tab at the top of the Access window.
2. Select the “Query Design” button from the ribbon below.
3. You will see a screen like the example below.  Select the tables you want to use in your query by double-clicking on their names or highlighting them and clicking “Add.”  You can select more than one table but they need to be related.
   

4. Once you have finished, you’ll be left with a screen like the one below (in which only one table has been selected.) Choose which fields you would like to include in your query results by double-clicking on the names in the box you see in the middle. Those fields will be entered into the table below. (Note: you can also click and drag the field names to the table on the bottom.)

5. Once you have made your selections you should have something like the figure below.  If you were to hit the “Run” button (towards the top left with the red exclamation point above it), you would get a list of just these fields from all the records in your table.  

6. In order to get a smaller subset of those records, take a look at the “Criteria” row in the table at the bottom of the figure above. By entering expressions in those fields you can limit the records your query returns to results that meet the criteria in your expressions. While this requires a bit of knowledge of what expressions are useable in Access, there are a few easy ways to control what data you get back from your queries:
   1. Text Fields.  If one of the fields you are using is a text field, you can limit your results to those records containing a specific word or phrase by enclosing the phrase in quotes in the criteria field.  For example, the third field of the Health Study example above ("City") contains all of the various cities subjects of the study come from.  To get back just the records where "Boston" was the response, you would type “Boston” (quotes included) into the "Criteria" field, and then run the query. 
   2. Numerical fields.  If you are using a field in your query that stores numerical information, basic mathematical operators can be used to limit your results.  Using the same example, let’s say you wanted to limit your results to study subjects with a BMI over or equal to 20.  Entering “>=20” (quotes not needed this time) in the Criteria field under “BMI”, would limit the results to just those participants.
   3. Dates.  Dates need to be specified by surrounding them with “#” signs.  You can then use them in logical expressions Access supports.  For instance, in order to only return study subjects born between 1/1/1975 and 1/1/1980, you could type the term “Between #1/1/1975# And #1/1/1980#”, into the criteria field under “Date of Birth.”  You could also express the same thing with mathematical operators, like this: “>=#2/1/2008# And <= #5/1/2008#”

NOTE: Be careful when reviewing data from queries.  If you make any changes to the data pulled from your queries, it will also change your data in the original table.

One of the primary benefits of Microsoft Access over Excel is the power of Access queries and reporting.  Understanding Access queries, however, isn't always easy.  This article gives you an introduction to the Access query designer, and tips for creating advanced Access queries to save you hours of time.
Bookmark this page

What is a Query in MS Access?

Simply put, a query is a way to combine information stored in separate tables to avoid duplication in the tables themselves.  For example, you have a table for customers, and a table for contacts.  But you need to send a mailing to each contact using the customer's address.  You can use a query to show each contact, the customer name, and the mailing address of the customer.  Data from separate (but related) tables, without needing to duplicate anything in the contacts table.
Access queries are very powerful, allowing you to view, insert, delete, and calculate records and fields from numerous tables in your database.

Building a Basic Query

We'll start by building a basic Access query joining three tables.  Say your database contains a table for Customers, Customer Types, and Orders.  Start by selecting the "Create" Ribbon tab.  Select "Query Design"

You will be prompted to select the tables and/or queries to include in your query.  Select each table and click "Add" or double-click them.  Remember to click "Close" when you are done adding your tables/queries.  If your database relationships have been defined correctly, Access will automatically draw the relationship lines between your tables.

If you don't see relationship lines between your tables

 

when you add them, you will want to draw them manually.  To do so, you'll need to select one field, and drag it to the related field in the other table.  Without that step, Access will not understand how to link the records together between the tables.  A few key things to remember when drawing the relationship lines:

• The two related fields need to have the same data type (e.g., Text or Numeric)
• Typically field will be the primary key of the table, and the other will be a corresponding foreign key.
• If you use a query within your query, you will always need to define the relationships to any other table/query manually.

Now you can begin dragging fields from each table/query into the field section of the query design window.

As shown above, we've added the Company Name, Customer Type, Order Date, Ship Date, and PO number from three separate tables into one view.  Next click the "Run" icon on the ribbon (the red exclamation mark) and we see the Query View:

The view above looks much like a standard Access table, when in fact it is a consolidation of several tables.  In this view, you can export your data to Microsoft Excel, PDF, or other formats.

Sorting and Filtering Data

Once you have a basic query in place, you'll want to be able to filter your records to perhaps display only certain records that match your criteria, or sort by one or more fields. 

In the screen above, we have sorted the records by CompanyName (Ascending means A-Z) and by OrderDate (Descending means newest dates first).  Note that the CompanyName field is listed first from the left, so the list will be sorted by Company Name, then if there are multiple records for a company, the newest order will be shown first.  In the Criteria section, we are filtering to shown any record where the Company Name contains "Research."  the "Like" statement is very powerful, and lets you find records that begin with a word (Like "Hello*"), end with a word (Like "*Hello"), or contain a word (Like "*Hello*").  All based on where you place the asterisk.  Note that CompanyName is a text field, so the criteria needs to be contain in quotes.  Next, we're going to exclude any records where the CustomerType is "Residential" using the Not Equal to (<>) sign.  We also want only records where the Order Date is less than 1/1/2008.  Note that the pound sign (#) is used to contain dates in Access.
Lastly, notice that the second line of the Criteria section starts with "or:" and we've entered a criteria.  You can use the second line to contain separate criteria sets.  In this case, we want either any Company with "Research" in the name that is not a Residential customer type and has orders before 1/1/2008 OR any record where the Ship Date is empty (that is what "Is Null" means).
If you have fields in your query you want to filter or sort by, but you don't want to see them in the actual results view, simply uncheck the "Show" checkbox for that field.  Access will still use it as criteria for sorting/filtering, but hide it in the Query View.

Editing Data in a Query

Depending upon how complicated your Access query is, you may be able to edit data.  But don't be alarmed if you receive a message "Record Locked" or "Cannot Add Record."  When you join multiple tables, Access may need extra guidance for how to handle edits or a new record.  And remember that with a relational database, editing a single row in a query can actually change the value for all records.  Take for example our field "CustomerType."  Changing the value in one row will actually change the text value in all records since we are modifying the field in the table tblCustomerType, not the underlying identifier field in the Customer table:

Why Are Records Missing in My Access Query!

You may be alarmed to find that you've created a query that returns no records, or many fewer than you expect!  Either your criteria is too strict, you've joined tables incorrectly, or, you need to define the relationships between the fields more precisely.  Taking our example query, recall that we joined the field CustomerTypeID and fk_CustomerTypeID in the Customer Type and Customer tables, respectively.  What if you have records in the Customer table that haven't been assigned a Customer Type value?  They will be missing from your query!  That is because Access, by default, assumes you want to see only records from two joined tables where the values match.  An empty value on a numeric AutoNumber field won't match!  Fortunately the remedy is quite simple.  You'll want to double-click the relationship line and explicitly tell Access you want to see all records from the Customer table even if there is no matching record in the Customer Type table: