FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Getting Started with Queries in Microsoft Access

Queries are the magic with which you will filter your databases to create unique views of your data. If you followed our advice, you will have created a series of tables all of which relate to one another in one way or another.

While tables are designed as a place to store data, queries were created to help you filter your data into more useful forms. Essentially, a query looks at your data and tells it to display only the data that matches a particular set of conditions.

• Do you want to see a list of only those customers that have made purchases in the last year? Use a query.
• Would you like to view only those customers that live in California, Oregon and Washington States? Use a query.
• Would you like to view a list of suppliers that provide free shipping for orders over $100.00? Use a query.

For just about ever different way you would like to look at your data, Microsoft Access queries will provide the tools you need.

Essentially, queries take a massive store of data and filters out all of the irrelevant material so that you can focus on what you need to learn.

Let's get started

Open the Northwind database on your local computer. Let's create a simple query to show those Employees from the London office.

Click on the Queries tab and then select New. You should see the following dialog box:

Select Design View and click OK. The Query Design Window will open up. Click the Employees table and select Add. You will see the table added to the Query Design window. Click Close to close the Show Table dialog.

In the Query Design Window, select the LastName, FirstName and City fields and drag them onto the Query Design grid. To select more than one of the fields in the Employee table, hold down the CTRL key while you select the fields. Your Query Design grid should resemble the following:

If you click the preview button on the tool bar, you will see a list of employees with their EmployeeID, last name and first names. It will look something like the following:

You can see a list of all the employees along with the city they work out of. Unfortunately, the list is not sorted alphabetically. Let's change that.

Return to the query design view and place your cursor in the Sort field of the LastName field. You'll see a drop-down box with several selections. Choose Ascending. Now, do the same for the FirstName field.

Your screen should now resemble the following:

Click on the preview button and you will now see your list of names has been sorted first by LastName and then by FirstName. It will look like the following:

Now, let's limit the list to only those employees based in London. Back in the query design window, place your cursor within the Criteria section of the City field. Type London and hit enter. Access will place double quotation marks around the word London. Your screen will look like the image below.

Click the preview button to see the new list.

Now save your query. Select File-Save As and give it a name. It is a good practice to always begin query names with a lower case q. This will make it easier to always be able to know queries from tables when you look at a long list in FrontPage. Call the file qLondonReps. Later, you can access this query in FrontPage just as easily as you can access your Employees table.

Let's add just one more change. How would you list employees from London OR Seattle. Simple, Just type exactly that in the Criteria screen. Your screen will look like the following:

And the end results are:

You've now created your first query. It's that simple. Save your query as qGettingStarted.