ULearnOffice.com

Navigation

Previous
Next
Homepage
FAQ
What's new
Sitemap

Topics

Access
Tables
Queries
Exercices on queries
Forms
Chart form
Reports
Label reports
Macros
Relations

Tutorials

Word
Excel
Excel 2010
PowerPoint
Access

Others

Demonstration files
Texte en français

Word 2010 Tutorials

Contact

By e-mail
Join our Newsletter

Share this page





Access - Exercices on queries

Before we begin
The exercices

Before we begin

This page consists of exercises to help you to understand the potential of the queries. It will help you to perfect your knowledge on the queries. Every exercise becomes more and more difficult. One presumes that you have already read and understood the functioning of the queries. Otherwise, you can always go to the previous page that explains the queries.

To realize the first queries, it is necessary you the table employees, of the data base demoacc1.mdb if you use Access 97. If you have Access on 2000, use the data base demoa2k1.mdb. These files are on the page of the demonstration files that contains all the files used during my demonstrations and the exercises of this site. click here to return you on this page then to return.

The last queries consist of several connected tables. For these, you must use data bases demoacc2.mdb or demoa2k2.mdb as you use Access 97 or Access on 2000 respectively. They are also on the page of the demonstration files.

 

The exercises

The big part of the creation of a query is to be capable of interpreting the composed question so that the options of a query are capable of answering it. To do it, it is necessary of the practice. The next part is a series of exercises to demonstrate some of the possibilities of the queries. These exercises progress in trouble. Each demonstrates a new possible option with the queries. They base themselves on the knowledge that you read on the previous page as well as on the previous exercises.

For each of the questions, think of the way you would answer before seeing the answer. use the stages described on the previous page to choose tables, fields and other options to create a query that answers the wanted question. Then execute the query to see if your answer is the same that that described more low.

1-Show the first and last name of the people having for a first name " roger ".
* Create a simple query.

2-Show the first and last name of all the persons whose last name begins with the letter " L ".
* Use Like *.

3-Show the first name, last name and salary of the persons with a salary superior to 45 000 $.
* =, <, =, <, = and a numeric field.

4-Show the first and last name of the persons hired in 1993.
* Use a Date/Time field.

5-Show the first and last name of the employees with Permanent status according to the alphabetical order of name and first name.
* Use a logical field and sorting of the records.

6-Show the first and last name of the employees having no permanent status.
* Use a logical field and the No operator.

7-By using only the Comments field, show the first and last name of the managers of the company.
* Use of " Is Null " criterion.

8-Show the commission of the salesperson Gendron of Montreal.
* Use of the "And" function.

9-Show the first and last name and the occupation of the salesmen of Montreal and Quebec.
* Use of the "Or" function.

10-Show the first and last name and the income of the employees with an income superior or equal to 45 000 $.
* Use of a monetary field and a calculated field.

11-Show the commission of each of the salesmen if one gives them an increase of 20 %.
* Use a calculated field with a constant (20 %).

12-Show the first and last name of the persons hired in 1993 without using the <= and >= criterias.
* Use the year ([Last name of the field of Date/Time type]) function. Show the expression generator.

13-Show how many years of service has every employee of the company.
* Use of the year() and date() functions.

14-Show next to the first name and to the name from the employees of the company the text "champion" for those that have a 45 000 $ commission or more. place the text "Saddened(been upset") in rib(coast) of those that don't answer the previous criterion.
* Use of the Truefalse(condition; if true; if false) function whose result is text.

15-Show the commission of the salesmen if one gave a 5 000 $ premium to those with a 45 000 $ commission or more.
* Use of Truefalse(condition; if true; if false) function whose result is a number.

16-Show the sum the salary mass of the company.
* Use of the Sum function.

17-Show the sum the salary mass of the company divided by occupation.
* Use of the Sum function with several criterias.

18-Show the sum the salary mass of the company divided by office location and by post.
* Use of the Sum function with several criterias.

19-Show the salary mass of the employees hired in 1993 left(restarted) by office location.
* Use of the Where function.

20-Show the number of persons for the company by office location and by occupation.
* Use of the Count function.

20a-The Count function trap.

21-Determine the number of "champion" salesmen having earned a 45 000 $ commission or more.
* Use of the Count and Where operators.

22-Use a query of analysis crossed to determine the salary mass of the company by occupation in column and by on-line office location.
* Crosstab query with the Sum function.

23-Use a query of analysis crossed to determine the number of employees by social status and the office location with the permanent status.
* Crosstab query with the Count operator and a criterion.

24-To change the office location from the employees from Quebec to Sillery.
* Update query.

25-Delete all the employees whose name is Gendron.
* Delete query.

26-Create a query that shows the first and last name and the date of hiring from the employees the choice of the year of that is left with the user of the query.
* Query with data entry (parametrized query).

The next queries requires fields that you will find in several tables. You should use the data base demoacc2.mdb (for Access 97) or demoa2k2.mdb (for Access on 2000). You will find these files on the page of the files of the demonstrations.

27-Show the number of invoice, name and the address of the customer as well as the first and last name of the salesperson.
* Query based on several tables.

28-Show the name of the employee as well as counts it various products, with description of the product, that he or she sold.
* Query based on several tables connected with the Count operator.

29-Show the total of sales by customers.
* Query based on several tables connected with the Sum function and a calculated field.

30-Use a query to enter the data of invoices.
* Query based on several tables to enter records.

Answers of the queries

1-Show the first and last name of the people having for a first name " roger ".

This query is to begin slowly with a simple query by using of the text. It is about the example developed by the previous page.

1. Choose one or several tables and the necessary queries.

Create a new query by using the Design view.
Of the list of tables and queries, select the Employees table.

2. Choose the type of query.

Make sure to have the query of selection type.

3. Choose one or several necessary fields.

Of the list of fields, add the fields "name" and "First name".

