Exercises in Access

(1)

EXERCISES

IN

(2)

CREATING DATABASE AND TABLES

1. Create a database for “ANGEL’S BOUTIQUE” and name it as RTW.ACCDB. 2. Create the following tables:

A. SALESMAN TABLE

FIELD NAME FIELD TYPE DESCRIPTION

Salesman Number Text Salesman Control No. Primary Key

Salesman Name Text Full name with Middle Initial Civil Status Number (1) Single; (2) Married;

(3) Widowed; (4)Separated Profile Memo Brief History of Salesman Salesman ID OLE ID picture of Salesman Date of Hiring Date/Time Official date of Hiring

B. PRODUCT TABLE

Product Number Text Product Control No. Primary Key

Product Description Text Description of Product. Stock on Hand Number Quantity of Product

On Stock.

Supplier Code Text Supplier of the Product. Price Currency Price of Product

C. SUPPLIER TABLE

Supplier Code Text Supplier Control No. Supplier Name Text Name of Supplier

D. SALES TABLE

Salesman Number Text Salesman Code Customer Name Text Full name of Customer Product Number Text Product Control Number Quantity Number Unit sold per product.

Quota Yes/No Test whether the salesman reached its monthly quota. Turnover Date Date/Time Remittance date of salesman.

(3)

Exercise #2

CHANGING FIELD PROPERTIES

1. Open the RTW.ACCDB and open the SALES TABLE and input the following records by applying the FIELD PROPERTIES.

a. SALESMAN NUMBER, PRODUCT NUMBER – input mask. b. TURNOVER DATE – input mask in the format of mm/dd/yy.

c. HIT SALES QUOTA - change caption of QUOTA and set Default value to “Yes” d. QUANTITY – enter the validation rule “Between 1 and 50”. >=1 and <=50 e. QUANTITY – enter the validation text “Quantity should be between 1 and 50”. 2. Input data for SALES TABLE.

SALESMA N NUMBER CUSTOMER NAME PRODUCT NUMBER QUANTIT Y HIT SALES QUOTA TURNOVE R DATE SM-1001 Andrada, Rita PN-1001 1 Yes 10/28/97 SM-1002 Dela Cruz, Alec PN-1004 2 No 10/29/97 SM-1003 Pilar, Clarisse PN-1003 10 Yes 10/25/97 SM-1004 Montalban, Edsa PN-1004 5 Yes 10/26/97 SM-1005 Antique, Joy PN-1002 3 No 10/27/97 SM-1006 Mabuhay, John PN-1001 2 Yes 10/30/97 2. Open the SUPPLIER TABLE and input the following records by applying the FIELD

PROPERTIES.

SUPPLIER CODE SUPPLIER NAME SC-1001 Bench

SC-1002 Penshoppe SC-1003 Adidas

(4)

ENTERING DATAINTHE TABLE

1. Open the RTW.ACCDB and Open the SALESMAN TABLE and input the following records: SALESMAN NUMBER – set the input mask

Profile - memo field – the student must give their own description of the salesman. Salesman ID – is an OLE field, the student must insert an object from MS Clipart Gallery.

SALESMA N NUMBER

SALESMAN

NAME STATUSCIVIL DATE OFHIRING SM-1001 Gere, Richard 1 02/25/90 SM-1002 Roberts, Julia 3 11/29/91 SM-1003 Reeves, Keanu 2 08/25/89 SM-1004 Bullock, Sandra 4 05/17/87 SM-1005 Douglas, Michael 1 06/02/95 SM-1006 Stone, Sharon 1 05/21/94 SM-1007 Slater, Christian 2 03/01/97 SM-1008 Douglas, Michael 4 03/26/93 SM-1009 Murphy, Eddie 4 04/02/98 SM-1010 Willis, Bruce 3 01/29/94 2. Open the PRODUCT TABLE and input the following records:

PRODUCT NUMBER, SUPPLIER CODE – input mask PRODUC

T NO.

PRODUCT

