1. Design a new action query: Emp_birthdays to create a new table Employees_birthdays (see picture).
• Use the expression builder to create the field Employee using the fields FirstName and LastName.
• Add the field Title.
• Use the expression builder to create the field Month_number.
• Use the expression builder to create the field Month.
• Use the expression builder to create the field Age.
• Sort the table in descending order to Month_number.
• Run the query and verify the results.
• Number of records added to Emp_birthdays:
• Age of Terry Samuels:
2. Design also a new action query: Emp_birthdays_del to delete all records in the table Employees_birthdays.
Run the query and verify the results.
3. Delete the existing table Employees_birthdays.
4. Run the action action query: Emp_birthdays again to recreate the table Employees_birthdays.
Verify the results.
5. Use the table Employees_birthdays to create an interactive query Emp_birthdays_mon to select only these employees, having their birthday in the month specified (at runtime) as a number.
• Sort this list in ascending order according to Age.
• Number of employees having their birthday in December:
7. Design a new action query: Emp_birthdays_num to create a new table Employees_bdays_num to display the number of birthdays per month.
• Use the expression builder to create the field Month, displaying the name of the month.
• The field Number = number of employees having their birthday in that month.
• Sort the table in this order: Jan, Feb, Mar,…
8. Design also a new action query: Emp_bdays_num_del to delete all records in the table Employees_bdays_num.
Run the query and verify the results.
Save the database and make a new backup copy!
If something goes wrong in the next exercise, you can use this backup…
PART 4 - The management of Gardencompany needs some very specific information from the IT department.
1. Design a new crosstab query: Shipper_City_num. (see picture below).
a. Query description:
• The table displays for every shipper the number of shipments to a specific city.
• Sort the table in ascending order to City.
Run the query and verify the results.
b. Number of records in the table:
c. Number of shipments of Flexible Delivery to Seattle:
2. Design a new crosstab query: Shipper_City_freight. (see picture).
• Query description:
• The table displays for every shipper the total of the Freight of all shipments to a specific city.
• The 2nd field of the table displays the highest Freight of all orders shipped to a city.
• The 3rd field of the table displays the lowest Freight of all orders shipped to a city.
• The 4th field of the table displays the number of orders shipped to a city.
• Sort the table in ascending order to City.
Run the query and verify the results.
• Number of records in the table:
• Number of shipments to Vancouver:
• Maximum Freight of shipments to Duvall:
• Minimum Freight of shipments to Kirkland:
• Total Freight for Flexible Delivery to San Francisco:
3. Design a new crosstab query: Employee_City_Order.
a. Query description:
• The table displays for every employee the total number of orders per city.
• The table also displays to the total number of orders per employee.
• Sort the table in ascending order to LastName and FirstName.
Run the query and verify the results.
b. Number of records in the table:
c. Number of Orders for Carpenter to Bellevue:
d. Employee with the maximum total number of orders:
e. Maximum total number of orders:
f. Employee with the maximum number of orders shipped to Seattle:
4. Design a new crosstab query: Employee_Cust_Order.
a. Query description:
• The table displays for every employee the total number of orders per customer.
• Use the expression builder to create one expression for name (= the LastName and FirstName ) of the customer, for example: Akers Kim.
• Do the same for the employee.
• The table also displays to the total number of orders per customer.
• Sort the table in descending order to Customer Name.
Run the query and verify the results.
b. Number of records in the table:
c. Total Number of Orders for Atkinson Teresa
d. Employee(s) responsible for the orders of Dresen Kate:
5. Design a new crosstab query: Cat_Cust.
a. Query description:
• The table displays for every customer the total quantity ordered per category.
• Use the expression builder to create one expression for name (= the LastName and FirstName ) of the customer, for example: Akers Kim.
• The table also displays to the total number of orders per customer.
• Sort the table in ascending order to Customer Name.
Run the query and verify the results.
b. Number of fields in the table:
c. Total Number of Orders for Clayton Jane
d. Total quantity of Bulbs ordered by Clayton Jane:
e. Who ordered the biggest quantity in one category ?
6. Design a new crosstab query: Empl_Cust_Sales.
a. Query description:
• The table displays for every employee the total sales figure per customer.
• Use the expression builder to create one expression for name (= the LastName and FirstName ) of the customer, for example: Akers Kim.
• Do the same for the employee.
• Hint:
• Instead of starting from scratch, simply copy the query Employee_Cust_Order and change its name.
• Open the copy of the query in design view and change it.
• Since the total sales figure for an order cannot be found in one of the tables of the database, you will have to make a separate query to calculate this.
• Don’t forget to include the discount !
• Use this query as a source for the new crosstab query
• The table also displays to the total number of orders per customer.
• Sort the table in ascending order to Customer Name.
Run the query and verify the results.
a. Total Sales for Alboucq Steve:
b. Employee(s) responsible for the highest sales figure ( for one customer only):
Save the database and make a new backup copy!
If something goes wrong in the next exercise, you can use this backup…
PART 5 - Gardencompany wants to change the Unit Price of some products.
1. Open the table Products.
• Unit Price of a Pitcher Plant:
• Unit Price of a Bat Box:
2. Design a new interactive action query: Price_increase to update the field UnitPrice of the table Products.
Query description:
• The query asks for the increase % at runtime ( before it starts updating the Unitprice ).
• Run the query and increase the UnitPrice of all products by 10 %.
• Number of records updated:
• Updated Unit Price of a Pitcher Plant:
• Updated Unit Price of a Bat Box:
Verify these results carefully and start again if they are incorrect !
Save the database and make a new backup copy!
If something goes wrong in the next exercise, you can use this backup…
3. Design a new interactive action query: Price_increase_undo to update the field UnitPrice of the table Products.
Query description:
• The query asks for the % to undo at runtime ( before it starts updating the Unitprice ).
• Run the query and undo the previous increase of the UnitPrice of all products by 10 %.
• Number of records updated:
• Twice updated Unit Price of a Pitcher Plant:
• Twice updated Unit Price of a Bat Box:
• Verify these results carefully ( you should now have exactly the same unit prices as originally in part 5.1. ) Start again using the backup copy if they are incorrect !
4. Open the table Products.
• Units in stock of a Pitcher Plant:
• Units in stock of a Bat Box:
5. Open the interactive action query Price_increase in design view.
• Change the query to increase the Unit Price of only those products where the number of UnitsInStock is between two ( inclusive ! ) numbers to be specified at runtime.
• Run the query and increase the Unit price by 5 % for only those products where the number of UnitsInStock is at least 10.
• Number of records updated:
• (Updated) Unit Price of a Pitcher Plant:
• (Updated) Unit Price of a Bat Box:
• Run the query again and increase the Unit Price by 15 % for only those products where the number of UnitsInStock is less than 10.
• Number of records updated:
• (Updated) Unit Price of a Pitcher Plant:
• (Updated) Unit Price of a Bat Box:
Save the database and make a new backup copy!
If something goes wrong in the next exercise, you can use this backup…
PART 6 - Gardencompany is expanding again and hires a number of new gardeners.
1. Delete the existing table Employees_new.
2. Design a new action query: NE_new to create a new version of the table Employees_new.
Query description:
• Add the fields: FirstName, LastName, Title, Address, City, State, Postal Code, HomePhone, Birthdate and Datehired. Do not add the other fields.
• Add only the records of the employees living in Edmonds or Seattle.
• Run the query and verify the results.
• Number of records added to Employees_new:
3. Design a new action query: NE_title to change Title into gardener.
Run the query and verify the changes.
4. Run the action query: NE_state to change State into WA ( for all new employees). Verify the changes.
5. Design a new action query: NE_hiredtoday to change DateHired into today’s date.
Run the query and verify the changes.
6. Design a new action query: NE_city to change the City into
• Sammamish for all new gardeners with a FirstName beginning with A-L.
Run the query and verify the changes.
• Fall City for all other new gardeners.
Run the query and verify the changes.
Save this last version of the query as NE_city.
7. Change manually LastName and FirstName in the table Employees_new.
Use the names of the pupils in this ICT class.
Delete all unused records or create new entries if necessary.
8. Design a new action query: NE_birthdate. This query makes all new gardeners two years younger.
Run the query and verify the changes.
9. Run the action query: NE_append to append all new employees to the Employees table.
Verify the changes.
Number of records in Employees:
10. Run the action query: NE_delete to delete all records in the Employees_new table.
Run the query and verify the changes.
11. Delete the table Employees_bdays_num.
12. Run the action query: Emp_birthdays_num to recreate the table Employees_bdays_num.
Verify the result.
• Number of records in Employees_bdays_num:
• Number of birthdays in the month of May:
13. Use the interactive query Emp_hired to answer these questions.
• Number of employees hired before 22/02/2009:
• Number of employees hired after 2005:
Save the database and make a new backup copy!
If something goes wrong in the next exercise, you can use this backup…
PART 7 - Gardencompany wants to reward some of its customers with a gift voucher. The management wants to compare two ideas.
1. Design an action query VOUCHER1 to create a new table Cust_voucher1.
• This table contains:
o A field with the customer name ( LastName + FirstName).
o The Phone Number of the customer
o The number of orders
o The gift amount
• Only customers who ordered at least twice from Gardencompany receive a gift.
• Customers who ordered twice get 20 € per order. Those who ordered more often get 50 € per order.
• Run the query and verify the results.
• Number of records added to Cust_voucher1:
• Gift amount for Teresa Atkinson:
• Gift amount for Pat Coleman:
2. Calculate the total amount of the gift vouchers.
• Total amount
Save the database and make a backup copy!
If something goes wrong in the next exercise, you can use this backup…
3. Design another action query VOUCHER2 to create a new table Cust_voucher2.
• This table contains:
o A field with the customer name ( LastName + FirstName).
o The Phone Number of the customer
o The number of orders
o The gift amount
• Only customers who ordered at least twice from Gardencompany receive a gift.
