Designing A Query in Access 2017 – Part 1

Posted by benadt2017
Oct-31-2017
Microsoft Training, MS Access, Queries, Tables
Comments Off on Designing A Query in Access 2017 – Part 1

The true strength of a relational database lies in its capability to instantaneously fetch and analyse your data by running a query. Queries enable you to pull data from one or multiple tables based on a set of search conditions you define.

This tutorial will teach you how to make a basic one-table query. Then you will figure out how to plan and run a slightly complex multi-table query.

What are ms access query ?

Queries are a method to search for and compile data from one or multiple tables. Running a query is like asking a detailed enquiry of your database. When you build a ms access query, you are defining particular search conditions to get exactly the data you need.

How are queries used?

Queries are significantly more effective than the simple searches or filters you might use to search for data inside a table. This is because queries can draw information from numerous tables. While you could use a search in the customers table to find the name of one customer or a filter on the orders table to view only orders placed within the previous week, neither would let you view both customers and orders at once. But you could simply run a query to search the name and phone number of each client who has made a purchase within the previous week. A well-designed query can retrieve information you might not be able to find out just by examining the data in your tables.

When you run a query, the results are displayed in a table, but when you design a query, you will use the ms access query Design view, which lets you see how your query is put together.

One-table queries

Start familiarising the query-building process by creating the simplest form: one-table query.

A one-table query is literally an advanced filter applied to a table.

Take for example an Online Shop database that contains the basic information of customers who made a purchase. If you want to pull out the basic information of the customers living in Florida, you will do so by running a ms access query on the Customers table of the Online Shop database.

How to apply a simple one-table query:

Step 1: Click the Create tab on the Ribbon, and locate the Queries group.

Step 2: Click Query Design so Access will switch to Query Design view.

Step 3: In the Show Table pop-up window that appears, choose the table you want to run a query on and click Add and then Close. We want to retrieve the customers information, so we will add the Customers table.

Step 4: The chosen table will appear as a small window in the Object Relationship pane. In the table window, double-click the field names you want to include in your query. They will be added to the design grid in the bottom part of the screen.

In our example, we want to view the basic information of customers who live in Florida, so we will include the First Name, Last Name, Street Address, City, State, Zip Code, Phone Number, and Email fields.

Step 5: Set the search criteria by clicking the cell in the Criteria: row of each field you want to filter.

Note: Typing criteria into more than one field in the Criteria: row will set your query to include only results that meet all of the criteria. If you want to set multiple criteria but don’t need the records shown in your results to meet them all, type the first criteria in the Criteria: row and additional criteria in the Or: row and the rows beneath it.

For this one-table query, we will use just simple search criteria.

We want to find the customers who live in Florida, so in the State field, we will type “Florida” (in quotation marks) in the Criteria: We include the quotation marks to retrieve all records with an exact match for Florida in the State field.
You can also use the zip code to find customers who live in the area. We will add the zip code 33614 as another criteria. Type 33614 (without quotation marks) in the or: row of the Zip Code For fields that have the Number data type, make sure to remove the quote marks around the default value expression. If you specify text criteria for a numeric or data/time field, Access will return a data type mismatch error.
Step 6: After you have set your criteria, run the query by clicking the Run command on the Query Tools Design tab.
The result will show the first name, last name, street address, city, state, zip code, phone number, and email of all customers who live in Florida and in the specified zip code.
Step 7: ms access query output will be shown in the query’s Datasheet View, which looks like a table. To save your query, right-click the query tab and choose Save. When prompted to name it, type the desired name, then click OK.
Understanding Designing A ms access query requires уоu have a good knowledge оf thіѕ topic, аnd thе оnlу wау уоu саn earn thіѕ іѕ bу gеttіng аn up-to-date information оn thіѕ subject. Click hеrе tо gеt уоur copy оf оur highly informative Ebook.To view Part-2 of this series, (by Ben Beitler ), Click here >>Kind regards,Ben Beitler – “Your Access Database Expert!P.S. ACT NOW! and I will give you a 30 day support service (by email) FOR FREE! to help build and refine your Access Database (any version) for you.