.:CS 416 - Database Management:.
| MS Access 5 | ||
|
Add lookup field for JOBCODE in the Employees table.
In the design view, select the jobcode field and start the
lookup wizard.
In the 1st
dialog window 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"…]
Add the “lookup” feature – COMBO BOX also to the Employees form
Open the form in the design view, delete the JobCode field and
label.
Activate the Field list /Add Existing Fields/, find the JobCode
field and move it to the form’s detail. Adjust properties of
this combo box.
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), ProductID (number – long integer, indexed-duplicates OK), Quantity (number – long integer), ShipDate (date/time), CarrierID (text, indexed – duplicates OK), Discount (number)
Set up relationships –
click Database Tools – Relationships.
Add all tables.
Click custID (CUSTOMERS field list) – drag and drop it on custID
(ODERS list) to create
1-many
relationship In the dialog window click Enforce Referential
Integrity,
Create another
1-many
relationship between empID (EMPLOYEES) – empID (ORDERS),
and finally, relate productID (PRODUCT TYPE) and productID
(ORDERS) via
many-many
as this field is neither a primary key nor will be indexed.
Delete the relationships ;)
[You can adjust properties, eventually, delete a relationship by
right-clicking it and choosing a corresponding option]
In Orders table, create lookup fields for EmployeeID, CustomerID, and ProductID. (The data will be looked up in the corresponding tables and option menu will show both – ID and name)
Check relationships and enforce referential integrity
Add 3 records into the Orders table [note that you can add only existing customer and employee IDs] Create a form for ORDERS with title and command button to open a US$/SKK Currency converter form /you have to create this one as well ;).
Create an EXPRESSION query to count 15% discount price for products in a certain price range /$3 – 8/ [in the products table we need to have at least 8 products with prices ranging from $1.57 to 40.65 and at least 4 product in price range $3-8]
•Click
Create – Query Design – 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, Caption = Discount price.
Save this query and name it discounts.
Create report for the discount query and add insert report
command button in the Products form.
Based on the EMPLOYEES table, create simple COUNT query that will display number of employees per department.
In the 1st field select JobCode click the Totals button –
this will add the total row that provides the most commonly
used functions - and
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 per dept. and
save query as emps_dept.
Create similar “COUNT” query to determine the total number of PRODUCTS in a particular category as well as the sum of the products’ quantities.
- In the first field select Product Code, Total = Group By; then Product Code, Total = Count; and finally Quantity = Sum). Save query as products_category.
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! Save 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! Save
|
||