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 2007/2010/2013
Excel 2010
PowerPoint
Access

Others

Demonstration files
Texte en français

Contact

By e-mail
Join our Newsletter

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, you must use the employees table from the demoacc1.mdb database. If you have Access 2000 or later, use the demoa2k1.mdb database. These files are on the demonstration files web page that contains all the files used during my demonstrations and the exercises of this site.

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 2000 or later respectively. They are also on the demonstration files web page.

The exercises

The big part of creating a query is being capable of converting the question into the options of a query. To do this, you must practice. The next part is a series of exercises to demonstrate some of the possibilities of a query. These exercises progress in complexity. Each demonstrates a new option with queries. They are based 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 the tables, the fields and the other options to create a query that answers the question. Then execute the query to see if your answer is the same that that described below.

1-Show the first and last name of everyone whose first name is " roger ".
* Create a simple query.

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

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

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

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

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

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

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

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

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

11-Show the commission of each sales person if he or she had an increase of 20 %.
* Use a calculated field with a constant (20 %).

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

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

14-Show next to the first name and the name from the employees of the company with the text "champion" for those that have a 45 000 $ commission or more. Place the text "Sorry" for 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 sales person if he or she was given a 5 000 $ bonus to those with a commission of 45 000 $ or more.
* Use of Truefalse(condition; if true; if false) function whose result is a number.

Aggregate queries

16-Show the total in payroll of the company.
* Use of the Sum function.

17-Show the total in payroll of the company divided by occupation.
* Use of the Sum function with several criteria.

18-Show the total in payroll of the company divided by office location and by occupation.
* Use of the Sum function with several criteria.

19-Show the payroll 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" sales person having earned a 45 000 $ commission or more.
* Use of the Count and Where operators.

Crosstab queries

22-Use a crosstab query to determine the payroll of the company by occupation in column and by office location.
* Crosstab query with the Sum function.

23-Use a crosstab query 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.

Action queries

24-Change the office location of every employees located in Quebec to Sillery.
* Update query.

25-Delete all the employees with the family name Gendron.
* Delete query.

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

Queries with multiple tables

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 demonstration files.

27-Show the invoice number, 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 the count of every product sold, 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 incoice data.
* 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 ".

We'll start you slowly with a simple query using a text field. It's the same example developed in the previous page.

1. Choose one or several tables or queries.

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

By using the tabs, you could select any table or query that's in the database. You can use or or more table or query to make a new query.

2. Choose the type of query.

Make sure to have chosen the selection query .

3. Choose one or several required 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 criterion.

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

Here is how the criteria grid should look like.

Criteria

You don't need to put quotation marks. Access will put them for you to indicate that the field First Name is a "text" type. Furthermore, you're not obliged to put the operator "=". It's selected by default if no other operator is chosen. Besides, you're not obliged to put the text in uppercase or lowercase. Access does not make the difference in the query. However, you must write the criterion correctly. For example, you will find noone if you wrote " rooooger "!

For the fields of type Date/Time, Access will place automatically in front of and after date the character "#". So, you're not obliged to put it. This is done to differentiate the Date/Time fields. " " is used for text fields and no special character is used for numbers or monetary fields.

7. Execute the query.

Execute the query by pressing on the 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 the last name of every person 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 single character in the criteria. For example, a query on b?ll could give the following results: ball, bill, bxll or b3ll. THe "?" can also be used with the Date/Time fields. For example, 98-??-01 could show all the records of the first day of every month.

The character "*" serves for replacing a series of characters. You may know 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 add "Like" to indicate that all the records must begin with the letter " l ".

Criteria

Execute the query by pressing on the button.

Result

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

This query demonstrates the possibilities of using the logical operators such as <,>, < =, and > = for text, date/time, numeric or monetary type. For this exercise, the research criterion is used on a monetary field. Other types of fields can be also used with these logical operators.

Criteria

Result

Pay attention when reading the question. Is it asking superior to X or equals and superior in X? it's the small trap that some don't pay attention to.

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

This query could use the operators > =, and, < =. But the exercise will demonstrate the operator "Between X and Y" and the type of Date/Time field. Notice "#" in front of and after date. If you don't put them, Access will write them for you. It's to identify that the field Hiring date is a Date/Time field.

Criteria

Result

Dependent on the options selected in Windows and Access, it's possible that the date format may be different. You should probably put the month in front followed by the day and the year. Make some attempts by changing the order of the year, of the month and of day.

5-Show the first and the last name of every employee with permanent status in alphabetical order by family name and first name.

This query looks at the possibilities with the logcal or Yes/No field and also to sort out the result of the query. Furthermore, the field Permanent is used to filter the records. But it will not be seen in the final result. Make sure that the Show box is deactivated for this field.

