Redefi ning Quality
MICROSOFT ACCESS
Microsoft Access is one of the database application programs. A database application program is used create, edit, manage and
organize a database. A database is an organized collection of records. Ms Access uses table as the basic data storage object.
Other ms Access Objects include table, query, form, report and macro.
Database Objects and descriptions
Tables
A table is a set of columns and rows, with each column referred to as a field. Each value in a field represents a
single type of data. Each row of a table is referred to as a record.
Fields - the smallest unit of data storage e.g. Name or Age
Records - A record is one row of data in a table composed of a group of Fields.
Queries You use queries to retrieve specific data from a database and to answer questions about the data.
Forms
Form is used to enter, edit, and display data. It give the ability to choose the format and arrangement of fields.
Reports You use reports to analyze data or present data to others. Reports organize or summarize data so you can print it
or view it onscreen.
Macros
Macros provide the ability to automate tasks. You use a macro to add functionality to a form, report, or control.
Modules Modules are used to automate tasks and add functionality to a form, report, or control. Modules are written in
Visual Basic for Applications.
Ms Access table has an underlying table structure or design which defines the data characteristics (data type) as either:Number This allows storing of numerical values e.g. 42, 5100, 6000808
Currency - This allow storing of numerical values treated as currency e.g. 2000, 30000, 6580
AutoNumber - This allow storing of numerical values which are automatically generated by Ms Access program.
Text This allow storing of alphanumerical data, which is data that may not be needed in any mathematical operation.
E.g. lamu, po Box 11, BK 380 B, 07248018419 etc.
Date/time This allow storing of date and time data e.g 8.00 PM, 13/07/2014
Facts in access
You double-click an object to open it.
You double click on a fieldname to rename fieldname.
You click create tab to create an object.
You right-click an object to view a menu of options, to do such things as open objects, rename objects, and delete objects.
Objects that are open appear on tabs. Right-click a tab to view a menu of options to do save/close object, or change view.
Starting PowerPoint program
>> Click Start button point to All Programs Microsoft Office Microsoft Office Access
Create a Blank Database
4. Click the Create button.
NB: Access creates the database and opens a new table in
datasheet view.
To rename a fieldname e.g. ID, add new field:
1. Double click the fieldname
2. Type name you want to give field Name & press enter.
A blank database is a database with nothing in it. You must
create all objects. Creating tables is the first step in building a
database.
How to create a new database:
Data type: A data type is a set of data with values having
1. Start Access program.
predefined characteristics which determine what kind of data
2. Click Blank Database. (or Click office button >>New)
3. Type the name you want to give your database in the File it can hold and how that data is stored.
Name field.
1
Changing fieldname data type
Redefi ning Quality
How to create a new table
1.
2.
3.
4.
Save the table
Click on the fieldname
Click datasheet tab
On data type selection arrow chooses the data type
you want. E.g. Number, text etc.
5. Save table
How to change/Modify a primary key fieldname
1. Save the table
2. In the navigation pane Right Click on the table and
select design view option.
3. Right Click on the field name that you want to be the
primary key fieldname and select primary key option
{as shown in diagram below}.
1. Click create tab
2. Click table command
3. Double click fieldname named ID or right click it to
rename (change) its name. Click add new field to add
other field names of the table.
To close a database:
1. Click the Microsoft Office button. A menu appears.
2. Click Close Database. Access closes the database.
To exit Access:
1. Click the Microsoft Office button. A menu appears.
2. Click Exit Access. You exit Access.
To open an existing database:
1. Click the Microsoft Office button. A menu appears.
2. Click Open. The Open dialog box appears.
3. Locate the folder in which you stored your database.
4. Click the database name, then click Open button.
Tip: You can also open an existing database by pressing CtrlO and then following steps 3 through 5.
4. Save table
NB: A primary key in an Access database table is a unique
value that helps identify each record in the table.
Relationships
You can create relationships between your tables in a database diagram to show how the columns in one table are linked to columns
in another table. In a database, relationships enable you to prevent redundant data (unnecessary duplicate record).
A relationship works by matching data in key columns usually columns with the same name in both tables. In most cases, the
relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign
key in the other table.
Types of Table Relationships
2. Click Database Tools tab.
There are three types of relationships between tables. The type of 3. Click the Relationships Command. The Relationships
relationship that is created depends on how the related columns
window appears.
are defined.
4. Double-click each table or query you want to use to build a
One-to-Many Many-to-Many One-to-One
relationship, on the show table
One-to-Many Relationships
Dialog box. The tables appear in the Relationships window.
A one-to-many relationship is the most common type of
NB: if the Show Table dialog does not show click Show Table
relationship. In this type of relationship, a row in table A can
command.
have many matching rows in table B, but a row in table B can
5. Click Close button to close the Show Table dialog box.
have only one matching row in table A. For example, the
6. Drag the Primary tables primary key over the related
Student and Payment tables have a one-to-many relationship:
tables field. The Edit Relationships Dialog box appears.
each student can pay many times, but each payment comes from
only one student.
Conditions for a table relationship
1. One column involved in the relationship must be a primary key
10. Click the Enforce Referential Integrity checkbox.
column while the other related to it must not be a primary key.
11. Click Create. NB: relationship appears as shown below
2. Both columns involved in the relationship must be of the same
data type (i.e. number etc)
3. Make sure that all the tables are closed prior to creating the
relationship.
12. Click Save
4. Data in the columns involved in the relationship should be alike Query
as shown below:
A query is used to retrieve and display data that meet a certain
Dept no
Dept no
criteria or condition. Such conditions or criteria vary depending
KZ106
TT109
TZ108
TZ108
on the data types of the columns o be retrieve. Query is based on
TT109
TT106
tables(s) or another query.
TT109
To create relationships:
1. Close all tables and forms.
Redefi ning Quality
Steps of creating a query
Common controls found in a form or report are command
1. Click create tab
buttons, text boxes, checkboxes, combo box, list box etc.
2. Click query design command
NB: to manually draw or place a above controls in a form or
3. Double click the table/query you want to base your query
report you will have to view the form or report in design view
with e.g. Student
mode, the obtain the control from the control toolbox.
4. Click close
Creating macros
5. Add the required fieldnames into the query designer by
Steps
dragging them
(i) Creating a macro to open a form/ report /query/table
6. Enter the criteria in the appropriate columns if its required. 1. Click create tab
7. Click design tab and then Click run ( ! ) command
2. Click macro command & select macro. A new macro appears
Creating a Form
3. Under action area click the selection arrow and choose either
1. click the table/query you want to base your form with e.g.
Open form, Open report, Open query, Open table
Student
4. Under action arguments area select name of form, report,
2. Click create tab
query, table that you want to be opened by the macro.
3. Click form command
5. Save
Creating a Report
(ii) Creating a macro to close a form/report /query/table
1. click the table/query you want to base your report with e.g. 1. Click create tab
Student
2. Click macro command & select macro. A new macro appears
2. Click create tab
3. Under action area click the selection arrow and choose close
3. Click report command
4. Under action arguments area on object type selection arrow
Macro
click and choose either: Form/report/query/table. Then on
Controls are objects that allow display, navigation, opening and
object name selection arrow click and choose the
closing forms or reports.
Form/report/query/table you want to be closed.
5. save
EXERCISE:
STUDENT
Adm
no
Student
name
3301 John
Address Tow
n
Course
Create a database named your name, and
then create the following tables in it.
1. Student 2. Courses 3. Payments
PAYMENT
P o box Msa Computer
Receipt no
Data paid
12
76 Nkr ATC1 13-July-13
3302 Annah
P O BOX
77
14-July-13
11
78 Nrb ATC2 16-July-13
3303 Joyce
P o box
3305 Ahmed
Adm no
13000
3303
6000
3305
6500
3304
16-July-13
15000
3307
P o box
81 Ksm ATC1 01-July-13
99
82
28-July-13
P o box Msa Computer
88
6000
3302
12000
3307
01
3304 Jeremy
Amount paid
79
3306 Ali
P o box
77
Msa CPA1
3307 Joyce
P o box
100
Nrb Tourism
3308 Antony
Po box 44 Msa CPA1
COURSE
COURSE
SUBJECTS
FEES
ATC1
BM,ICT,ENT
12900
ATC2
Audit,Law,cost
13000
COMPUTER
Word,excel,Access
14900
CPA1
FA,ENT,LAW
15800
CPA2
Cost,Audit,econ
TOURISM
Travel,Animal,security
TABLE RELATIONSHIP
Create a relationship for the above tables
QUERIES
Create a query that will show:
a) Only students doing computer course and save it as computer students
Field
Adm no Student name
Address course
Table
Sort
Show
Criteri
a
computer
Or
b) Only students doing cpa1 course and save it as CPA1 students
Field
Adm no Student
Address course
name
Criteri
CPA1
a
Prepared by Mr. mwangi
6000
40000
c) Only payments whose amount paid is Above 10000 and save it as payments above
10000
Field
Receipt
Date Paid Amount
Adm No
No
Paid
Criteri
>10000
a
d) Only payments whose amount paid is below 10000 and save it as payments below
10000
Field
Receipt No
Date Paid
Amount Paid
Adm No
Criteria
<10000
e) All payments made on 13th OR 15th July 2013 and save it as payments dated 13 or 15
Field
Receipt No
Date Paid
Amount Paid
Adm No
Criteria
=13/7/2013 OR =15/7/2013
f) All payments made between 13th and 19th July 2013 and save it as payments dated btw 13
and 19
Field
Receipt No
Date Paid
Amount Paid
Adm No
Criteria
>13/7/2013 AND <19/7/2013
g) All courses which have cost subject and save it as courses with cost subject
Field
Course
Subjects
Fees
Criter
*cost*
ia
h) All students whose name start with letter j and save it as letter J students
Field
Adm no
Student name
Address
course
i)
Criteria
J*
All students whose name end with letter y and save it as letter Y students
Field
Adm no
Student name
Address
course
Criteria
*y
FORMS
Create a form for each table above
REPORTS
Create a report for each query created above
MACRO
Create a macro to open a form and another to close the form
QUESTION ONE
Create the following table and do the tasks that follow
Table: Management team
Emp Number
Last name
First Name
Street
City
Zip
Telephone
1
Munoz
Loius
11 Ram bridge
Carbodinate
81623
970-4338
2
Lattimore
Joseph
302 Garden
Carbodinate
81623
970-4345
3
Artist
Patricia
45 Williams
Basalt
81621
970-0074
4
Renaldo
Carlos
793 Ridge
Basalt
81621
970-1265
5
Norwick
Robert
10 Webb
Carbodinate
81623
970-2838
6
Kamp
James
93 Pond drive
Carbodinate
81623
970-5944
7
Franz
Richard
39 Brook
Basalt
81621
970-2648
8
Collins
Timothy
8 Powder
Basalt
81602
970-8943
9
Stokes
George
9 Young
Glenwood
81627
970-7890
Tasks
1. Create Query of "carbodinate" city save it Dwellers of carbodinate city.
2. Create Query of management team whose first name start with letter R & save it as first name R
managers
3. Create Reports of queries above
4. Create a form of the above table.
5. Create a macro to open table created above and another to close the table.
6. Create a macro to open form created above and another to close the form.
Prepared by Mr. mwangi
QUESTION TWO
Create the following table and do the tasks that follow
Table: Courses
Co_code
Course title
Tutor ID
Ac 121
Accounting
JM 25
Co 111
Computer Operator
LK 15
Mc 458
Mass Communication
BR 17
Mg 569
Management
SF 15
Mk 587
Marketing
SA 48
Pb 568
Publishing
JT 18
Se 245
Secretarial
FG 74
Tutors
Prepared by Mr. mwangi
Tutor ID
BR 17
FG 74
JM 25
JT 18
LK 15
SA 48
SF 15
Table Student
Name
Betty Rocha
Felis Gate
James Mwaka
Jane Tambo
Lucas Kim
Sammy Ali
Sarah Faulu
Prepared by Mr. mwangi
Contact
0712255431
07211457
0728844317
0712266473
0715544713
0721113623
0712288461
Adm No
Name
Co_ code
Contact
3373
Sally Kim
Co 111
0713366589
3374
Ali Juma
Ac 121
0714488259
3375
Faith Awuor Se 245
0732255481
3376
Zena Salim Mg 569
0725566412
3377
Dan Maina
Pb 568
0721155884
3378
Ian Njeru
Mk 587
0715544367
3379
Caleb Bill
Mc 458
0721144975
TASKs:
i.
Create a relationship of the tables
ii.
Create a query for student name and course title.
iii.
Create a query for tutors name and course title
iv.
Create a query for tutors name and student name.
QUESTION ONE
Create the following table and do the tasks that follow
Table: Employee Table
Emp Number
1
2
3
4
5
6
7
8
9
Last name
Munoz
Lattimore
Artist
Renaldo
Norwick
Kamp
Franz
Collins
Stokes
First Name
Loius
Joseph
Patricia
Carlos
Robert
James
Richard
Timothy
George
Street
11 Ram bridge
302 Garden
45 Williams
793 Ridge
10 Webb
93 Pond drive
39 Brook
8 Powder
9 Young
City
Carbodinate
Carbodinate
Basalt
Basalt
Carbodinate
Carbodinate
Basalt
Basalt
Glenwood
Zip
81623
81623
81621
81621
81623
81623
81621
81602
81627
Telephone
970-4338
970-4345
970-0074
970-1265
970-2838
970-5944
970-2648
970-8943
970-7890
Tasks
1 Create Query of "carbodinate" city save it Dwellers of carbodinate city.
2 Create Query of employees whose first name start with letter R & save it as first name R
Employees
3 Create Reports of queries above
4 Create a form of the above table.
5 Create a macro to open table created above and another to close the table.
6 Create a macro to open form created above and another to close the form.
QUESTION TWO
Create the following table and do the tasks that follow
Table: Department Table
Table: Employees Table
Dept
Name
Design
Testing
Engine
Wiring
Chasis
Dept
No
21
22
23
24
25
No of
employees
67
25
50
34
69
Head
Soud
Haddady
Wangare
Kamau
Wanjohi
Prepared by Mr. mwangi
Hamisi
Em
p
No
001
Chris
Name
Ag
e
Dep
t No
24
21
002
27
25
Sudi
003
30
25
Said
004
13
22
Hamid
a
005
29
21
Salary
25,00
0
24,00
0
23,00
0
22,00
0
21,00
0
Employmen
t Date
4/9/2000
11/5/1988
6/23/1999
6/6/1986
3/3/1990
Task
a) Set Dept No, Emp No as primary keys for department table and employees table respectively
b) Create relationship of the two tables
c) Create query for each table
d) Create query for department names, employee name and their salary
e) Create Reports of each queries above and each table
f) Create a form of each table above.
g) Create a macro for each table and form that will open table and form
h) Create a macro for each table and form that will close table and form
QUESTION THREE
Create the following table and do the tasks that follow
Table: Courses
Tutors
Co_code
Ac 121
Co 111
Mc 458
Mg 569
Mk 587
Pb 568
Se 245
Course title
Accounting
Computer Operator
Mass Communication
Management
Marketing
Publishing
Secretarial
Tutor ID
JM 25
LK 15
BR 17
SF 15
SA 48
JT 18
FG 74
Tutor ID
BR 17
FG 74
JM 25
JT 18
LK 15
SA 48
SF 15
Table Student
Prepared by Mr. mwangi
Name
Betty Rocha
Felis Gate
James Mwaka
Jane Tambo
Lucas Kim
Sammy Ali
Sarah Faulu
Contact
0712255431
07211457
0728844317
0712266473
0715544713
0721113623
0712288461
Adm No
Contact
Name
Co_ code
0713366589
3373
Sally Kim
Co 111
0714488259
3374
Ali Juma
Ac 121
0732255481
3375
Faith Awuor
Se 245
0725566412
3376
Zena Salim
Mg 569
0721155884
3377
Dan Maina
Pb 568
0715544367
3378
Ian Njeru
Mk 587
0721144975
3379
Caleb Bill
Mc 458
TASKs:
i Set Co_code, Tutor ID, Adm No as primary keys for table courses, tutors and student respectively
ii Create a relationship of the tables
iii
Create a query for student name and course title.
ivCreate a query for tutors name and course title
v Create a query for tutors name and student name.
Prepared by Mr. mwangi
