In Access, queries are similar to questions which are asked to source connected, especially very distinct, data in your database.
Create a query
- Click Create > Query Wizard.
- Choose Simple Query Wizard, and next press OK.
- Click the table or query that includes the field, and next create the field within the Selected Fields list. When complete, click Next.
If you added a number field
If applicable, the wizard enquires about if you want the query to yield specific information or summary data. Opt for one of the following:
- For finding individual records, press Details, followed by Next. Skip on to the 4th step.
- Alternatively, for checking summarised numeric data, (e.g. averages), click Summary and next, choose Summary Options. Decide which fields you wish to summarise and the ways you want to summarise the data. Number fields are the only fields that are listed. For every number field, select a function:
- Sum – The query returns the sum of all the values in the field.
- Avg – The query returns the average of the field’s values.
- Min – The query gives the smallest value of the field.
- Max – The query provides the largest value of the field.
- To filter the query results based on a count of the records in a data source, choose the Count records in table name for that table.
If you added a date/time field
The Query Wizard prompts you with suggestions of grouping the date values. Since you used a date/time field, you may calculate summary values for every unique date and time value, for each day, month, quarter, or year.
4. Name the query.
5. Clearly state if you want to open the query (in Datasheet View) or adjust the query (in Design View).
Once your Access database has tables and relationships, it’s time to design and build some queries, which are essential components of any database.
In this video, you’ll learn the basic process for creating and running a query, get an introduction to several types of queries, create a query in the Query Wizard, and modify a query in the Query Designer.
Queries are literally the questions you ask of your data. In fact, asking the question you want answered can be a good place to start.
For example, “How many orders did TailSpin Toys place last month?”
The language in that question points you to the Orders table, and from there you select the fields you want to see, build the query, and then run it.
That’s the basic flow for most queries. You ask your question and then you pick a data source—the tables, or even other query results which contain the data—that will answer that question.
You select the fields you want to see in the result, which is also called the “data set” by the way, and add any criteria.
In this example, the criteria filter the results to TailSpin Toys for the month of May.
You can use queries for more than just answering questions. Queries are like a multi-tool or combination pliers—they can be one of the handiest tools in any database because they do so much.
For example, queries can provide data for forms and reports, and they can also help you make bulk changes to your data.
Now, you can build several types of queries in any database. Let’s take a look by selecting Create, then Query Design.
We’ll close the Show Table dialog box for now.
These are the types of queries you can create.
A Select query is the most common. It’s the type that helps you get answers out of your data.
Use a Make Table query to select records from an existing table and save them as a new table. This can be handy, for example, if you import a big Excel file and you want to break some of that data into separate tables.
You run these next three queries against existing data.
Append queries add data to existing tables, …
Update queries change existing data, …
and Delete queries remove data.
You use a Crosstab query when you need to display summary data such as averages or sums.
For example, if you need sales by region, use a Crosstab query.
Here’s what a Crosstab query looks like in the Query Designer.
And the results look like this. You can see this one returns sales data per quarter.
Use a Union query to combine multiple Select queries into a single result. Do this when you need to see data from unrelated sources.
Use a Pass-Through query to connect to a large database such as, say, Microsoft SQL server, and process queries on that database.
When you do this, Access becomes the front-end —essentially a set of tools that help you run the larger database.
Finally, use a Data Definition query to create or modify tables and indexes. The thing is, Access provides so many other ways to create tables and change indexes that you’ll probably never actually use this type of query.
Now let’s create a Select query, the type of query you’ll use the most.
To build it, let’s select the Create tab and then select the Query Wizard.
If you’re new to queries, this is a nice place to start.
Select Simple Query Wizard, and select OK.
Select the Phone Numbers table, and then select the Customer and Phone Number fields.
Change the query title if you want something more descriptive.
Select that you want to display the results, ….
… and then select Finish.
Here’s the result.
You might have a couple of questions at this point, like “What kind of query did we just create?” …
And, “How do we know whether we’re calling someone’s office phone or their fax machine?”
You can answer those questions by adding some data to the query.
To modify your query, let’s open the Query Designer. Select Home, then View, then Design View.
The Query Designer shows you the data source—in this case it’s a table but it can also be a query. You also see the field involved in the query.
The ribbon indicates the TYPE of query, and in this case, you can see that it’s a Select query.
To add the Type field and make the phone list easier to use, you just drag it.
To see the results, on the Design tab, select Run.
Now, although using the Query Wizard is quick and easy, you can’t do everything with it. For example, you can’t add formulas by using the Query Wizard.
So for increased flexibility and to make whatever changes you might need, use the Query Designer.
So now you’ve learned the basics for creating and running a query by using the Query Wizard and the Query Designer. Using queries helps you analyse your data and get the answers you need.