DESCRIPTION ON HANDSTOCK SUPPLIERCODE PRICE PN-1001 Polo 250 SC-1001 250.00 PN-1002 Skirt 360 SC-1002 300.00 PN-1003 Jeans 520 SC-1003 750.00 PN-1004 Baby Dress 600 SC-1004 550.00 PN-1005 Polo Shirt 250 SC-1005 475.00

(5)

Exercise #4

SORTINGAND FILTERINGOF RECORDS

PROCEDURES:

1. Open the Clothes Database.

2. Open SALESMAN TABLE and sort the SALESMAN Name field in Ascending Order and view its resulting output in the DATASHEET VIEW.

3. Open SALES TABLE and Filter the records by entering the following criteria: PRODUCT NUMBER = “PN-1002” AND “PN-1001”

4. Open PRODUCT Table and Sort the Product Description. 5. Open Supplier Table and Sort the Supplier Name.

(6)

CREATINGA RELATIONSHIP

PROCEDURES:

1. Open the Clothes Database.

2. Create a relationship on the four Tables and these are the following: Product, Sales,

Salesman, and Supplier based on their common field.

3. Close the Relationship window by choosing the Close button.

4. In the next dialog box, click the Yes button to save the layout changes to the Relationship window.

(7)

Exercise #6

CREATINGA SELECT QUERY

PROCEDURES:

1. Open the RTW.ACCDB Database.

2. Create a new Query using the following tables: Salesman, and Sales

3. Drag the Salesman Name field from the Salesman Table into the first column of the Field Grid.

4. Drag the Customer Name, Product Number and the Quantity fields from the Sales Table into the succeeding columns of the Field Grid.

5. Activate the cell just beneath the Quantity field in the Query Design Grid and select the Ascending order option for the sort order.

6. View the resulting Dynaset in the Datasheet View.

(8)

WRITINGAN EXPRESSION

PROCEDURES:

2. Create a new Query using the following tables: Product, Sales, and Salesman

3. Drag the following field(s) from their respective table to the Field Column of the Query Design Grid:

a. Salesman Name - Salesman Table b. Customer Name - Sales Table c. Product Description - Product Table d. Price - Product Table e. Quantity - Sales Table

4. Activate the cell under the Quantity field in the Field Grid and arrange it in Ascending Order. 5. Create a Calculated column by type the following Expressions after the Quantity field in the

Query Design Grid: Amount Due: ([Price]*[Quantity]) 6. View the resulting Dynaset in the Datasheet View.

(9)

Exercise #8

ACTION QUERIES

PROCEDURES:

2. Add the following fields from the Sales Table: Amount Due, Commission, Net Sales, and

Profit.

3. Create a new query that this new query will update the Amount Due field of Sales table using the Update Query commands. Use this Expression:

([Price]*[Quantity]) Query Name: Update Amount Due

4. Create another query that this new query will update the Commission field of Sales table using the Update Query commands. Use this Expression:

([Amount Due]*0.05) Query Name: Update Commission

5. Create another query that this new query will update the Net Sales field of Sales table using the Update Query commands. Use this Expression:

([Amount Due]+[Commission]) Query Name: Update Net Sales

6. Create another query that this new query will update the Profit field of Sales table using the Update Query commands. Use this Expression:

([Net Sales]*0.10) Query Name: Update Profit

(10)

CREATING FORMS

PROCEDURES:

2. Create a form using the Form for the following tables: a. Salesman Table Form Name: Salesman Form b. Sales Table Form Name: Sales Form c. Product Table Form Name: Product Form d. Supplier Table Form Name: Supplier Entry

3. In the Form View window, click the Navigation button to move the next three records. 4. View the last record.

5. View the first record.

6. Add 5 more records for each table. 7. Close the 2 forms.

(11)

Exercise #10

CREATING, EDITINGAND CUSTOMIZINGA FORM

PROCEDURES:

2. In the Database Window, click the form tab. 3. Create a form based on the following:

Table Fields

a. Salesman Salesman Number b. Salesman Salesman Name c. Sales Customer Name d. Product Product Number e. Product Product Description f. Sales Quantity

