Step by step exercise. Summary queries


Objective.

 

Practice the steps to creating summary queries.

 

Exercise 1.

 

We will start by creating a query that tells us how many courses we have, how many hours these involve, the average length in time of a course, and how many hours does the longest course last.

1 Open the Classes.mdb database in the My exercises folder.

2 Position yourself over the Queries tab of the Database window.

3 Click on the Create query in design view query, or, click on the button and select the Design view before clicking on the Accept button.

The Show table box will open next. To know which tables we will need, we need to think from which field it should calculate what it is we are looking for, in our case all the necessary data is in the Courses table.

4 Click on the Courses table so that it stands out.

5 Click on the Add button.

6 Click on the Close button to close the dialogue box.

We already have the table added in the Query design window, and we will now indicate which fields (columns) should be used.

 

7 Double click on the Course code field of the table (we will use this to calculate how many fields there are).

8 Double click on the nºhours field (we will use this to calculate how many hours we have in total).

9 Doble clic again on the nºhours field (we will use this to calculate what the average length is).

10 Double clic again on the nºhours field (we will use this to calculate the length of the longest course).

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

Haz clic en el botón de la barra de herramientas para añadir a la rejilla la fila Total: y convertir la consulta en una consulta de resumen.

12 In the first column of the grid (of the Course code) change the group in+++++++++ value for the value Count++++++++ value in the Total: row. ++++++++++++++++++, furtharmore, we have selected the Course code to be the primary key of the table and so it may not contain any null values. Remember that the Count function does not count the null values.

En la primera columna de la rejilla (la de Código curso) cambia el valor agrupar por de la fila Total: por el valor Cuenta. Queremos saber cuántos alumnos hay en la tabla luego tenemos que contar, además hemos elegido el campo Codigo curso por ser la clave principal de la tabla por lo que no puede contener valores nulos. Recuerda que la función Cuenta no cuenta los valores nulos.

13 Next in the same field, in the Field: row, in front of Course code write nº of courses: so that this text appears as the heading of the column.

Note: As we want to compute records in this case it can be done in a different way, substituting steps 12 and 13 for those that follow next.

12 Substituting the group in++++++++ value for the the value Expression in the Total: row.

Cambiando el valor agrupar por de la fila Total: por el valor Expresión.

13 Substituting the Course code value for the value nº of courses:Count(*) in the Field: row.

14 In the second column of the grid (the first of nº hours) change the group in+++++++ value for the value Sum in the Total: row. We want to know how many hours we have in total, later we need to total them.

15 Next in the same column, in the Field: row , write Total hours: in front of nº hours so that this text appears as the heading of the column.

16 In the third column of the grid (the second of nº of hours) change the group in++++++++++++++ value for the value Average in the Total: row. We want to know the average length later and will need to use the Average function.

En la tercera columna de la rejilla (la segunda de nºhoras) cambia el valor agrupar por de la fila Total: por el valor Promedio. Queremos saber cuál es la media horararia luego tenemos que utilizar la función Promedio.

17 Next in the same column, write Average hour++++++++++++ in front of no.of hours in the Field: row so that this text appears as the column header.

A continuación en la misma columna, en la fila Campo:, delante de nºhoras escribe Media horaria: para que aparezca este texto como encabezado de columna.

18 In the fourth and last column of the grid change the group in: value in the Total: row for the value Max. We want to know how many hours the longest course has and this will later be the maximum value found in the no.of hours column.

19 Next in the same column, in the Field: row , write Longest course length: in front of nº hours so that this text appears as the column header.

 

20 Click on the run button in the Datasheet view to see the result.

Observe how only one row of results appears with the totals that we indicated. Look at the headings of the columns as well, they are the names that you wrote in front of the two points++++++++++++++++++ : in the Field: row.

Observa que aparece una única fila de resultados con los totales que hemos indicado. Fíjate también en los encabezados de las columnas, son los nombres que has escrito delante de los dos puntos : en la fila Campo:

21 Close the query, as this is the first time that we save it we will be asked for a name, name it Summary courses total.++++++++++++++++++++++++

Cierra la consulta, como es la primera vez que la guardas te pedirá un nombre, pónle cursos resumen total.

 

 

Ejercise 2.

 

We are going to create a query to know the number of students recordred in each course, we want the course code to appear, the name of the course, and the number of students.

