The rationale behind creating a database is typically to store data that you need to access for business purposes. While creating the actual database and its data entry forms and reports is a necessary first step, the day-to-day work on the database generally involves getting business information out of it.
 |
In this article I’ll look at how to do exactly that by using queries. These queries let you answer typical business questions and help you to analyze the information in your database in a meaningful way.
If you are new to working with Microsoft Access, I suggest you first read my earlier tutorial, Build Your First Database with Access. This will familiarize you with Access so you understand the basics of creating a data table and a data entry form and report to go with it.
That article includes a simple query for extracting data for customers who live in a particular state or province. In this article, we look at writing more advanced queries, and how you can format and sort the query results.
Create a Data Table
If you want to follow along with this example and create the queries as you go, create a small data table with eight fields called: CompanyName, State, RepName, PhoneNumber, CustomerSince, CreditLimit, OutstandingBalance and Preferred.
CompanyName, State, RepName and PhoneNumber should all be text fields; CustomerSince is a date field; CreditLimit and OutstandingBalance are currency fields, and Preferred is a yes/no field. Type some sample data into your table.
Extract Matching Data
Let’s start with a simple query that lists all the businesses situated in California. To create this query, click Queries in the Objects list and choose Create query in Design view. In the Show Table dialog, select the Customers table and click Add. Click Close to close the dialog.
 This OR query returns customers located in California or those who qualify as preferred customers. (Click for larger image). |
You create your query in the grid below the table. To add the fields you want displayed in the query results, drag fields from the Customers table in the top half of the dialog into the fields (columns) in the grid. For example, you may want the CompanyName, RepName and PhoneNumber in the results so that you can contact these customers.
If you drag the asterisk (*) entry, all fields will be added to the grid – however as you can’t easily specify individual criteria for the fields, this is not as useful a technique as it might appear to be.
Add the State field so you can build a query using it. If you don’t want the state to appear in the results, disable its Show checkbox. To display California companies only, type CA in the Criteria cell underneath the State field name. Click the Run button on the toolbar to run the query.
When you’re done, click the Design View button to return to Query design view.
Using AND and OR Queries
AND and OR queries let you combine criteria so that you can search for customers that match more than one criteria. For example, you may want a list of customers from California with credit limits exceeding $4,000.
To do this, add the CreditLimit field to the query and in its Criteria cell type >=4000 on the same line as the letters CA appear. This creates an AND query which displays only customers located in California and who have credit limits of $4,000 or more. Click the Run button to preview the query results.
Use an AND query when both criteria must be met. If you want to match either of two criteria such as listing customers who are either located in California or who are preferred customers, use an OR query.
To do this, remove the CreditLimit field and add the Preferred field to your table. In the OR line under the Preferred field name, type the word Yes. Run the query, and you will see all customers from California as well as all preferred customers. A customer must match one or both of the criteria to appear in the list.
