Unit 8 exercise help. Summary queries


Exercise 1: Cars

Part 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.

3 Double click on Sold cars table.

5 Click on Close button to close the dialogue box.

6 Double click on License field.

7 Double click on Price field.

8 Double clic again on Price field.

9 Click on the button on the toolbar to add the Total: row to the grid and convert the query into a summary query.

10 In the first column of the grid (License field) change in the Total: row, the Group By value to Expression.

11 In the same column, in Field: row, type Cars sold:Count(*)

12 In the second column (Price field) change the Group By value to Sum.

13 Next in the same column, in Field: row, type sold amount: in front of Price so that this text appears as the column header.

14 In the third column of the grid change the Group By value to Avg.

15 Next in the same column, in Field: row, type Average price: in front of Price so that this text appears as the column header

16 Double click on the Make field (in the table).

17 In this column change the Group By value to Where.

18 Next in the same column, in Criteria: row, type <> Audi so that Audi cars are not taken into account.

19 Click on the run button to see the result.

20 Close the query naming it Summary without Audi.

Part 2:

The trick consists in using the internal values of the Yes/No type fields, the Yes value is stored as the value -1, and the No as 0, and so if we add up the values of a Yes/No type column, we will obtain the number of Yes values that the column contains in negative number, we only need to multiply this value by -1 to obtain its positive.

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 Duble click on the Services table.

5 Click on Close button to close the Dialogue box.

6 Double click on the Oil change field.

7 Double click on the Filters change field.

8 Double click on the Revise Brakes field.

9 Click on the button on the toolbar to add the Total: row to the grid and convert the table into a summary query.

10 In the first column of the grid (Oil change) in t Total: row, change the Group By value to Expression.

11 In the same column, in Field: type Oil changes:sum([Oil change])*-1

12 In the second column (Filters change) repeat steps 10 and 11 changing Oil change to Filters change and changing the header.

13 In the third column (Revise Brakes) repeat steps 10 and 11 changing Oil change to Revise Brakes and changing the header.

14 Click on the button to see the result.

15 Close the query and name it Summary Services.

 

Exercise 2: Clinic

 

For part 1 :

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

2 Click on the Create query in design view.

Next will open the Show table dialogue box.

3 Double click on Doctors table.

5 Double click on Entries table.

7 Click on the Close button to close the dialogue box. The tables should appear combined.

8 Double click on the Doctor name field.

9 Double click on the Doctor surname field.

10 Double click on the Entry Number field.

11 Click on the button on the toolbar to add the Total: row to the grid and convert the query into a summary query.

12 In the first column of the grid (Doctor name) leave the value Group by in the Total: row.

13 In the second column of the grid (Doctor surname) leave the value Group by in the Total: row.

14 In the third column of the grid (Entry Number) change the value Group by to Count in the Total: row.

15 Next in the same column, in the Field: row, type admissions attended to: in front of Entry Number so that this appears as the column header.

16 Next in the same column, in Criteria: row, Type > 3 so that only the rows that contain more than 3 admissions will appear. In this case you do not put Where in the Total: row because the condition is based on the rows of the result of the summary.

17 Click on the button to see the result.

18 Close the query, name it Saturated doctors.

 

For part 2:

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

2 Click on the Create query in design view option.

Next will open the Show table box.

3 Double click on Doctors table.

5 Double click on Admissions table.

7 Click on the Close button to close the dialogue box. The two tables should appear combined.

8 Double click on Doctor Speciality field.

9 Double click on Entry Date field.

10 Click on the button on the toolbar.

11 In the first column (Doctor Speciality) leave the value Group by in the Total: row.

12 In the second column (Entry Date) change the value Group by to MIN in the Total: row. The oldest date will be the minimum value of all the dates.

13 Next in the same column, in Field: row, Type oldest admission: in front of Entry Number so that this text appears as f the column header.

14 Click on the button to see the result.

15 Close the query and name it Oldest admissions.

 



 
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.