We will start by creating the query, the source of the data will be the combination of the courses and the students tables as we can extract how many students there by are using the course code in the students table, and as we also want the title of the course we need to combine the two tables.

1 Position yourself in the Queries tab in the Database window if you are not already there.

2 Click on the Create query in design view option.

Next will open the Show table dialogue box.

3 Click on the Students table.

4 Click on the Add button.

5 Click on the Courses table.

6 Click on the Add button.

7 Click on the Close button to close the dialogue box.

With this combination only the students that have courses will appear, we will now change the combination so that the students without courses will also appear.

8 Double click on the line that joins the two tables and select the Include ALL the 'Courses' records and only those records that have the same combined fields in the 'Students'. We already have the data source prepared, and now we only need to define which fields to obtain.

9 Double click on the Course code field of the Courses table.

10 Double click on the Course title field of the Courses table.

11 Double click on the Student code field of the Students table.

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

13 In the first column of the grid (of the course code) leve the group in=++++++ value in the Total: row. We want to count how many students there are in each course and the counting operation should affect all the rows that have the same course code.

En la primera columna de la rejilla (la del código de curso) deja el valor agrupar por de la fila Total:. Queremos contar cuántos alumnos hay en cada curso, la operación de contar debe afectar a todas las filas que tengan el mismo código de curso.

14 In the second column (of the course name) leave the group in:+++++ value in the Total: row. Adding this field to the fields group will not modify the groups that interest us as the Course code value is always associated with the same value of Course name and will not permit us to see it.

En la segunda columna de la rejilla (la del título del curso) deja el valor agrupar por de la fila Total:. Añadir este campo a los campos de agrupación no modificará los grupos que nos interesan ya que un valor de Codigo Curso siempre está asociado el mismo valor de Titulo Curso y nos permitirá visualizarlo.

15 In the third column of the grid (of the Student code) change the group in+++++ value for the Count value +++++ in the Total: row. In this case we could not have used the count(*) option as in the previous exercisefor those courses that have no students.

En la tercera columna de la rejilla (la del Código del alumno) cambia el valor agrupar por de la fila Total: por el valor Cuenta. En este caso no podríamos haber utilizado la función cuenta(*) como en el ejercicio anterior por los cursos que no tienen alumnos.

16 Next in the same column, in the Field: row, write of students: in front of Student code so that this text appears as the column header.

A continuación en la misma columna, en la fila Campo:, delante de Codigo Alumnado escribe nº de alumnos: para que aparezca este texto como encabezado de columna.

17 Click on the run button or the Datasheet view button to see the result.

Observe how a row appears for each course with calculated total.

18 Close the query, as this is the first time that we save the table we will be asked for a name, name it Number of students per course.

 

Exercise 3.

 

We will now create a query to know the number of students from Valencia recordred in this course, we want the name of the course and the number of students to appear. This query will be very similar to the previous one, and only the data source needs to be changed in order that only students from Valencia are counted.

We will start by creating the query.

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

2 Double click on the Number of students per course query,

3 Click on in order to go to design view.

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

5 Name the new query Amount from Valencia.

6 Double click on the City field of the Students table. We need this field to select the students.

7 In the grids column (of the City) change the group in+++++ value for the Where value in the Total: row. In this way we indicate that the condition included in tha Criteria: row affects the rows of the data source and that the records are selected before calculating anything.

8 In the Criteria: row of this same column write Valencia. To form the City = 'Valencia' condition.

En la fila Criterios: de esa misma columna escribe Valencia. Para formar la condición Poblacion = 'Valencia'.

9 Click on the run button .

10 Click on the save button .

11Close the query.

Exercise 4.

 

We will now create a query to know how many students are recordred in each course, we want the name of the course and the number of students to appear, but only those courses that have at least 2 students should appear.

Now the selection condition affects the rows of the result, they are based in the rows of the example and so we will not use the Where option.

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

2 Double click on the Number of students per course query.

3 Click on to go to design view,

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

5 Name the new query Courses with more than 2 students.

6 Write >2 in the Criteria: row of the column that the nº of students is being extracted from. To form the Count(Student code) > 2 condition.

7 Click on the run button .

8 Click on the save button .

9 Close the query.

Note: The query can be improved as it is not necessary for the combination of the two tables to obtain those courses with no students (they do not comply with the condition). You only need to double click on the line that joins the two tables and again leave the first option in the Join properties dialogue box.



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.