1. Create an update query to double the price of all products – new query, select Product Type table, OK. Right-click the neutral - gray) query area, select query type – update query (this will add the “update to” row). Select the Price field and in the update to field built event that will double the price in the Product Type table’s price field. (r.c. – build – select table, field, *2). Name the query price_up.
UPDATE [Product Type] SET [Product Type].Price = [Product Type]![Price]*2;

2. Create another update query that will change date hired fields in the Employees table to 01/01/2004 , name the query date_hired.

3. Create another update query to replace country in Customers table (Slovakia to Slovak Republic or SK to SVK) – new query, table customers, query type = update; 1st field – country, update to e.g. SVK, criteria SK. Name the query country_update.

4. Create a new – delete query that will remove all C category products (adjust your data entries in the table). Select the Product Type table, query type – delete. In the 1st column select *Product Type (table), Delete: From (this is the column where you choose a table, from which you want to delete some field(s)); in the 2nd column choose Product Category, Delete: Where, criteria = C.
DELETE [Product Type].*, [Product Type].ProductCode
FROM [Product Type]
WHERE ((([Product Type].ProductCode)="T"));

* * *
It is also possible to select, replace, search for desired elements in a TABLE, IN DATA ENTRY VIEW:

5. In customer table, telephone number column, replace any 74 with 75 (select the phone# column, click Edit – Replace; Find: 74, Replace with: 75, where, phone#, Match: Any part of Field.

6. Sort data in table by company name (A-Z) – select the company name column and click the AZ button. Then move the contact name column right behind the web page fields (select the column, drag and drop where desired), and country code (state column) behind the company name, and sort these two columns (company, country) Z-A (select both columns by holding down the Shift key)

7. In the product type table, use filter to display only products for $1.12 – select the Price column, click the Filter by Form icon (funnel and form); in the price field available in the Filter table select 1.12 and click the Filter button. To view all the data, deselect the Filter button.

8. Use a filter to select all products but those we have 50. Right-click in a quantity field of product, where there is 50 pieces of a particular product, choose Filter excluding selection.

9. Use filter by form to select products quantity = 45 and price is 10.40

10.Use the FilterFor (r.c in any product code field) function select only F products

11. Export the customers table to MS Excel. (Open table, File – Export, change Save As Type)

* * *

CREATE SIMPLE SELECT QUERIES (review)

12. From Product type table display only product type (sorted A-Z), price, and product name. Then run the query and move name column next to type. Than replace all prices 1.12 with 1.50 (this will replace the prices also in the table!). Name the query Query 1.

13. Create query to select only the F category products (do not show) and list the products’name (A-Z), quantity, and price. Name the query Query 2.

14. Query to select F category products (do not show) of quantity 50 or 100 (criteria: 50 or 100, do not show). We want to see a list of product’s name (A-Z), type, and price. Name the query Query 3.

•15. Query to select E category products where we have more than 5 pieces (quantity, do not show, criteria >5). Again, want to see a list of product’s name (A-Z), type, and price. Name the query Query 4.

16. From the Customers table select customers we met before, eventually, on March 31, 2004 (Last Meeting, Criteria: <=March 31, 2004)and sort them by name from a-z. Name the query Query 5.

17. From Product table select 25% of products we have the least. Query based on product type, quantity (do not show, sort asc, Top values: 25%), name of product, sort a-z by type. Name the query Query 6.

18. Create a query that will find 5% of most expensive products. The list of products will be sorted a-z by name. Name the query Query 7.

19. Create a calculated field query that displays product name a-z, price, quantity, and total value (replace expr1 with total value). Name the query Query 8.