Step by step exercise. Simple queries (II)

Objective.

Practice the operations for Creating a simple query and include search criteria

Exercise 1.

We will start by creating a query which will contain fields Name, Surname, and Date of birth from Students table.

1 Open the Classes.mdb database.

2 Position yourself on Queries tab in the Database window.

3 Double-click on the Create a Query in Design view option, or, click on the button, select the Design view option and click on OK button.

Next will open the Show table box.

4 Click on the Students table, it will remain selected if it was not already before.

5 Click on the Add button. Observe how the Students table appears in the tables area of the design window.

6 Click on the Close button to close the dialogue box as the query is going to be based only on the Students table.

Now we will add the Name, Surname, City, and Date of Birth fields

7 Click on the Field: row in the first column of the grid.

8 Drop down the associated list by clicking on drop down list arrow.

9 Select the Student name field.

10 Double-click on Student surname in the table.This is another way of adding fields to the grid.

11 Drago the City field from the table to the grid.This is another way of adding fields to the grid.

12 Include the Date of birth field the way you prefer.

The grid will appear in the following way:

13 Click on the run button or the Datasheet button to see the result.

14 Now we will save the query, click on the button on the toolbar.

15 Write the name that we would like to give to the query, My Students. Look out! we may not give it the same name as a table already created.

16 Click on the button in the query window to exit it.

Exercise 2.

We will now edit the previous query in order to obtain those students who were born before the year 1967.

1 Select in the Database window the query we want to edit, My Students, by clicking on it.

2 Click on the button in the Database window. The Query design window will open.

3 Click on the Criteria: row in the Date of birth column.

4 Write <01/01/67 in order to indicate the "[Date of birth] < #01/01/67#" condition. Observe how Access has enclosed the date between # #..

The QBE box will appear in the following way:

5 Run the query by clicking on the button on the toolbar.

We will now save the query but with a different name.

1 Drop down the File menu and select the Save as... option.

2 Write another name, Students before 67.

3 Click on the OK button (or press Enter key).

4 Close the query by clicking on the button.

Exercise 3.

Now we will edit the previous query in order to obtain only those students from Los Angeles who were born before 67. We need to create the criteria city = "Los Angeles" and [date of birth] < #01/01/67#

1 Select the query to edit, Students before 67 by clicking on it.

2 Click on the button in the Database window. The Query design window will open..

3 Click on the Criteria: row in the City column.

4 Type Los Angeles. In this case it is not necessary to type the operator as it is =, just the value must be type and it does not need to be enclosed between inverted commas, Access will automatically add them.

The QBE box will appear in the following way:

As the conditions are placed in the same row, the query will recover record that comply with both criteria. In other words, all the students from Los Angeles AND born before 67 will appear.

5 Execute the query by clicking on the button on the toolbar.

Note one student from the other list does not appear (Mario) as he is from Las Vegas.

Now we will save the query with a different name.

1 Drop down the File menu and select the Save as... option.

2 Type Students from Los Angeles and before 67.

3 Click on the OK button.

4 Close the query by clicking on the button.

Exercise 4.

Now let us perform another query using the first one, but this time we will sort the students by surname.

1 Select the query to edit, My Students, by clicking on it.

2 Click on the button in the Database window. The Query design window will open.

3 Click on the Sort: row in the Student surname column.

4 Click on the arrow to drop down the list and select Ascending in order to sort from A to Z.

The QBE box will appear in the following way:

5 Run the query by clicking on the button on the toolbar.

We will now save the query with a a different name.

1 Drop down the File menu and select the Save as... option.

2 Write the name as Students by surname.

3 Click on the OK button.

4 Close the query by clicking on the button.