1.0.0 Microsoft Access Tutorial

This Microsoft Access tutorial is aimed at anyone who wishes to learn how to use Microsoft Access. It assumes a basic understanding of databases and how they work.

Table of Contents

This tutorial consists of the following lessons

1. Opening the Microsoft Access
2. Create A New Blank Database 
3. Create A Table in Design view
4. Create A Table using wizard
5. Create A Table by entering Data
6. Create A Relationship
7. Create A Query In Design View
8. Create A Query By Using Wizard

Explanation:

Week 1 - Data Analysis and Database Design
For Week 1, Miss Ayuni had explained on the topics about the sub topic we should be learning. Apart from that Miss Ayuni also already explain about marking for Data Analysis subject and Database Design (DADD) .
Miss Ayuni had also ordered we to divide a group .
Starting with chapter 1 .
Carry Mark
Report 1 : Table & Relationship
Report 2 : Query
Report 3 : Form
Report 4 : Report
Quiz 1 : 5%
Quiz 2 : 5%
Mid Term : 10%
Project(system) - Presentation : 10%
Final Report : 10%
Final Exam : 100% = 40%

Week 2 - Chapter 2
Enter chapter 2 for Data Analysis subject and Database Design (DADD).
Miss Ayuni have explained in detail on way to take Micosoft Access .
Stage by stage have shown to facilitate our learning process .

1.0.0.1 Definition of Data Type & Field Properties

Data Type

Text
Text, number, or a combination up to 255 characters

Memo
Similar to the text field, can contain text, numbers, or a combination up to 2 GB of data.

Number
Numbers up to 16 bytes of data

Date/Time
Date and Time information

Currency
Currency up to 8 bytes and precise to 4 decimal places

AutoNumber
Access creates a unique number for each new record. This is often the primary key for the table

Yes/No
Yes and No, stored as -1 for yes and 0 for no

OLE Object
Images, documents, graphs up to 2 GB

Hyperlink
Web addresses

Attachment
Attachments such as images, spreadsheets, documents, and charts.

Field Properties


Field Size : The maximum number of character you can enter in the field. The largest maximum you can set is 255. Press F1 to hlp on field size

Caption : The label text that is displayed for this field by default in forms, reports, and queries. If this property is empty, the name of the field is used. Any text string is allowed.

Decimal Places : Specifies the number of decimal places to use when displaying numbers.

Default Value : Automatically assigns the specified value to this field when a new record is added.

Format : Determines the way that the field appears when it is displayed or printed in datasheets or in forms or reports that are bound to the field. You can use any valid number format. In most cases, you should set the Format value to Currency.

Indexed : Specifies whether the field has an index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.). There are three available values:

Yes (No duplicates) : Creates a unique index on the field.
Yes (Duplicates OK) : Creates a non-unique index on the field.
No Removes any index on the field.

Input Mask : Displays editing characters to guide data entry. For example, an input mask might display a dollar sign ($) at the beginning of the field.

Required : Requires that data be entered in the field.

Smart Tags : Attaches a smart tag to the field.
Text Align Specifies the default alignment of text within a control.

Validation Rule : Supplies an expression that must be true whenever you add or change the value in this field. Use in conjunction with the Validation Text property.

Validation Text : Enter a message to display when a value that is entered violates the expression in the Validation Rule property.

1.0.1 Opening the Microsoft Access

STEP 1 (Opening the file)

1.0.2 Create a New File

STEP 1  (CREATE A NEW BLANK DATABASE)

 STEP 2 (CREATE A FILE NAME)

1.0.3 Create A Table in Design view

STEP 1 (CREATE TABLE IN DESIGN VIEW)

STEP 2 (INSERT FIELD NAME  & RIGHT CLICK TO ADD...)

(PRIMARY KEY OR...)

(INSERT ROW OR...)

(DELETE ROWS)

STEP 3 (SAVING TABLE NAME)

1.0.4 Create A Table using wizard

STEP 1 (CREATE TABLE BY USING WIZARD)

STEP 2 (SELECTING THE SAMPLE)

STEP 3 (RENAME THE TABLE)

STEP 4 (NEXT)

STEP 5 (TICK IN THE MIDDLE & CLICK FINISH)

STEP 6 (RESULT)

1.0.5 Create A Table by entering Data

STEP 1 (CREATE TABLE BY ENTERING DATA)

STEP 2 (DOUBLE “CLICK” A  FIELD TO CHANGE IT NAME)

1.0.6 Create A Relationships

STEP 1 (CLICK "RELATIONSHIPS" ICON ON THE TOOLBAR OPTIONS)

STEP 2 (SELECTING ALL BY HOLDING "CTRL",CLICK ADD)

 STEP 3 (DRAG PRIMARY KEY "BOLD FONT" TO FOREIGN KEY)

