1. Add lookup field for department number in the Employees table. In the design view, select the deptID field and from the data type menu choose lookup wizard. Once the dialog window appears, choose option I will type values I want, next, No of columns – 1 and in the column cells type IS01, IS02, MG01, MG02, MKT01, HR01, Next, Finish. [Note that the wizard changed lookup properties of this field: Display Control = Combo box, Row Source Type= Value List, Row Source = "IS01";"IS02";"MKT01"…]

2. Add the “lookup” feature also to the form – open the Employees form in the design view, delete the dept# field and label. Click View – Field list (click the Field List icon on the standard toolbar (between hyperlink and toolbox)), once the list of Employees table fields appears, find the DeptID and drag and drop it to the form’s detail. Adjust properties of this combo box.

3. Create another table for SalesOrders, fields in this table will be: OrderID (primary key, autonumber), CustomerID (number – long integer, indexed-duplicates OK), EmployeeID (number – long integer, indexed-duplicates OK), OrderDate (date/time, choose format), ProductID (number – long integer, indexed-duplicates OK), Quantity (number – long integer), ShipDate (date/time), CarrierID (text, indexed – duplicates OK), Discount (number)

4. Set relationships – r.c. any neutral area in the basic window, eventually, click Tools – Relationships. Click the custID (customers field list) – drag and drop it on custID (orders list) 1-many, once the dialog window appears, click Enforce Referential Integrity, cascade update/delete; create another 1-many relationship between emplID (customers) – emplID (orders), cascade update/delete, and finally, productID(product type) – productID (orders) many-many as this field is neither a primary key nor indexed. [You can adjust properties, eventually, delete a relationship by right-clicking it and choosing a corresponding option]

[Primary key – foreign key (left-right) one-to many, many –to-many (established automatically e.g. by lookup from a different table)
Enforce referential integrity = (dependency) can’t update/delete records without permission]

5. Add 5 records into the Orders table [note that you can add only existing customer and employee IDs]

* * *

6. Create EXPRESSION query which will count 15% discount price for products in a certain price range [in the products table we need to have at least 10 products with prices ranging from 1.57 to 40.65, at least 4 product in price range $3-8, 2 of them will have 2 decimal places)] Click Query – New – Design View – Select the Product table; in the 1st field select Product Name, sort: ascending, show. Right-click the 2nd field, choose the Build option; in the expression builder window select the Product Type table, double click the Price field, then click * and add 0.85. Then, in the Criteria cell type >=3 And <=8. then select the 2nd column, r.c.- properties, change Format = Currency, Decimal places =1, Caption = Discount price. Save this query and name it discounts.
SELECT [Product Type].ProductName, [Product Type]![Price]*0.85 AS Expr1
FROM [Product Type]
WHERE ((([Product Type]![Price]*0.85)>=1.5 And ([Product Type]![Price]*0.85)<=3))
ORDER BY [Product Type].ProductName;

7. Based on the employee table, create COUNT query that will display number of employees per department. In the 1st field select DepartmentID (job code?), click “sum” button – this will add the total row that provides the most commonly used functions – in the total row select “group by”; in the 2nd column, again, select the job code field, and in total row choose the count option. Change caption of the 2nd column to employees/dept. Save query as emps_dept.

8. Create similar “COUNT” query to determine the total number of products in a particular category as well as the sum of product quantities. (In the first field select Product Code, Total = Group By; Product Code, Total = Count; Quantity = Sum). Save query as product_cat.

9. Open the emps_dept count query and add the PARAMETER feature that will display number of employees from just one, desired department. In the first column click the criteria row, press shift+F2 to enlarge the cell, then type selection criteria in [] (e.g. [Enter desired Job Code IS01, IS02, MKT01, MG01, MG02, or HR01]) – no period! Copy this text, close the dialog window, click Query - Parameters, paste the criteria in the 1st column, in the data type column choose text, ok, save.
PARAMETERS [Enter desired Job Code IS01, IS02, MKT01, MG01, MG02, or HR01] Text ( 255 );
SELECT Employees.DepartmentID, Count(Employees.DepartmentID) AS CountOfDepartmentID
FROM Employees
GROUP BY Employees.DepartmentID
HAVING (((Employees.DepartmentID)=[Enter desired Job Code IS01, IS02, MKT01, MG01, MG02, or HR01]));

10. Add the PARAMETER feature also to the other query. In the first column click the criteria row, press shift+F2 to enlarge the cell, then type selection criteria [Enter product category C, D, E, F, or T] – no period! Copy this text, close the dialog window, click Query - Parameters, paste the criteria in the 1st column, in the data type column choose text, ok, save.