Criterion

Please remember! The sorting priority goes to the field most to the left and so on; even if it's not seen!

Result

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

It would be so easy to simply put the criterion No under the field Permanent status. But this example is especially to demonstrate the Not logical operator. The result will show 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 only No.

Criterion

Result

Another example would be to show all the employees but not those in Quebec.

Note:
There is a special characteristic for the Yes/No field that you should know. Although the final result is shown as Yes/No, or 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 the last name of the managers of the company.

If you look at the contents of the "Comments" field, you will notice that every person in the company has a comment 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 " Null " criterion.

Criterion

Result

8-Show the commission of the sales person named 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 criterion row. If the criteria are on different rows, it's the equivalent or using the OR logical operator or combining two searches into one result.

Criterion

Result

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

Be careful! There is a trap in the question.

it's a question of logic. Ask yourself this question: "Can you be in Montreal AND in Quebec at the same time?" Of course not. But you can be at one OR the other. It's a question just of interpreting correctly the question that is presented to you. It's not always as simple as you may think.

Criterion

You could also answer this question in this way:

Criterion

Result

This question demonstrates that it's possible to combine criteria under and use the AND and OR logical operators. The criteria on the same row are added (AND) one to another. The criteria that are on another row offer another possibility to look at more information (OR).

10-Show the first and the last name and the income of every employee with an income of 45 000 $ or more.

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

You can write the calculated field 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 a colon":". For the exercise, you must create the calculated field 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".
Add a colon : to separate the name of the field from the formula that you need.
Write the formula salary + commission

Ex: name:formula , gst:total*0,07, tvq:(total)*0,875 etc.

Starting with Access 2010, you can have calculted fields directly into your table.

Be careful!
If the name of the field consists of several words, you should put it in between square brackets [ ]. Ex: [Date of birth], [Goods and services tax].

Sometimes, after you executed a query with a calculated field, that Access asks for the value of a filed. A window appears with the message and also indicates the name of a field. The reason for that this window is simple: you made an spelling error in one of the names of the fields. You must return and write the correct fild name inside the calculated field box. It must match the fild name that's 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's written in the table.

Criterion

Result

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

This query uses once again a calculated field. It uses a multiplication instead of an addition.

Criterion

Result

If you want to, correct this query to add the first and last name of each sales person in front of their commission.

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

It would be too easy to find the answer by using the "Between X and Y" criterion. This query will show you what's possible by using integrated Access functions and the expression generator.

To date, all the queries we've seen were simple. Access has many integrated functions ready to create more elaborated searches. For the exercise, we'll use the year() function year() that's in the Date/Time category. This function extracts the year in the form of number from a Date/Time field.

A Date/Time field contains many of information. There is the year, the month, the day, the hour, the minutes and the seconds. Access offers you many functions to extract that the part of time that you need.

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

The same concept applies to the other functions. Ex:

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

Add the fields First name and Last name to the list of the fields of the query.
Place the pointer into the third box of the list of fields: to the right of the Last name field.
From the list of commands, press the button.
OR
Press the right mouse button.

From the context menu, select the Generate option.

The Expression generator will appear.

it's from this window that you can access all of Access's resources. You can you to look for all the objects, to all the fields, the queries, the forms, the reports of your database. Furthermore, you have also access to the functions integrated by Access. These give you a lot of flexibility to create your own queries. If you're interested, you can even create your own functions.

it's in the box on top of the window that the formula for the calculated field will be written. You can find all of Access' functions and combine them to create the result you want. Ex: Abs(CMonnaie([text field])). Just the box where the formula will be written, there are buttons for mathematical functions and some criteriions. In three columns below that, you can access all the Access's objects. For the exercise, you must find the integrated Year() function.

To see the contents of the folders with a "+" on top of them, you must "doubleclick" on the folder.

Double-click on the Functions folder.
Click the integrated Functions folder .

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

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

The content of the third column will only show the functions of the chosen category. In this case, it's the Date/Time category of functions.

In the third column, find the Year function.

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

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

You must replace the "Number" by a DXate/Time field for the query, that is the field Hiring date.

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.
From the first column, click the Employees table.

The contents of the second and the third column will show the fields and options for this table.

In the second column, "double click" on the Hiring date field.
OR
Click the field Hiring date field and press the Paste button.

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

The formula needed for this exercice is now created. The result should be year([Employees)]![Hiring date])

Press OK.

Access then returns you to the queries' Design view.

Press the Enter key to complete the entry of the formula in the fields box.

Access will add " Expr1: " in front of the formula. Access considers this function as a calculated field like in the previous exercises. A calculated field always consists of a name, followed by colon (:) and the calculation. You can change the text " Expr1 " by something more appropriate such as " Year of hiring ".

