Unit 10 Exercise Help. Action queries


Exercise 1: cars

Apartado 1:

1 Position yourself in the Queries tab in the Database window of the Cars database.

2 Click on the Create query in design view option.

Next will open the Show table box.

3 Select the Services table.

4 Click on the Add button.

5 Select the Sold cars table.

6 Click on the Add button.

7 Click on the Close button.

8 Double click on the services field.* to select all the fields in the Services table at once.

9 Double click on the Make field to add it to the grid in order to type a search criteria

10 Type Ford in the Criteria row to form the Make = 'Ford' condition.

11 Uncheck the box on the Show row in the Make column so that it does not appear in the result of the query.

Now we have the new query to obtain the data to store in the new table.

12 Drop down the button on the toolbar and select the Make-table query... option.

13 Type the name of the new table as Ford Services.

14 Click on the OK button.

15 Click on the Datasheet view button to check the data that will be stored.

16 Click on the run button to create the new table.

17 Close the query, name it Create Ford services.

 

Part 2:

1 Position yourself in the Queries tab in the Database window of the cars database.

2 Click on the Create query in design view option.

The Show table box will open next.

3 Select the Sold cars table.

4 Click on the Add buttn.

5 Click on the Close button.

We will add the fields that we need (those that we want to update and those that we need to include in the search criteria, in our exercise we want to update the price of all Ford cars, we will add Price and Make.

6 Double click on the Price field.

7 Double click on the Make field.

8 Write Ford in the Criteria row to form the Make = 'Ford' condition.

9 Drop down the button on the toolbar and select the Update query option.

10 In Price column in Update To row type [Price]*1.05 or if you prefer [Price] + ([Price]*5/100). Remember to write the name of the field between brackets [ ] to avoid Access confusing it with literals.

11 Click on the run button to update.

12 Close the query, naming it Increase Price.

 

Part 3:

1 Position yourself in the Queries tab in the Database window of the Cars database.

2 Click on the Create query in design view option.

3 Select the Sold cars table.

4 Click on the Add button.

5 Select the Services table.

6 Click on the Add button.

7 Click on the Close button.

8 Double click on the services field.*.

9 Double click on the Make field to add it to the grid in order to put a search condition.

10 Type Ford in the Criteria row to form the Make = 'Ford' condition.

11 Drop down the button on the toolbar and select the Delete query option.

12 Observe how Access has put Where in the Delete: row of the Make column, and in the Services.* column has put From.

13 Click on the run button to delete the records.

14 Close the query, name it Delete Ford services.

 

Part 4:

1 Position yourself in the Queries tab in the Database window of the Cars database.

2 Click on the Create query in design view option.

3 Select the Ford services table.

4 Click on the Add button.

5 Click on the Close button.

6 Double click on the * field to select all the fields at the same time. We can do this as the Ford services fields are in the same order as the fields in the Services table and have he same names.

7 Drop down the button on the toolbar and select the Append query... option.

8 Type the name of the table where we want to insert the rows, Services.

9 Observe how Access has automatically put Services.* in the Append to: row (all the fields from the Services table).

10 Click on the run button to insert the records.

11 Close the query, name it Recover Services.

 

 

Exercise 2: Clinic

 

Part 1:

1 Position yourself in the Queries tab in the Database window of the Clinic database.

2 Click on the Create query in design view option.

Next will open the Show table box.

3 Select the Patients table.

4 Click on the Add button.

5 Select the Entries table.

6 Click on the Add button.

7 Click on the Close button.

8 Double click on the line that unites the two tables in the tables zone. The Combination properties dialogue box will open.

9 Select the include ALL records from 'Patients' and only those records from 'Entries' where the joined fields are equal.

10 Click on the OK button to close the dialogue box. Observe how the relationship now appears as an arrow in the Entries table, this indicates to us that the query includes all the Patients records.

11 Double click on the Patients. * field to select all the fields in the Patients table at the same time.

12 Double click on the Entry Number field to add it to the grid in order to put a selection condition.

13 Type is null in the Criteria row to form the condition:[Entry Number] is null. The patients that have not been admitted are those that do not appear in the Entries table, and so they will appear in the combination rows with no data in those fields that come from the Entries table.

14 Click in the verification box of the Show row in the Entry Number field so that that it does not appear in the result of the query.

Now we have the query with which to obtain the data to store in the new table.

15 Drop down the button and select the Make-table query... option.

16 Type the name of the new table as Non admitted patients.

17 Click on the OK button.

18 Click on the run button to create the new table.

19 Close the query, naming it Create non admitted patients.

 

Part 2:

1 Position yourself in the Queries tab in the Database window of the Clinic database.

2 Click on the Create query in design view option.

Next will open the Show table box.

3 Select the Entries table.

4 Click on the Add button.

5 Click on the Close button.

We already have the design window with the table added, and now we will add the fields that we need (those that we want to update and those that we need to include in the selection criteria, in our exercise we want to update the Entry Room of the admissions in room 504, so we add Entry Room.

6 Double click on the Entry Room field.

7 Type 504 in the Criteria row to form the condition: [Entry Room]= 504.

8 Drop down the button on the toolbar and select the Update query option.

9 Type 505 in the Update To: row.

10 Click on the run button to update.

11 Close the query, name it Room change.

Part 3:

1 Position yourself in the Queries tab in the Database window of the Clinic database.

2 Click on the Create query in design view option.

3 Double click on Entries table.

4 Click on the Close button.

5 Double click on the Entries. * field to select all the fields in the Entries table at the same time.

6 Double click on the Entry Room field to add it to the grid in order to put a selection condition.

7 Type 201 in the Criteria row to form the condition: [Entry Room]= 201.

8 Uncheck the Show box in Entry Room column.

9 Drop down the button on the toolbar and select the Make-Table query option.

10 Type the name of the new table as Rooms 201.

11 Click on the OK button.

12 Click on the run button to create the new table.

13 Close the query, name it Create room 201.

 

Part 4:

1 Position yourself in the Queries tab in the Database window of the Clinic database.

2 Click on the Create query in design view option.

3 Double click on Entries table.

4 Click on the Close button.

5 Double click on the * field to select all the fields in the Entries table at the same time.

6 Double click on the Entry Room field to add it to the grid in order to put a selection condition.

7 Type 201 in the Criteria row to form the condition: [Entry Room]= 201.

8 Click in the verification box of the Show row in the Entry Room field so that that it does not appear in the result of the query.

9 Drop down the button on the toolbar and select the Delete query option.

10 Observe how Access has put Where in the Delete: row of the Entry Room column, and in the Entries.* column has put From.

11 Click on the run button to delete the records.

12 Close the query, name it Delete Entries.

 

Part 5:

1 Position yourself in the Queries tab in the Database window of the Clinic database.

2 Click on the Create query in design view option.

3 Select the Rooms 201 table.

4 Click on the Add button.

5 Click on the Close button.

6 Double click on the * field to select all the fields at once.

7 Drop down the button on the toolbar and select the Append query... option.

8 Type the name of the table where we want to insert the rows, Entries

9 Observe how Access has automatically put Entries.* in the Append to: row.

10 Click on the run button to insert the records.

11Close the query, naming it Recover Entries .

 



 
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.