4. Determine if fields need to be sorted out.

For this exercise, the fields do not need to be sorted.

5. Hide fields if needed.

For this exercise, no field needs to be hidden.

6. Determine the criteria of selection.

Write roger in the first criterion row under the First name field.

Here is how the criteria grid should look like.

Criteria

Field:

First name

Last name

Sorting:

 

 

Show:

X

X

Criterion:

" Roger "

 

Or:

 

 

You are not obliged to put quotation marks. Access will put them to indicate that the field is of type "text". Furthermore, you are not obliged to put the operator "=". It's put by default if no other operator is chosen. Besides, you are not obliged to put the text in capital letters or in small letters. Access does not make the difference during the query. It is however necessary to write the criterion correctly. For example, you will find nobody if you wrote to " rooooger "!

For the fields of type Date/Time, Access will place automatically in front of and after date the character "*". So, you are not obliged to put it. All this is to be capable of differentiating the types of fields; " " for text, * for Date/Time and nothing for figures (monetary or numeric).

7. Execute the query.

Execute the query by pressing on the Execute button.

Here is the result.

Result

First name
Last name
Roger
Lepage
Roger
Dubuc

For the next exercises, only the section of the criteria and the result will be shown. Read attentively the question before looking at the answer. Try to imagine the query before looking at the answer. There will be additional explanations on the new options that every exercise will bring.

2-Show the first and last name of all the persons whose last name begins with the letter " L ".

This query serves for seeing the special characters such as " and ". The character "?" Is useful to replace a character in the query. For example, a query on b? Lle could give the following results: ball, bubble swings, beautiful. Besides being useful for the fields of type text, he can also be uses with the fields of type Date/Time. For example, 98-??-01 could show all the records of the first day of every month.

The character "*" serves for replacing an indefinite series of characters. One knows the beginning but not the end. For example, a query by using bal* could give the following result: balance, balcony, ball, ballerina, ballet...

*For the criterion of the "name" field, write only l*.

Access will take care to write "Like" to indicate that all the records must begin with the letter " l ".

Criteria

Field:

First name

Last name

Sorting:

 

 

Show:

X

X

Criterion:

 

Like l*

Or:

 

 

Execute the query by pressing on the button.

Result

First name

Last name

Roger

Lepage

Denis

Lambert

Elects

Lavigueur

3-Show the first name, last name and salary of the persons with a salary superior to 45 000 $.

This query demonstrates the possibilities of using the operators <,>, < =, and > = for the fields of numeric or monetary type. For this exercise, the research criterion uses a field of monetary type. The other types of fields can be also used with these operators.

Criteria

Field:

First name

Last name

Salary

Sorting:

 

 

 

Show:

X

X

X

Criterion:

 

 

> 45000

Or:

 

 

 

Result

First name

Last name

Salary

Roger

Lepage

50000

Summits attention by reading the question. One asks you superior in X or equals and superior in X? It is the small trap that some don't pay attention.

4-Show the first name and last name of the persons hired in 1993.

This query could use the operators > =, and, < =. But the exercise will demonstrate the operator "Entre" and the type of field Date/Time. notice "*" in front of and after date. If you don't put them, Access will write them for you. It is to identify that the field Engages is of type Date/Time.

Criteria

Field:

First name

Last name

Hiring date

Sorting:

 

 

 

Show:

X

X

X

Criterion:

 

 

Between #93-01-01# and #93-12-31#

Or:

 

 

 

Result

First name

Last name

Hiring date

Suzanne

Rémi

93-01-01

Éric

Gendron

93-06-06

Roger

Dubuc

93-06-06

Elects

Lavigueur

93-06-01

Paul

Gendron

93-01-01

Dependent on options selected in Windows and Access, it is possible that the date format is different. You should probably put the month front and in the daytime and the year. Summits some attempts by changing the order of the year, of the month and of day.

5-Show the first and last name of the employees with permanent status according to the alphabetical order of name and first name.

This query looks at the possibilities with the field of logical type Yes/No and also to sort out the result of the query. Furthermore, one uses the field Permanent to filter the records. But this one is not shown during the presentation of the information. make sure that the box View is deactivated for this field.

Criterion

Field:

Last name

First name

Last name

Permanent

Sorting:

Ascending

Ascending

 

 

Show:

 

X

X

 

Criterion:

 

 

 

Yes

Or:

 

 

 

 

Please remember! The sorting priority goes to the field most to the left and so on; even if this one is not shown!

Result

First name

Last name

Roger

Dubuc

Denis

Lambert

Elects

Lavigueur

Roger

Lepage

Suzanne

Rémi

6-Show the first and last name of the employees having no permanent status.

It would be easy simply to put the criterion not under the field permanent status. But this example is especially to demonstrate the operator No. This one shows all the information except those that you selected. In that box, it will show all those that are different of yes. So, the only possibility is not.

Criterion

Field:

First name

Last name

Permanent

Sorting:

 

 

 

Show:

X

X

 

Criterion:

 

 

Not yes

Or:

 

 

 

Result

First name

Last name

Éric

Gendron

Paul

Gendron

Another example would be to show all the employees except those of Quebec.

Note:
There is a characteristic of the fields of type Yes/No that you should know. Although it is shown Yes/No, the True / False in the screen, the information is kept in the table under numeric format. Access registers 0 (zeros) when false and 1 when true. With a little of imagination, you can really take advantage of this situation.


7-By using only the Comments field, show the first and last name of the managers of the company.

If you look at the contents "Comments" field, you will notice that all the persons have one except the managers. Then, you can ask to Access to look for the records where that field is empty. To do so, you must use the criterion " Null ".

Criterion

Field:

First name

Last name

Comments

Sorting:

 

 

 

Show:

X

X

X

Criterion:

 

 

Is Null

Or:

 

 

 

Result

First name

Last name

Comments

Roger

Lepage

 

Roger