Criterion

Result

Somebody found another way of answering this question.

Criterion

Although this way works, it does not demonstrate the possibilities of functions or the expression generator. This exercise demonstrated that it's 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 much easier with the year() function to make 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

Result

There are also the other functions in the Date/Time category that 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's 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 this category. Access offers more than 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 numeric format. Fractions represent hours, minutes and seconds. For example, the number 37222 represents November 27, 2001. So 37222,5 is at noon on November 27, 2001.

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

This exercise is to demonstrate you the IIF function, the equivalent of the IF function in Excel, where the result will show a specific text (Champion or Sorry). It works on the same principle as the IF function in Excel. IT needs it three "parameters" or three "arguments", to work correctly: the condition, what to do when TRUE and what to do when FALSE. These arguments or 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

Result

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

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

it's in this kind of situation where the IIF function works best. The fonction only applies if the required conditions are filled. In the previous exercise, the IIF function was used to showtext. Here, the function is used to calculate a number.

Criterion

Result

Can you prepare a query that shows the difference in income with and without this premium? You should be able if you understood well the previous exercises.

16-Show the total in payroll of the company.

All the previous questions showed the information according to a criteria. But what can you do when you need to find the sum, the average or the number of records that answer certain criteria? It's for these circumstances that there are the operations.

There is a very powerful option that was not covered until now: the grouping operations . It's possible group the records with the same values together so that you can add them, count them, find the average, the smallest, the highest and several other mathematical operations. It's also an opportunity to use again a calculated fields to determine the company's payroll. A calculated field will be used once again to determine the income (salary + commission) for every employee.

From the Query tab, press the button.

A new row willl appear below the row for field names. It's the Operations row. Access will let you carry out mathematical functions on the records of a query. If you look at the possible functions, you will find Sum, Average, the smallest ( Min ), the highest ( Max ) etc.

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

From the row for operations, select the Sum function for Income.

Note:
Several persons mix the functions Sum and Count. The Sum function is used for the add of numbers from a Numbers fields or Monetary field. The Count function is used to count the number of records that answer the selected criteria.

Criterion

Result

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

Criterion

In that box, it's not really more practical than the function adds. It's however more practical when it's used in a calculated field 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 total in payroll of the company divided by occupation.

It's another query that needs the Sum operation. One more field is needed however to get the necessary detail. In that box, you must add the Occupation field to be able to distribute the incomes by occupation.

From the Query tab, press the button.

Criterion

Result

The payroll 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 total in payroll of the company divided by office location and by occupation.

it's also about a query that needs an function (sum((dream)). It's 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

Result

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 payroll 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

Result

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

Result

20a-The trap with the Count function

The function Counts calculates the number of records that answers the criteria that you chose. There is however a situation where it could underestimate the exact number. Let's retake the last example with a small modification. Instead of using the First name field, use the Comments field.

Result

There are no more managers! Why is there a difference? Access does not add record where the contents of the field is empty. As you saw it in the exercise 7, the contents of the Comments field was empty for the managers of the company. Therefore, it's strongly suggested to always use Count operation with a field that you are certain has some content. 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 sales person.

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

Criterion

Result

22-Use a query of analysis crossed to determine the payroll 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's 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's the field that will detail the values of columns. It's 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's 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's 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 payroll, 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] + [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.

Execute the query by pressing on the button.

Result:

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 payroll from the employees who have or not the permanent status inside the company. Or, you can use a calculated field with the function Year ([hiring]) to determine the payroll by the number of years of experience(experiment) in the company. It's 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's 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:

Execute the query by pressing on the button.

Result:

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.

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's 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're 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's 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.

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's 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 calculated field 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 calculated field Year by hiring, enter the following text: [What is the year of hiring? ( 4 numbers) Ex: on 1998].

Here are fields and options for the criteria.

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.

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's 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 invoice number, 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's 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's 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's now necessary to add fields to the query.

From the table Invoices, select the field invoice number.
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's 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..

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's 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.

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

Execute the query by pressing on the button.

Result:

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's possible "to connect" them by using tables Invoice and Transition Fact-Inv. In this way, it's 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's 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.

Criterion:

Execute the query by pressing on the button.

Result:

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 calculated field to determine the total of sales by items. It's 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.

Criterion:

Execute the query by pressing on the button.

Result:

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

it's 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 invoice number that connect tables Invoice and Transition Fact-Inv, you must use the second. So, for this exercise, it's necessary at least fields invoice number, 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].

Enter the following data the appropriate fields.

it's 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 numbers, 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's 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 invoice number.

The second record is to demonstrate you that it's possible to enter several items on the same invoice. It's enough to write the same invoice number 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.