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
- With a database open, go to the “Create” tab at the top of the Access window.
- Select the “Query Design” button from the ribbon below.
- 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.
- 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.)
- 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.
- 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:
- 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.
- 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.
- 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.