Dubuc

 


8-Show the commission of the salesperson Gendron of Montreal.

This query demonstrates the possibility of hiding fields as well as the possibility of using several criteria at the same time. You can combine several criteria at the same time if they are on the same row of criterion. If the criteria are on different rows, it is the equivalent to show the result of two different searches.

Criterion

Field:

Occupation

Last name

Office

Commission

Sorting:

 

 

 

 

Show:

 

 

 

X

Criterion:

"salesperson"

" Gendron "

"Montreal"

 

Or:

 

 

 

 

Result

Commission

23 000 $

9-Show the first and last name and the occupation of the salesmen in Montréal and in Québec.

Be careful! There is a trap in the question.

It is about a question of logic. You ask the question: can you be in Montreal AND in Quebec at the same time? No. But you can be for the one OR another. It is a question just of interpreting correctly the question that is presented to you. It is not always as simple as that.

Criterion

Field:

First name

Last name

Occupation

Office

Sorting:

 

 

 

 

Show:

X

X

X

X

Criterion:

 

 

salesperson

Montreal

Or:

 

 

salesperson

Quebec

You could also answer this question in this way:

Criterion

Field:

First name

Last name

Occupation

Office

Sorting:

 

 

 

 

Show:

X

X

X

X

Criterion:

 

 

salesperson

Montreal or Quebec

Or:

 

 

 

 

Result

First name

Last name

Occupation

Office

Denis

Lambert

salesperson

Montreal

Suzanne

Rémi

salesperson

Montreal

Éric

Gendron

salesperson

Montreal

Elects

Lavigueur

salesperson

Quebec

Paul

Gendron

salesperson

Quebec

This question demonstrates that it is possible to combine criteria under format AND and OR. The criteria on the same row are added (AND) one to another. The criteria that are on another row offer another possibility to look of the information (OR).


10-Show the first and last name and the income of the employees with an income superior or equal to 45 000 $.

One of the big advantages of the queries is the possibility to create calculated fields. You can show the result of a formula that uses the information resulting from the other fields of the record. For this example, the income is calculated by the sum the salary and the commission of the employee.

You should write the field calculated in one of the boxes of the row Field. The calculated field consists of two parts: the name and the formula. They are separated by one ":". For the exercise, you must create the field calculated for the income of every employee.

Place the cursor in one of the boxes empty of the row Field.
write the name that you want to give to your field. For the exercise, write "Income".
write one: to separate the name of the field of the formula that you need. It is after it: that you write your formula.
write the formula salary + commission

Ex: name:formula , tps:total*0,07, tvq:(total+tps)*0,75 etc.

So, when you create your table, he should not have of fields the result of that you can find from the other fields of the record. The example above demonstrates that it is useless to have a field TPS and TVQ in a table because it is easy to calculate them.

Be careful!
If the name of the field consists of several words, you should put it between hooks [ ]. Ex: [Date of birth], [Tax on products and services].

It often arrives after you executed a query with a calculated field that Access asks for the value of the argument. A window appears with the message and indicates also the name of a field. The reason that this window appears is simple: you made an error of spelling on one of the names of field. It is absolutely necessary that the name of the field in the box of the calculated field is identical the one you to gave in the table or in the query.

For this exercise, some forget to put two " m " or two " s " in the field commission. Some put even a " s " at the end of the field Commission or the Salary field. You should write it in the same way as it is written in the table.

Criterion

Field:

First name

Last name

Income: salary + commission

Sorting:

 

 

 

Show:

X

X

X

Criterion:

 

 

> 45000

Or:

 

 

 

Result

First name

Last name

Income

Roger

Lepage

50 000 $

Suzanne

Rémi

65 000 $

Elects

Lavigueur

47 000 $


11-Show the commission of each of the salesmen if one gives them an increase of 20 %.

This query uses once again a calculated field. It is about a reproduction instead of an addition.

Criterion

Field:

Occupation

Adjusted: commission * 1,2

Sorting:

 

 

Show:

 

X

Criterion:

"salesperson"

 

Or:

 

 

Result

Adjusted

54 000,00 $

78 000,00 $

27 600,00 $

56 400,00 $

26 400,00 $

If you want it, correct this query to add the first and last name of the salesmen in front of their incomes.

12-Show the first and last name of the persons hired in 1993 without using the < = and >= criterias.

It would be too easy to find the answer by using the criterion "Entre". This query is to show to you what is possible to carry out with the functions of Access and the expression generator.

In date all the queries were carried out with simple research criteria. Access a list of functions prepared offers you to carry out more elaborated searches. For the exercise, it is necessary uses the function year () that meets itself in the category Date/Time. This function stands out in the form of figure the year from the field of the type Date/Time that meets itself there.

A field of type Date/Time contains a lot of information. There is a year, a month, a day, the hour, the minutes, the seconds and even the watches of seconds that are kept there. Access offers you functions to analyze that the part that interests you of these data.

For example, if the field of type Date/Time Date of birth is 1999-07-02, the function year ([Date of birth]) will give the figure 1999.

The same concept applies to the other functions. Ex:

Month ([field of type Date/Time])

Figure from 1 to 12 (in January in December)

In the daytime ([field of type Date/Time])

Figure from 1 to 31 (1-st in the 31-th day of the month)

Joursem ([field of type Date/Time])

Figure from 1 to 7 (On Sunday in Saturday)

Access offers several other functions that one can directly write in the box or by using the expression generator that will be used for this exercise.

* Add fields First name and Last name to the list of the fields of the query.
* Place the pointer in the third box of the row of fields: in the right-hand side of the field Last name.
*From the toolbar of the queries, press the button .
OR
* Press the right mouse button.

Menu

*From the context menu, select the Generate option.

The window of the expression generator will appear.

It is from this window that you have access to some of Access's resources. You can you to look for all the objects of all the fields, the queries, the forms, the reports of your data base. Furthermore, you have also access to the functions integrated by Access. These give you a lot of flexibility at the time of the creation of your queries. If you are interested, you can also create your own functions.

It is in the box of the height that will be registered the formula for the field calculated by this exercise. You can in certain boxes combine functions of Access to reach(affect) the result that you want. Ex: Abs (CMonnaie ([field of type text])). Just down, it there of the buttons for the mathematical functions and the various criteria of selection. In three columns down, it is possible to reach all the Access's objects. For the exercise, you must look for the function integrated Year().

To see the contents of the files that have one "+" on the top, you must make(do) a "double clickk" on the file in question.

*Double-click on the Functions folderFile functions(offices).
Click the integrated Functions folder .

The contents of the second and the third column will fill(perform). The second column contains the list of the categories of functions. The third column contains the list of the available functions. The function Year() is a part of the category Date/Time.

*In the second column, click the Date/Time category.

The contents of the third column change only to show the functions of the chosen category, that is that of the Date/Time. The popular function is in the second of the list. To insert it into the section of the formula:

*Double-click on the Year() function.
OR
Click the Year() function.
* Press Paste button.

The contents of the box of formulation will be: Year ("Number").

It is enough now to replace "Number" by the field of type necessary Date/Time for the query, that is the field Engages.

*Delete the text "counts", including quotation marks but not the brackets.
* leave the cursor between both brackets.
*Double-click on the file Tables of the first column.
*Still in the first column, click the Employees table.

The contents of the second and the third column will change to show the possibilities from this table.

*In the second column, "double clickk" on the Engages field.
OR
Click the field Engages and press the Paste button.

The text [Employees]! [Hiring] should appear between both brackets. This is to indicate that the Engages field of the Employees table was selected.

The creation of the function is ended. The result should be year ([used(employed)]! [Hiring])

* Press the OK button.

Access then returns you to the queries' Design view.

* Press the Enter key to end the entry of the formula to the box of fields.

Access will add " Expr1: " in front of the formula. Access considers this function as a field calculated by the previous exercises. A calculated field always consists of a name, followed by one two points and the calculation. You can if you want to change him(it,her) the text " Expr1 " by something more appropriate such as " Year of hiring ".

Criterion

Field:

First name

Last name

Expr1:year([Employees]![Hiring date])

Sorting:

 

 

 

Show:

X

X

 

Criterion:

 

 

1993

Or:

 

 

 

Result

First name

Last name

Suzanne

Rémi

Éric

Gendron

Roger

Dubuc

Elects

Lavigueur

Paul

Gendron

Somebody found another way of answering this question.

Criterion

Field:

First name

Last name

Hiring date

Sorting:

 

 

 

Show:

X

X

 

Criterion:

 

 

93-??-?

Or:

 

 

 

Although this way works, it does not demonstrate the possibilities of the functions. This exercise demonstrated that it is possible to use a function for a calculated field. But, you can also use the functions for the criteria of your query.

13-Show how many years of service has every employee of the company.

This exercise demonstrates a little better the advantage of the functions in calculated fields. IT demonstrates also the function that is the date of the computer on that you work. It becomes so easy, with the function year (), to make(do) a subtraction to calculate the number of years of service. The following result is correct if the query was made in the year 2002.

Criterion

Field:

First name

Last name

Service:year(date()) - year([Hiring date])

Sorting:

 

 

 

Show:

X

X

X

Criterion:

 

 

 

Or:

 

 

 

Result

First name

Last name

Service

Roger

Lepage

10

Denis

Lambert

10

Suzanne

Rémi

9

Éric

Gendron

9

Roger

Dubuc

9

Elects

Lavigueur

9

Paul

Gendron

9

There are also the other functions of the group Date/Time who could interest you. There is a now() function that gives not only the date but also the hour of the computer. So, instead of having just the 98-01-01 with the date() function, it is possible to have with the now() function 98-01-01 12:00:00. There the other functions such as in the daytime(), joursem(), month() and several others that are available in the category Date/Time. It is not necessary to forget that Access offers you 176 functions distributed in 16 categories.

Note on the Date/Time fields types.
Although Access shows the information in the form of date or of the hour, the information is kept in the software under numeric format. For example, the figure 37222 s on November 27, 2001. So 37222,5 s at noon on November 27, 2201.

14-Show next to the first name and to the name from the employees of the company the text "champion" for those that have a 45 000 $ commission or more. Place the text "Sorry" for those that don't answer the criterion.

This exercise is to demonstrate you the function Vraifaux to show of the text. IT works on the same principle as the function =Si of Excel. IT needs it three information, or three "parameters", to work correctly: the condition, that to make(do) so really and that make so false. These parameters are separated by a semicolon (;) .On asks you here to write the text "Fieldion" if the person has a 45 000 $ commission or more. Otherwise, you must show the "Sorry" text.

Criterion

Field:

First name

Last name

Criteria:vraifaux ([commission] > = 45000; "Champion"; "Sorry")

Sorting:

 

 

 

Show:

X

X

X

Criterion:

 

 

 

Or:

 

 

 

Result

First name

Last name

Criterion

Roger

Lepage

Sorry

Denis

Lambert

Fieldion

Suzanne

Rémi

Fieldion

Éric

Gendron

Sorry

Roger

Dubuc

Sorry

Elects

Lavigueur

Fieldion

Paul

Gendron

Sorry

For this exercise, the function Vraifaux was used to show of the text. You can also use it also to show figures as in the next exercise.

15-Show the commission of the salesmen if you gave a 5 000 $ premium to those with a 45 000 $ commission or more.

It is in this kind of situation of the function vraifaux is really advantageous. IT applies only if the required conditions are filled. In the previous exercise, the function Vraifaux() was used with the text. Here the function is used to calculate a number.

Criterion

Field:

First name

Last name

Ajusted:Truefalse ([commission] > = 45000; [commission]+5000);[commission])

