Step by step exercise. Creating simple queries.

Objective.

Practice the operations for Creating, executing, and saving a simple query.

Exercise 1.

We will start by creating a query and using the symbol * (all the columns).

1 Open the Classes.mdb database found in My Documents folder.

2 Position youself on the Queries tab in the Database window.

3 Click on the Create a query in Design view option, or, click on the button and select the Design view option before clicking on the OK button.

Next will open the Show table box.

4 Click on the Courses table so that it remains selected if it is not.

5 Click on the Add button. Observe how the Courses table appears in the Tables area of the Query design window.

6 Click on the Close button to close the dialogue box as the query will only be based on the Courses table.

We already have the design window with the table added, now we will indicate which fields (columns) we want to appear in the query. In the table we have the fields from the Courses table and a * which represents all the columns.

7 Double click on the asterisk and observe how the asterisk appears in the QBE grid.

8 Click on the execute button or the Datasheet button to see the result. Observe how all the columns from the Courses table have appeared. As we do not have any courses created no data will appear, but we can introduce it directly from the Datasheet view of the query.

9 Introduce the following data. There is no need to save the records as they are automatically stored.

Now we are going to delete the * column from the grid.

1 Return to the Design viw by clicking on button.

2 Move the pointer over the top part of the column until the column selection arrow appears and at this moment clic. The column will appear selected.

3 Press the Del key, the column is now empty.

Now we will add the fields one by one.

1 Double click on the Course code field, it will be added to the QBE grid.

2 Double click on the hours field, it will be added after the last.

3 Double click on the Start date field, it will be added. Now we would like to add Course name after Course Code.

4 Drag the Course Name over to the hours field with the mouse. The Course name field has taken the place of hours, displacing it and the rest of the fields positioned on the right.

5 Finish by adding the Finish date field.

We are now going to leave the hours field after the Finish date field:

1 Select the hours column as we did before with. The cursor will have taken the form.

2 Click on the mouse button and keeping it down drag the column to behind the Finish date column. You already know how to move columns.

Now we will add a calculated field which will indicate the number of lapsed days between the start and finish date.

1 Position the cursor in the Field: row in the first free column in the grid (after the hours field) and type days : [finish date] - [Start date].

In front of the colon is the header of the column, and after the colon we put the expression that indicates Access how to calculate the value of the column.

In this case the names of the columns must be enclosed in brackets [ ] because they will contain blank spaces.

We can also calculate what 10% of the hours of the course is:

1 Position the cursor in the Field: row in the first free column in the grid.

2 Type 10%hours:hours * 0.1.

Notice how in this case there is no reason to enclose the name of the field hours in brackets because it has no blank spaces.

3 Now visualize the result of the query using the execute button or the Datasheet view .

4 Change the Course number 1, type 150 in hours column and press Enter key.

Observe how when the field is changed, the calculated field will be updated (the percentage will vary).

5 Leave the value as it was before in the record (300).

6 Close the query, and as this is the first time that we save it we will be asked for a name, type simple query.

Exercise 2.

In the Students table we still need to assign courses to students, and so we will create a query in such a way that it appears like the Student code with its course code, in order to introduce the values that we will see next.

We will start by creating the query.

1 Position yourself over the Queries tab in the Database window.

2 Double-click on Create Query in Design view option.

Next the Show table dialog box will open.

3 Click on the Students table so that it remains selected.

4 Click on te Add button.

5 Click on the Close button in order to close the dialogue box as the query will only take data from Students table.

We already have the added table in the design window, now lets indicate which fields (columns) we want to appear in the query.

6 Double click on the Student code field in Students table and observe how it appears in the QBE grid.

7 Double click on the Course field and observe how it appears in the QBE grid.

8 Click on the run button .

9 Introduce the following data:

10 Close the query, and save it as Student Course Query.