Article Index
Microsoft Access Tutorial
Queries
Creating A Database
Tables
Creating Tables
Queries
Forms In Microsoft Access
Reports In Microsoft Access
Data Access Pages
Controlling The User's Input
Subdatasheets
Designing Forms
Exploring And Analyzing Data
Tables Looks
Design And Exploration
Forms Design Propertie
Controlling Data Output
Microsoft Access Controls
Design And Improvements
Importing And Exporting Data
Macros
Switchboards

One of the most important tasks when using a database is to search and retrieve information. When you create a database, sometimes large tables (though necessary) don't make your life easy. You need to reorganize your data from the many tables so that necessary information can be gathered for easy viewing and manipulation.

Definition:

A query is a piece or part of the database created to search for, and retrieve, specific data for a specific purpose. It can be viewed as a question, or a filter intended to select only some necessary data.

Creating A Query From A Wizard:

Start Microsoft Access and open the MPLS1 database.

A query can be created from one table, more than one table, or from another query. Like a table, a query can be created in many different ways, except that, this time, data originates from somewhere, mainly a table.

To create our first query, From the Database window, click the Queries button. Double-click the Create Query By Using Wizard button. (If you are using MS Access 97, click New, click Simple Query Wizard, and click OK).

The first page of the Simple Query Wizard expects you to choose the origin of your query, starting with the table or the query name. For this example, we will use the tblEmployees table.

Then, you choose the necessary data that you want to filter for your query. We want to use this query to easily retrieve the employees data by either their employee number, their last name, the date they were hired, their salary, their marital status, their E-Mail address, or the city or state they live in. All other information can be used somewhere else. 4.

From the Available Fields list box, double-click EmployeeNumber, Title, LastName, Function, DateHired, Salary, MaritalStatus, City, and State. Click Next twice.

Give the title qryEmployees to your query. Click Finish.

The finished product looks like a spreadsheet, made of columns and rows. Unlike the parent table, it contains only the data we want to query.

Now that we have a query, it is time to put it to good use. You can modify criteria or the behavior of a query in Design View.

Switch to the Design View.

Under the LastName field, set the Sort orders to Ascending.

Running A Query:

When your query is ready, you can run it by clicking the exclamation buttonon the toolbar.

To see the results of our query, click the exclamation point button on the standard toolbar.

Switch back to Design View. Delete the Ascending sort the LastName field.

Query Sorting:

To view employees by seniority in the company, sort the employees in ascending order under DateHired. And run the query.

Now, to see the last employee who was hired and other subsequent employees, change the sort to Descending, under DateHired. Then run the query.

Switch to Design View and delete the sort retrieval under DateHired.

To see the lowest paid employee in the company, set the sort retrieval to Ascending under Salary. Then run the query.

When you are finished inquiring about other people's salary, switch to Design View, and delete the sort set under Salary.

You can run a double query. For example, if you want to find out people by seniority (who was hired first in a certain year), and you want to find out who is earning more among those hired in a time period, set the sort retrieval under DateHired to Ascending; then set the sort to Descending under Salary. And run the query.

Switch to Design View. And delete both sort retrievals.

Even after you have decided on the fields necessary for your query, you can decide not to take into consideration a field or some of the fields in your query. To do that, you would decide not to Show a field.

To see how it is done, click the Show check box under MaritalStatus (so, make sure it is empty/unchecked). Then run the query. The Marital Status column is absent.

Switch back to Design View and click the check box to Show the MaritalStatus field.

Setting Criteria In A Query:

Sorting fields and setting criteria are what makes a query valuable. If you can sort a field in ascending, descending, or in combination, there are limits to these actions. The criteria setting allows you be even more specific.

Make sure your qryEmployees query is in Design View.

To see only the people who were hired before 1995, in the Criteria row for the DateHired field, type <#1/1/96#. This means "show me the people whose DateHired field is less than January 1st, 1996", which means anybody hired before 1996. Then run the query.

Switch back to Design View and delete the criteria set in DateHired.

To find out which employees live Maryland, in the Criteria fieldfor State, type ="MD". Run the query.

To find out which ones of the employees don't live in MD, change ="MD" to <>"MD". Run the query.

To get the list of employees whose salaries range from $10 to $15, in the Criteria for Salary, type >=10 And <=15. Run the query.

Switch to Design View and delete the Criteria set for Salary.

To run the same query, in the Criteria for Salary, type Between 10 And 15. Run the query, then switch back to Design View, and delete the Criteria under Salary.

To get a list of married employees (and invite them to the Employees Marriage Conference of the Greater Washington DC(?!?)), in the Criteria box for MaritalStatus, type True and run the query.

Switch back to Design View. To run the same query, replace True with On.

To print a list of married employees, on the Standard toolbar, click the Print button.

To get a list of the employees who hold a function in the company, switch the query to Design View. In the Criteria for the Function field, type Is Not Null and run the query. Switch back to Design View. To get a list of employees who don't hold any function in the company, replace Is Not Null with Is Null and run the query. Switch back to Design View and delete the criteria.

Switch to Design View and delete the Criteria set for MaritalStatus.

Combining Sort And Criteria:

The most useful thing about a query is to get the right information and efficiently. This comes from running a query whose sort and criteria fields have been set appropriately. Just like you can combine Sort retrieval, you can combine criteria fields, sort fields, and combinations of lots of them. Sometimes, the purpose is to narrow your search as much as necessary.

The first thing you need to know about combining fields is that they use Boolean algebra. Let's review it quickly. If I say, "the dog is in the house", that condition is TRUE whenever the dog is in the house.

If I say, "the dog is in the house and the door is closed"; that statement is true only if the dog is in the house AND the door is closed; if the dog is in the house and the door is opened (NOT closed), the statement is FALSE, the same goes if the dog is outside but the door is closed.

If I say, "the dog is in the house or the door is closed"; if one of these two situations is TRUE even if the other situation is false, the statement is true. All of that was plain English, wasn't it? The tricky part lies somewhere else. Mathematicians have had the (silly) idea of starting some sentences negatively. If in English I say, "the dog is not in the house and the door is closed"; if the dog is NOT in the house and the door is closed, the statement is TRUE, but the dog has to be NOT in the house.

In Boolean Algebra, that would be written, "NOT the dog is in the house and the door is closed". I know you understand it because somebody is just plying with our... mind.

To get a list of employees whose salary is greater than $10 AND live in MD, in the Criteria for Salary, type >=10; then in the Criteria for State, type =MD (the query will change it to ="MD"). Run the query.

Switch back to Design View and delete both criteria.

To get a list of the company's employees who earn more than $11 AND are married AND don't live in Maryland, in the Criteria, for the Salary field, type >=11, for the MaritalStatus, type True, and for State, type <>"MD". Run the query.

Switch to Design View and delete all of the preceding criteria.

To get a list of employees who live in Maryland OR in Virginia, for the State field, in the Criteria row, type MD Or VA, then run the query.

Switch to Design View. To run the same query, in the Criteria under State, type MD, press the down arrow key (that should take you to the OR field of the Criteria for State), then type VA. Run the query.

Switch to Design View and delete the preceding criteria. Finally, to see an Ascending order list of employees who live either in Maryland or in Arlington (VA), set the Sort order of LastName to Ascending; in the OR field for City, type "Arlington"; and in the Criteria field for State, type "MD". Run the query.

Switch back to Design View. delete all of the Sort, Criteria, and OR fields. Run the query. Make sure you have all data.

Save the qryEmployees query and close the database.