Sorting:

 

 

 

Show:

X

X

X

Criterion:

 

 

 

Or:

 

 

 

Result

First name

Last name

Adjusted

Roger

Lepage

0

Denis

Lambert

50 000

Suzanne

Rémi

70 000

Éric

Gendron

23 000

Roger

Dubuc

0

Elects

Lavigueur

52 000

Paul

Gendron

22 000

Can you prepare a query that demonstrated the difference to the salary mass with and without this premium? You should be capable if you understood well the previous exercises.

16-Show the sum the salary mass of the company.

In date, the questions showed the information according to the wanted criteria. But what can you do when one needs to find the sum, the average or the number of records that answer certain criteria? It is for these circumstances that there are the functions.

There is a very powerful function that was not covered until now: the functions of grouping. It is possible with the functions to group together(include) the records to be able to add them, count them, find the average, the smallest, the biggest and several others. It is also an opportunity to see again(revise) fields calculated to determine the salary mass of the company. One uses once again a field calculated to determine the income (salary + commission) from every employee.

*From the toolbar, press the button.
OR
*From the View menu, select the Functions option.

A new row fitted below the row of fields. It is the row of the functions. Access allows you to carry out mathematical functions on the records of a query. If you look at the possible functions, you will find the others the sum, the average, the smallest ( min ), the biggest ( max ) etc.

*In the first column, write the following formula: Income: [salary] + [committee([commission]).

List of the operations

*From the row of the functions, select the function of Sum for the income.

Note:
Several persons mix the functions Sum and Count. The function Sum is used for the addition of figures of fields of numeric type or monetary type. The function Counts is used to count the number of records that answer the selected criteria.

It's as well to note as it is impossible to put a criterion under the functions except for the functions Grouping, Count, Expression and Where. This last one will be used for the exercise number 19.

Criterion

Field:

Income: [salary] + [commission]

function:

Sum

Sorting:

 

Show:

X

Criterion:

 

Or:

 

Result

Income

295 000 $

There is also another way of realizing the query by using the function adds. Here is what it would look like.

Criterion

Field:

Income: sum([salary] + [commission])

Sorting:

 

Show:

X

Criterion:

 

Or:

 

In that box, it is not really more practical than the function adds. IT is however more practical when it is used in a field calculated with the other functions. For example: sum([commission] + [salary] / count([Offices]). In passing, this last formula is the equivalent of average of ([salary] + [commission]).

17-Show the sum the salary mass of the company divided by occupation.

It is once again about a query that needs an function. A field more is needed however to have the necessary detail. In that box, you must add the field office(desk) to be able to distribute incomes by post.

* Of the toolbar, press the button.
OR
*From the View menu, select the functions option.

Criterion

Field:

Occupation

Income: salary + commission

function:

Grouping

Sum

Sorting:

 

 

Show:

X

X

Criterion:

 

 

Or:

 

 

Result

Occupation

Income

Manager

93 000 $

salesperson

202 000 $

The salary mass of the company is now distributed according to the job(workstation): manager or salesperson. For the example, there are only two posts(post offices). If there would have been more that it, every occupation would be shown with its equivalent income.

18-Show the sum the salary mass of the company divided by office location and by post.

It is also about a query that needs an function (sum((dream)). It is some more necessary to add of details that both last queries. In that box, you must add fields Office and occupation besides calculating incomes.

* Press the button.
OR
*From the View menu, select the functions option.

Criterion

Field:

Office

Occupation

Income: salary + commission

function:

Grouping

Grouping

Sum

Sorting:

 

 

 

Show:

X

X

X

Criterion:

 

 

 

Or:

 

 

 

Result

Office

Occupation

Income

Montreal

Manager

50 000 $

Montreal

salesperson

133 000 $

Quebec

Manager

43 000 $

Quebec

salesperson

169 000 $

The more that you add of fields, the more that the answer will be detailed. In the exercise 11, there was only a field for the total of incomes. In the exercise 12, there was besides the field of incomes that of the posts. The result of the query is more detailed than before. With this question and three fields in the posting, the result is even more detailed.

19-Show the salary mass of the employees hired in 1993 by Office location.

You noticed in the last three exercises that every time you add a field to the query that the answer becomes more and more detailed. But, what can you do when you must make a function with criteria without detailing(retailing) on these? There is an function to settle(adjust) this situation. The function "Where" is used when you want to add a condition to a query without for all that detailed the answer on this condition. For the example, the field Engages is used for tourver the employees who were hired in 1993. However, the income is not distributed for each of the dates of hiring of this year.

Criterion

Field:

Hiring

Office

Income: salary + commission

function:

Where

Grouping

Sum

Sorting:

 

 

 

Show:

 

X

X

Criterion:

Between 93-01-01 * and 93-12-31 *

 

 

Or:

 

 

 

Result

Office

Incomes

Montreal

88 000 $

Quebec

112 000 $

If you had not used the function "Where", and left with the function grouping for the field Engages, the result would have shown the cumulative of incomes by office(desk) and by date of hiring from the employees of the company.

20-Show the number of persons for the company by office location and by occupation.

This exercise is to demonstrate you the functioning of the function Counts. This function, as the name indicates it, counts the number of records that answer the wanted criteria. So, as for all the functions, every time you add a field to the query, the answer becomes more and more detailed.

* Press the button.
OR
*From the View menu, select the Functions option.

Criterion

Field:

Office

Occupation

First name

function:

Grouping

Grouping

Count

Sorting:

Ascending

Ascending

 

Show:

X

X

X

Criterion:

 

 

 

Or:

 

 

 

Result

Office

Occupation

CountOfFirst Name

Montreal

Manager

1

Montreal

salesperson

3

Quebec

Manager

1

Quebec

salesperson

2

20a-The trap of the Count function

The function Counts calculates the number ' records that answers the criteria that you chose. There is however a situation or this one could underestimate the exact number. Let's resume the last example with a small modification. Instead of making(doing) the account on the field First name, use the field Likent.

Field:

Office

Occupation

Likent

function:

Grouping

Grouping

Count

Sorting:

Ascending

Ascending

 

Show:

X

X

X

Criterion:

 

 

 

Or:

 

 

 

Result

Office

Occupation

CompteDeLikentaire

Montreal

Manager

0

Montreal

salesperson

3

Quebec

Manager

0

Quebec

salesperson

2

There are no more managers! Why is there a difference? Access does not add record of that the contents of the field are empty. As you saw it in the exercise 7, the contents of the field how are empty for the managers of the company. It is so strongly suggested always to use with the function Counts a field of that one is certain that there are a contents. You can one of the other fields of the query or field that serves as primary key of the table. By definition, this one can never be empty.

21-Determine the number of "champion" salespersons that earned a commision of 45 000 $ or more.

The exercise consists in using the function Counts to know the number of persons who answer the criteria mentioned above. This exercise looks like the exercise 19 a lot. The difference is that this exercise uses the function Counts instead of the function Sum. Because the managers of the company have no commissions, the field Posts is useless only to count the salesmen.

* Press the button.
OR
*From the View menu, select the Functions option.

Criterion

Field:

Number:Prénom

Commission

function:

Count

Where

Sorting:

 

 

Show:

X

 

Criterion:

 

> 45000

Or:

 

 

Result

Number

3

22-Use a query of analysis crossed to determine the salary mass of the company by occupation in column and by on-line office location.

To create a crossed analysis, you need at least three fields. The first one is the Header column. It is the values that will be shown at the top of every column of the crossed picture of analysis. For this exercise, it will be the contents of the field Post. So, the columns of the picture will represent each of the values that you will find in the field. Because there are only two activities(occupations), managing and salesperson, the picture will have only two columns with these values.

The second element necessary for a crossed analysis is to have at least a Header row. It is the field that will detail the values of columns. It is also possible to you to add the other Headers row to describe with more details the results. For this exercise, this field will be the Office field.

The last element in the creation of a crossed analysis is a value. It is a number or an function as those used for the exercises from 16 to 21 (sum(dream), average, account...). For the purpose of the exercise, it is once again necessary to use the Returned calculated field that is the total of the salary and the commission of each from the employees of the company (returned: [salary] + [commission]). A crossed picture of analysis can only have an function at the same moment. So, if you need to have the sum and the average of the salary mass, will be needed two analyses with different functions (Sum and Averages for example).

*Create a new query in Design view.

It would have been possible to use the assistant of crossed analysis if the exercise did not use the returned calculated field.

*Among the list of tables and queries, select the Employees table.
* Select a query of crostab query by selecting the button.
OR
*From the Query menu, select the Crostab query.

* Select the Office Occupation field and.
*On the row of fields, in the right-hand side of both fields that you have just added, write the formula for the calculated field: Income: [salary] + [committee([commission]).
*In the row of the functions, select the function Sum under the Returned field. leave the function Grouping with the two other fields.
*In the row of analysis, select the option Header column under the Occupation field.
*In the row of analysis, select the option Header row under the Office field.
*In the row of analysis, select the option Value under the Returned calculated field.

Here is a representation of fields and selected options.

Field:

Occupation

Office

Income: [salary] + [commission]

Table:

Employees

Employees

 

function:

Grouping

Grouping

Sum

Analysis:

Column header

Line header

Value

Sorting:

 

 

 

Criterion:

 

 

 

*Execute the query by pressing on the button.

Result:

Office

Manager

salesperson

Montreal

50 000,00 $

133 000,00 $

Quebec

43 000,00 $

69 000,00 $

You can add the other fields on the row of analysis if it uses the Header row. The result will be more detailed if there are more fields that use the heading row. For example, you can add the field Permanent to differentiate the salary mass from the employees who have or not the permanent status inside the company. Or, you can use a field calculated with the function Year ([hiring]) to determine the salary mass by the number of years of experience(experiment) in the company. It is to you also possible of resources of the other fields for criteria of selection. If you don't want that these fields display in the picture, use the option not shown on the row of analysis.

23-Use a crosstab query to determine the number of employees by social status and by office location with the permanent status.

This exercise demonstrates that it is possible to have a crossed analysis that uses the function Counts and criteria. Instead of with a picture containing all the employees, this analysis will only have the employees with the permanent status in the company.

Criterion:

Field:

Office

Statut Social

Commission

Permanent

Table:

Employees

Employees

Employees

Employees

function:

Grouping

Grouping

Count

Grouping

Analysis:

Column header

Line header

Value

 

Sorting:

Ascending

Ascending

 

 

Criterion:

 

 

 

Yes

*Execute the query by pressing on the button.

Result:

Social status

Montreal

Quebec

1

1

2

2

 

3

 

2

There are five persons from the list who have the permanent status; three in Montreal and two in Quebec. With the description of the numbers, one knows that there is a bachelor 1 , two married persons 2 and two persons are married with children ( 3 ).

24-To change the office location from the employees from Quebec to Sillery.

It often happens after the entered of the initial data that there are corrections of massive data. You can always change manually the contents of fields. But, you can also, with the Updated query of type, change the contents of one fields more quickly and effectively and without having to worry about errors of spelling.

Contrary to a query of type selection or of type Analyzes crossing, a query of action modifies the contents of a table according to the criteria that you chose. Here, it consists in modifying the contents of the field Office to Sillery for all the records are the contents is at present Quebec.

*Create a new query in Design view.
*From the list of tables and queries, select the Employees table.
* Select the type of Updated query by pressing on the button.
OR
*From the Query menu, select the updated Query option.

*From the list of fields, select the Office field.
*In the row Updated under the Office field, enter the text: Sillery.
*In the row of the criteria under the Office field, enter the text: Quebec.

Access is automatically going to add quotation marks around Sillery and Quebec because the field Office is of type Text.

Field:

Office

Table:

Employees

Update:

" Sillery "

Criterion:

"Quebec"

*Execute the query by pressing on the button.

Access will ask you last time to confirm the modification at the table of the data base.

*Press the OK button to confirm the modification.

You can also create an Updated query of type by using several fields for the criteria of selection or for the update. You can also invert this function by changing place Quebec and Sillery in the grid of the criteria.

It is also possible to you to verify if the records to be modified answer all the criteria that you selected before the update. use the type of query Selection to show the records with the criteria of your choice. When you are satisfied that the records should be modified, change the type of query of Selection Update.

25-Delete all the employees whose name is Gendron.

It is here about a query of action of type deletion. It deletes all the records of the table that answer the criteria that you select.

*Create a short story(piece of news) in Design view.
*From the list of tables and queries, select the Employees table.
* Select the type of query of Delete type by pressing on the button.
OR
*From the Query menu, select the Delete Query option.

* Select the field Last name.
*In the criterion under the field Last name, enter the text: Gendron.

Here is the representation of the options chosen as this query.

Field:

Last name

Table:

Employees

Delete:

Where

Criterion:

Gendron

*Execute the query by pressing on the button.

Access asks you to confirm the deletion of the records that answer the selected criteria.

*For the purpose of the exercises, keep the records by selecting the No option.

It is also possible to you to create a query of deletion that uses several criteria and several fields.

Before deleting records, you can verify if your criteria answer your needs. Instead of choosing a query of type deletion, select a query of type selection. You can then show the records with the criteria of your choice and make sure to have chosen the good records. If you have the good records, you can change the type of query a query deletion and execute it.

This possibility is very advantageous to avoid errors that could be expensive in loss of data and at time to retranscribe the data (if possible).

26-Create a query that shows the name, the first name and the year of hiring from the employees the choice of the year of that is left with the user of the query.

Contrary to the other queries where the criteria of selection were determined in the mode of creation, this query gives a choice to the user by asking it a question. This query is also going to ask you to use a field calculated with the function Year() on the field of type Date/Time Engages.

*Create a new query in Design view.
*From the list of tables and queries, select the Employees table.
* Select the type of query Selection by pressing on the button.
OR
*From the Query menu, select the Query selection option.

*From the list of fields, select fields First name and Last name.
*In the right-hand side of the previous fields on the row of fields, enter the following calculated field: Year of hiring: Year ([hiring]).
*On the row of the criteria under the field calculated Year by hiring, enter the following text: [What is the year of hiring? ( 4 figures) Ex: on 1998].

Here are fields and options for the criteria.

Field:

First name

Last name

Year of hiring: Year ([hiring date])

Table:

Employees

Employees

 

Sorting:

 

 

 

Show:

X

X

X

Criteria:

 

 

[ What is the year of hiring date? ( 4 numbers) Ex: on 1998]

*Execute the query by pressing on the button.

*For the purpose of the exercise, enter 1993 the box of the value of the argument.
* Press on the OK button.

Access will show the following result.

Suzanne

Rémi

1993

Éric

Gendron

1993

Roger

Dubuc

1993

Elects

Lavigueur

1993

Paul

Gendron

1993

Instead of asking a question, the query can look for the information that comes from a field located in a form. The criterion would look like this: [Forms]! [Last name of the form]! [Last name of the field]. It is in this way that one begins to create a "more formal" presentation for the data. You can use the expression generator by pressing on the button to fetch controls them that you need in the data base.

27-Show the number of invoice, name and the address of the customer as well as the first and last name of the salesperson.

In date, all the exercises that you saw required that a table; the table Employees. However, the most powerful queries can use the information resulting from several tables or from connected queries. The next exercises consist in creating queries using several tables.

Previously, it is necessary that to protect you on your floppy disk or on your hard drive a copy of the data base demoacc2.mdb. Because of difficilté from the Browser Netscape, the file was "compacted", or " zipped ", under the name demoacc2.exe. It contains the data base. It is enough to execute the program so that this one takes out you the necessary data base for the next exercises. For those that use Access on 2000, select the file demoa2k2.exe. This data base contains several tables with that it will be possible to connect them. You will find this file on the demonstration files Web page.

To have more information about the relations between tables and queries, you watch to produce to the page on the relations between tables or queries All the functions that you saw in the previous exercises are also possible with tables or connected queries.

*Create a new query in Design view.
*From the list of the available tables, select tables Customers, Invoices and Employees.

It is now necessary to add fields to the query.

*From the table Invoices, select the field Number of invoice.
*From the table Customers, select fields Last name and Address.
*From the Employees table, select fields First name and Last name.

It would be possible from this point to execute the query. However, you would have no deliberate result because there are no relations between tables. Access would show all the possible combinations. The next stage consists in creating the relations between the tables of this query. Previously, it is first necessary to see that are fields in common between these tables. Between tables Customers and Invoices fields in common are No. Customer and Codes Customer respectively. Enter tables Invoices and Employees, fields in common are Employee no and Numéro of poste (Employee's ID number). All these fields are the same types and the same lengthes. But especially, they contain the same kind of information; the reference number on the customer or on the employee.

*Place the pointer on the field No. customer of the table Customers.
*While pressing the left mouse button , move the pointer on the field Codes customer of the table Invoices.
*Release the mouse button..

Connected tables

A row will appear between these two fields. This indicates that there is a relation between both tables. These two tables are now connected on a field in common. This makes possible to reach the information of the other table. For example, by knowing the reference number of the invoice, it is possible to know the name, address as well as all other information about the customer.

Here is it that you must do to remove the relation. In the box, or you would have made the relation on different fields that those mentioned.

Click on the row that connects both tables.
From the Edit menu, select the Delete option.
OR
Press on the Delete key.

It remains to connect tables Invoices and Employees on their fields in common: No. salesperson and Number of the post.

*Place the pointer on the field No. salesperson of the table Invoices.
*While pressing the left mouse button , move the pointer on the field Numéro of poste (Employee's ID number) of the Employees table.
* Release the mouse button.

3 tables connected

The final result should look like this. If you have not this result, redo the relations between tables.

Field:

No. Charge

Last name

Address

First name

Last name

Table:

Invoice

Customers

Customers

Employees

Employees

Sorting:

 

 

 

 

 

Show:

X

X

X

X

X

Criteria:

 

 

 

 

 

*Execute the query by pressing on the button.

Result:

No. Charge

Customer. Last name

Address

First name

Employees. Last name

23

Alpha Ltée

1000 Lavigne

Denis

Lambert

24

Béatrix Inc.

On 2000 Of Ormeaux

Suzanne

Rémi

25

Charles and Cie.

3000 Sts - Hubert

Éric

Gendron

26

Demix Inc.

4000 Manufacturer

Elects

Lavigueur

At the time of the view of the result, Access makes(does) the difference between the field Last name of the table Customers and of the Employees table by showing the name of the table in front of the name of the field (Customers. Last name and Employees. Last name).

28-Show the name of the employee as well as counts it of products that he or she sold.

This query requires also the contents of several tables to realize it. But it demonstrates also the "indirect" links between the tables of the data base. Although there are no "direct" links between tables Employees and Inventory, it is possible "to connect" them by using tables Invoice and Transition Fact-Inv. In this way, it is possible to have in the same query fields Last name and First name of the table Employees and Description of the table Inventory.

Another difference between this exercise and the previous is the use of the function Counts to know how many items were sold and by whom. It is very possible that there is a bonus if the salesperson reached(affected) his quota for an item. Here is the list of tables and relations that you need.

If you don't know how to connect tables, watch to read the previous exercise.

Relations enter tables Employees, invoice, Trans Fact-Inv and Inventory

Criterion:

Field:

First name

Last name

Description

Quantity

Table:

Employees

Employees

Inventory

Transition Fact-Inv

function

Grouping

Grouping

Grouping

Count

Sorting:

 

 

 

 

Show:

X

X

X

X

Criteria:

 

 

 

 

*Execute the query by pressing on the button.

Result:

First name

Last name

Description

CountOfquantity

Denis

Lambert

Cycle of mountain

1

Elects

Lavigueur

Cycle of mountain

1

Éric

Gendron

Board with snow

1

Éric

Gendron

Cycle of mountain

2

Paul

Gendron

Ice skates

2

Paul

Gendron

Ski

1

Paul

Gendron

Cycle of mountain

2

Roger

Lepage

Board with snow

1

Roger

Lepage

Cycle of mountain

1

Suzanne

Rémi

Board with snow

1

Suzanne

Rémi

Ski

2

Suzanne

Rémi

Cycle of mountain

1

29-Show the total of sales by customers

This exercise uses also several tables connected to have the deliberate result. It uses also the function Sum and the field calculated to determine the total of sales by items. It is especially the query that an administrator would be interested to see. He could know who are it's best customers. Here are the connected tables and them relations.

Connected tables: Customers, Invoice, Transition Fact-Inv and Inventory

Criterion:

Field: Last name Total: Quantity * [unit price]
Table: Customers
function Grouping Sum
Sorting:
Show: X X
Criteria:

*Execute the query by pressing on the Executebutton.

Result:

Last name

Total

Alpha Ltée

2 400,00 $

Béatrix Inc.

6 155,00 $

Charles and cie

8 975,00 $

Demix Inc.

4 950,00 $

Éricson Industriel

4 800,00 $

30-Use a query to enter the data of invoices.

It is also possible to use a query to enter data. The query should have, at least , one of the fields in common connected tables. Furthermore, the field in common should be the one that allows to enter several times the same information. For example, between fields No. Invoice and Number of invoice that connect tables Invoice and Transition Fact-Inv, you must use the second. So, for this exercise, it is necessary at least fields Number of invoice, Codes Customer, No. salesperson and Number of product. One could then choose the other fields. To demonstrate the efficiency of this query, only the following fields will be added: Last name (of the table Customers), address and quantity. Furthermore, a calculated field will be added to the end of every record to determine the total of the item. The formula will be the following one: Total: quantity * [unit price].

Field:

Invoice Number

Code Customer

Last name

Address

Employee no

Number of product

Quantity

Total: quantity * [Unit price]

Table:

Transition Fact-Inv

Customers

Customers

Customers

Invoice

Transition Fact-Inv

Transition Fact-Inv

 

Sorting:

 

 

 

 

 

 

 

 

Show:

X

X

X

X

X

X

X

 

Criteria:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

*Enter the following data the appropriate fields.

Bill no

Customer code

Employee no

Product ID

Quantity

None

1

2

1

10

The number that Access determined to the previous record

Even

Even

2

20

None

2

3

1

10

It is useless to enter a number to begin a new invoice. Tables Invoice and Transition Fact-Inv are connected with the integrity rules option activated. But especially, the "update in cascade" option was activated. It means that, when you don't put figures, Access goes to see if there is an equivalent value in the table Charges. Because No. Invoice is the primary key of the table, is that it is impossible to have an empty field, it forces Access has to create a new record in the table Charges and to give it a number. This number is then retransféré in the field Number of invoice.

The second record is to demonstrate you that it is possible to enter several items on the same invoice. It is enough to write the same number of invoice in the field No. Charges. However, thanks to the primary key consistsd of several fields of the table Fact-Inv, the user of the data base can not enter several times the same item on the same invoice.

Conclusion

I hope that all these queries gave you ideas and enough material to create your own queries. Leave me a message if you have any comments or questions.

 

You like what you read? Share it with your friends.




This site is hosted by 1&1.com