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
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, it is necessary to 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 creation mode.
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 Last name
Sorting:
Show: X X
Criterion: Like l*
Or:

Execute the query by pressing on the button.

Result

First name Last 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 Last name Salary
Sorting:
Show: X X X
Criterion: > 45000
Or:

Result

First name Last 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 Last name Hiring date
Sorting:
Show: X X X
Criterion: Between #93-01-01# and #93-12-31#
Or:

Result

First name Last 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 Last name First name Last 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 one looks at the contents of the field "comment", one notices that all the persons have a comment, except the managers. Then, it is necessary to indicate to Access to look for the records of that the field comment is empty. To do it, it is necessary to use the criterion " Null ".

Criterion

Field: First name Last name Comments
Sorting:
Show: X X X
Criterion: Null is
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 the other one. 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. It is possible to show the result of a formula that uses the information resulting from the other fields of the recording. 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, it is necessary to 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 recording. 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 parameter. 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

Being 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 analyse 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. It is possible to 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, it is necessary to look for the function integrated Year().

To see the contents of the files that have one "+" on the top, it is necessary to make(do) a "double-click" 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-click" 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' creation mode.

* 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([being used(employed)]! [Hiring])
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
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])
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 "Saddened(been upset") in rib(coast) of those that don't answer the previous 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, it is necessary to show the "Sorry" text.

Criterion

Field: First name Last name Critère:vraifaux ([commission] > = 45000; "Fieldion"; "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:vraifaux ([commission] > = 45000; [committee([commission]) (5000); [committee([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] + [committee([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(dream) ([commission] + [salary]) / account ([office(desk)]). In passing, this last formula is the equivalent of average ([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, it is necessary to 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, it is necessary to 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 it is necessary to 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 CompteDePrénom
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 Comment.

Field: Office Occupation Comment
function: Grouping Grouping Count
Sorting: Ascending Ascending
Show: X X X
Criterion:
Or:

Result

Office Occupation CompteDeCommentaire
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 recording of that the contents of the field are empty. As you saw it in the exercise 7, the contents of the field comment 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 salesmen "champions" having earned a 45 000 $ commission 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 Creation mode.

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 creation mode.
*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 creation mode.
*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 Creation mode.
*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 ([embauche]) (hiring)
Table: Employees Employees
Sorting:
Show: X X X
Criteria: [ What is the year of hiring? ( 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 parameter.
* Press on the OK button.

Access will show the following result.

Suzanne Rémi On 1993
Éric Gendron On 1993
Roger Dubuc On 1993
Elects Lavigueur On 1993
Paul Gendron On 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 Creation mode.
*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 it is necessary to 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, it is necessary to 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 recording to determine the total of the item. The formula will be the following one: Total: quantity * [unit price].

Field: Nunméro of Invoice 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 recording in the table Charges and to give it a number. This number is then retransféré in the field Number of invoice.

The second recording 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