Excel Question

MARKS   MARKS

Final EXAM

MS Office (Access 2013)

 

Academic Year:		2021 – 2022	Semester:		Level 4 s3
Course Title:		ADVANCED IT SKILLS	Course Code:		FPIT0304
Student’s Name:	Hajar Hamdan ALHarrasi			Student’s ID:		Le
Computer No:				Date:

 

Instructions for Student:

1. ID card should be placed on the table
2. This exam paper consists of 4 Pages including the front page.
3. Time allowed is two hours (2 hours).
4. Blue or black pen only is allowed for answering the questions. Pencil can be used for diagrams only.
5. Dictionary or any electronic gadget is not allowed.
6. Switch off your mobile phone and put it on top of teacher’s table.
7. Exchanging or sharing of resources is prohibited.

Mark Distribution:

Section	Total Marks Allocated	Total Marks Scored	Remarks
Part I	27
Part II	3
Part III	5
Part IIV	5
TOTAL	40

 

 

Marked by:		Checked by:
Signature:		Signature:
Date:		Date:

 

Part I

Table Design and Relationship

Marks 27

1) Create a folder on your desktop and name it by your full name.

2) Create a database inside your folder and save it by your full name.

3) Create a table “VCMS2022” with the following fields and set ID as Primary Key

Field Name	Data Type	Properties	Description
ID	Number	Field Size: Long Integer	Duplicates are not allowed in  ID
TITLE	Lookup Wizard (Short Text) -HOD NAVIGATION OF FISHING VESSELS -HOD  MARINE ENGINES MECHANICS -HOD AQUACULTURE -HOD SEA FOOD AND QUALITY CONTROL	Field Size: 40	Titles of  VCMS
OFFICE	Short Text	Field Size: 15 Default Value is “O”	Details about Office
DEPARTMENT	Lookup Wizard (Short Text) -NAVIGATION OF FISHING VESSELS -MARINE ENGINES MECHANICS -AQUACULTURE – SEA FOOD AND QUALITY CONTROL	Field Size: 40	Names of Departments
LOGO	Attachment	Caption: Logo	Attach Department Logo

4) Enter the following records in the table “VCMS2022”.

ID	TITLE	OFFICE	DEPARTMENT	LOGO
100	HOD NAVIGATION OF FISHING VESSELS	ON	NAVIGATION OF FISHING VESSELS
200	-HOD  MARINE ENGINES MECHANICS	OM	-MARINE ENGINES MECHANICS
300	HOD AQUACULTURE	OA	AQUACULTURE
400	HOD SEA FOOD AND QUALITY CONTROL	OS	SEA FOOD AND QUALITY CONTROL

5) Create a table “VCMS INSTRUCTORS” with the following fields and set INS_ID as Primary Key.

Field Name	Data Type	Properties	Description
INS_ID	Number	Field Size: Long Integer	Instructors ID.  Duplicates are not allowed
INS_NAME	Short Text	Field Size: 40	Name of  Instructors
NATIONALITY	Short Text	Field Size: 25	Nationality of  Instructors
AGE	Number	Field Size: Long Integer   Validation Rule: Age>=20 Validation Text  “Not Possible to Register”	Validation Rule: “Age should be equal to or greater than 30”   Validation Text  “Employee below 30 cannot be Registered”
DATE_OF_JOINING	Date/Time	Format: General Date	Joining Date of  Instructors
ID	Lookup Wizard VCMS2022 Table (ID)	Field Size: Long Integer	Look for the values from the another table     VCMS2022

 

6) Enter the following records in the “VCMS INSTRUCTORS” table

INS_ID	INS_NAME	NATIONALITY	AGE	DATE_OF_JOINING	ID
192001	Akram	Omani	32	01/01/2001	100
202002	Sahar	UAE	33	01/01/2020	200
182003	Ali	Qatari	34	01/01/2003	100
172004	Omar	Omani	36	01/01/2014	400

 

7) Edit the relationship between the two (2) Tables. Apply Enforce Referential Integrity.

8) Open both tables and confirm that there are no extra records saved.

9) In the Datasheet view select all the fields of the INSTRUCTORS table and change the font Color to Purple, Font Size to 12, font Type to Algerian and apply Bold Formatting.

10) Adjust all the fields of the table accordingly. Save all the changes and close tables.

 

Part II

Query Design

Marks 3

 

1. Create a new query “QUERY1” which will select all the fields of VCMS2022 table sorted in descending order by ID.
2. Create a new query “QUERY2” which will select the field INS_ID, and INS_Name of INSTRUCTORS table whose name is Akram or Omar.

 

 

Part III

Form Design

Marks 5

 

1. Create a form using the form wizard with the following attributes:
   1. The source of records is VCMS2022

Select the following fields: TITLE, DEPARTMENT & OFFICE.

1. Layout is justified.
2. Title is VCMS2022.

2. Modify the form as shown below:
   1. Add your Full Name to the Form header and align text to center.
   2. Insert any image at the left-hand side and date and time at the right-hand side of the form header then save and close the form.

 

 

Part IV

Report  Design

Marks 5

 

1. Create a report using the report wizard with the following attributes:
2. The source of information is INSTRUCTORS table and Select all the fields.
3. Grouping Level is INS
4. Sort Order is Descending for Age.
5. Layout is Outline and Orientation is Landscape.
6. Title is Your First Name.