g. Product Price h. Sales Amount Due

4. Modify and reposition the controls so that it will look the same as shown below:

5. Click the form view button to display your form.

6. In the form view window, click the navigation buttons to move between records.

7. Print and close the form. When you closed the form, Access displays a dialog box asking you if you want to save the changes in the design of the form, just click Yes button to save it.

8. Form Name: Sales Entry.

9. In the design window, change the Product Number text box into a Combo Box.

10. Add a heading label in the Form Header section and then surrounds the label with a rectangle.

HEADING: Angel’s Boutique

11. Add a Calculated control on the Amount Due field. Expression: =([Quantity]*[Price]) 12. Click the Form view button to display the result of your form.

(12)

ADDING COMMAND BUTTONSTOA FORM

PROCEDURES:

2. Select the Sales Entry Form and then click the Design button. 3. Add the following command buttons to the current form.

A. Go to First Record B. Go to Previous Record C. Go to Next Record D. Go to Last Record E. Find Record F. Add New Record G. Save Record H. Delete Record I. Print Record J. Undo Record

(13)

Exercise #12

CREATING OBJECTSAND ADDINGOBJECTSTOA FORM

PROCEDURES:

2. Select the Sales Entry Form and then click the Design button.

3. Create a Logo using the MS Word or MS PowerPoint or Paint Program size 1” height and 1.5” width.

4. After creating the logo, Copy the Logo and Paste to the Sales Entry Form and be sure that the form must be in Design View.

5. Add a Title and Type Angel’s Boutique. Place beside the Logo.

6. Click the Form View button to see the changes that had been created.

A B

Angel’s Boutique

(14)

CREATING SUBFORM FORM

PROCEDURES:

2. In the Create tab, click the More Forms and Select Form Wizard.

3. Click the down-arrow button located to the right of the Tables/Queries box, and then select the Sales table from the list. Select the following fields Customer Name, Product Number, Quantity, Quota and Turnover Date. Click the Next button.

4. Select the Tabular Layout and Click Next button.

5. Select the Style that you like and click Next button. Type Sales Form in the Title of the Form.

6. Click the Form Maximize button. Drag the Form’s frame up to 5 inches mark to stretch it. 7. Rearrange the remaining text boxes and labels.

8. In the Database Window, select the Salesman Entry Form and then click the Design button.

9. Rearrange and resize the controls.

10. After modifying the form, click the Window menu, and then click the Clothes Database to bring the Database Window in front of the Sales Form.

11. Drag the Sales Form from the Database Window into the Salesman Entry Form. Click the Form View button to display the records in the Form View window.

(15)

Exercise #14

CREATINGA SIMPLE REPORT

PROCEDURES:

1. Open the RTW.ACCDB Database. 2. Click the Create tab.

3. Click the Report Wizard button.

4. In the New Report window, select the Report Wizard and then select the Salesman table from the list of tables/queries box. Using Tabular Layout and Landscape Orientation.

5. Report Name: Salesman Report

6. Create the following report using the following table, Using Tabular Layout and

Landscape Orientation:

A.

Sales and assign the Report Name: Sales Report.

B.

Product and assign the Report Name: Product Report.

(16)

CREATINGA SIMPLE REPORT

PROCEDURES:

2. Select the Product table and click the Create tab. 3. In the Report Group, click Labels button.

4. In the Tables/Queries box click the drop-down list and select the Product Table

5. Select the Product Number, at the second line of the label select the Product

Description field.

Prototype:

Product Number Product Description

6. Print/Preview and close the report. Report Name: Product Label Report 7. Select the Product table and click the Create tab.

8. In the Report Group, click Labels button.

9. In the Tables/Queries box click the drop-down list and select the Supplier Table

10. Select the Supplier Code, at the second line of the label select the Supplier Name field. Prototype:

Supplier Code Supplier Name

(17)

Exercise #16

CREATING MAIN SWITCHBOARD

PROCEDURES:

2. Create your own switchboard. Assign your own form, reports and main switchboard 3. Add graphics that correspond to your database.

(18)