STEP 4 (TICK ENFORCE REFERENTIAL INTEGRITY, CLICK CREATE)

STEP 5 (RESULT.PS: DO THE SAME THING AS ABOVE)

1.0.7 Create A Query In Design View

STEP 1 (CLICK CREATE QUERY IN DESIGN VIEW)

STEP 2 (SELECT ALL BY HOLDING "CTRL", CLICK ADD)

STEP 3 (DRAG CUSTNO INTO THE FIELD)

STEP 4 (DRAG ANYTHING FROM THE TABLE INTO THE FIELD)

STEP 5 (SAVE AS)

1.0.8 Create A Query By Using Wizard

STEP 1 (CLICK CREATE QUERY BY USING WIZARD)

STEP 2 (SELECT THE TABLE/QUERIES)

STEP 3 (SELECTING BY CLICKING ">>" OR ">" INTO THE SELECTED FIELD, CLICK NEXT )

STEP 4 (CLICK NEXT)

STEP 5 (RENAME THE QUERY AND CLICK FINISH)

STEP 6 (RESULT)

2.0.1 QUESTION 1

Draw a complete E-R Diagram for the following

A) A customer reserves a date for maintanance or repairs to a vehicle. The reservation is given a Reservation_No, Customer_ID and Vehicle_No are recorded with the reservation. The Time_Of_Reservation is also recorded.
B) Information kept about customer includes Customer_ID, Customer_Name, Address and Telephone_No.
C) Information kept about the vehicle include Vehicle_No, Make, Reg_No and Date_Of _Manufacturing.
D) After examination, a number of job are recorded for the vehicle. Each job has a Job_No within the ing and reasons for carrying out the job are recorded as why needed.
E) The parts ued for each job and Time_Spent on each job are also recorded.
F) The information about parts include Part_No and Price.
    i) Find the entities
    ii) Find the attribute
    iii) Sketch ERD
    iv) Relationship
    v) Cordinalty Constraint
    vi) Attribute
    vii) Primary Key

Entities
1. Customer

• Customer_ID (PK)
• Customer_Name
• Address
• Telephone_No

2. Vehicle

• Vehicle_No (PK)
• Make
• Reg_No
• Date_Of_Manufacturing

3. Reservation

• Reservation_No (PK)
• Customer_ID
• Vehicle
• Time_Of_Reservation

4. Job

• Job_No (PK)
• Why_Needed
  

5. Parts

• Part_No (PK)
• Price
• Time_Spent

ERD

2.0.2 QUESTION 2

a) What is the defination of relational & why it is needed?
Relational key are device that relates one entity with another entity in relationship. A key consist of one or more attributes that can be identified in a row and enables us to link the table together in database for easier data access storage.

b) List and explain 4 type of relational keys.
i Candidate Key - an attribute or group of attribute that identify a unique row in a relation.
ii. Primary Key - any of the candidates that are selected to be the key or an attribute that uniquely identifies each row in a relational table.
iii. Foreign Key - an attribute in one table whose values must match the primary key in another table or whose value must not null.
iv. Composite Key - a primary key that consists of more than one attribute, especially in (M:N) relationship.

c) Based on table given below, answer all the following question:

i) What is the name for the above table?
BORROWER

ii)What is the primary key for above table?
BORROWER_ID

iii) What is data type for borrower's name, category, age and date of birth column?

1. BORROWER NAME - text
2. CATOGERY- text
3. DATE OF BIRTH - date/time
4. AGE - number

iv) How many attributes and records contains in the table?

1. Attributes - 7
2. Records - 2

v) What is the function of field size, input mask and defult value?

1. Field size - the maximum number of character you can enter in the field.
2. Input mask - a pattern for all data to be entered in this field.
3. Default value - a value that automatically entered in this field for new records.

2.0.3 QUESTION 3

Based on the diagram below, draw a complete entity relationship diagram (ERD) which include entity, attributes and relationship.

ANSWER:

2.0.4 QUESTION 4

Everyday, there are FIVE doctors is on duty in Orthopedic words. There are working on shift. A doctor can give a treatment to their patients and each patients can received a treatment from many doctor based on type of diseases. A patient can take a medicine but much medicine could be taken by many patients.

1. Doctor information is DoctorID and DoctorName
2. Patient information is PatientID, PatientName and Address
3. Medicine informtion is MedicineCode and MedicineName
4. For each treatment, it had TypeOfTreatment and CostOfTreatment
5. For each accepted medicine, it has a Quantity and Date

ANSWER:

2.0.5 QUESTION 5

Based on figure above, create a complete ERD which includes entities, attributes, relationships, relational keys and cardinalty constraints.

ANSWER:

2.0.6 QUESTION 6

ANSWER:

2.0.7 QUESTION 7