Access 2003: Extracting Data Through Queries

Thursday May 22nd 2008 by Helen Bradley
Share:

You've put in the time and effort to build and populate a database — now what? We take a look at the ways to create queries to find pearls of business wisdom. Plus: watch the video.

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.


Microsoft Access Queries

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.

Access query screen shot
We're using this sample data for the queries discussed in this column.
(Click for larger image)
.

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.

Access query screen shot
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.

Return to the Query Design view, and this time let’s consider the situation where you want a list of customers with credit limits between $2,000 and $5,000. Remove any existing criteria and add the CreditLimit field to the table. In the Criteria area for the CreditLimit field, type:

>=2000 And <=5000

Access query screen shot
This query creates and displays a field dynamically by performing a calculation using data from two other fields.
(Click for larger image)
.

In this case where the two criteria: >=2000 and <=5000 both relate to the same field, you write the query by placing both criteria in the same cell with the word AND appearing between them.

Another example of this is a query that displays all customers who became customers in the period between January 1, 2000 and December 31, 2005. In this case, add the CustomerSince field to the query, remove any other criteria, and then type the following into the Criteria cell for the CustomerSince field:

>=#1/1/2000#and<=#12/31/2005#

When you use dates, surround the date text with hash symbols so Access understands this data represents a date.

Sort Query Data

You can sort the query results by selecting the sort order from the Sort dropdown list below a field name in the query grid. For example, to sort the previous CustomerSince query by the date in the CustomerSince field, set the sort cell under CustomerSince to read Ascending and test the result by running the query again.

Access query screen shot
This query is very flexible as it prompts you for the query data at run time.
(Click for larger image)
.

Calculated Data

There may be times when you'll need data that doesn't actually exist in the data table field, but by making calculations in your queries, you can mine that data. For example, to determine the difference between a customer’s CreditLimit and their OutstandingBalance, add a field to your query table by typing this into an empty Field cell:

AvailableCredit: [CreditLimit]-[OutstandingBalance]

Run the query and notice that a new AvailableCredit field appears displaying the difference between a customer's credit limit and what they currently owe. This field isn't added to the table, and it only appears in the query. You don't need to have it in the table because you can create it dynamically whenever you need it.

Format Query Results

To format the results of a query, right-click the field name in the query grid and choose Properties > Format. Here, for example, you can configure a numeric field to display as currency with 0 decimal places. Run the query to see the results.

Want to See Even More
Software Application Tips?
Check out our SmallBusinessComputing series, How-To With Helen Bradley

Reuse a Query

You can save any of these queries and reuse them later on. However, you'll probably want to make them more useful by having them prompt you for the criteria when the query is run rather than hardwiring the criteria into the query.

For example, to create a query to extract customers who have credit limits between certain values, add the CreditLimit field to your query table and type the following into the Criteria cell:

Between [Lower CreditLimit Value] And [Upper CreditLimit Value]

Run the query, and you'll be prompted to enter the credit limit values marking the lower and upper range of credit limits you want to view.

As you can see there are plenty of options for extracting meaningful information from your database. Being familiar with the methods that you can use to extract data will let you make better use of the time and effort that you have put into entering the data in the first place.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com

Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today!

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved