MICROSOFT OFFICE ACCESS

MICROSOFT OFFICE ACCES

INTRODUCTION TO ACCESS

A database is a collection of information that’s related to a particular, subject or purpose such as tracking customer’s orders or maintaining a music collection. If your database isn’t stored on a computer or only parts of it are you may be tracking information from a variety of sources that you’re having to coordinate and organize yourself.

 

For example, suppose the phone numbers of your suppliers are stored in various locations, in a card file containing supplier phone numbers, in product information files in a file cabinet, and in a spreadsheet containing order information. If a supplier’s phone number changes, you might have to update that information in all three places. In a database however you only have to update that information in one place –the supplier’s phone number is automatically updated whatever you use it in the database.

 

TABLES

To store your data create one table for each type of information that you track .To bring the data from multiple tables together in a query,form,report or data access page, define relationship between the tables.

QUERIES

To find and retrieve just the data that meets conditions that you specify, including data from multiple tables create a query. A query can also update or delete multiple records at the same time, and perform predefined or custom calculations on your data.

FORMS

To easily view, enter and change data in a table, create a form .When you open a form, Microsoft Access retrieves the data from one or more tables, and displays it on the screen with the layout you choose in the Form Wizard or with the layout that you created on your own in Design View

REPORTS

To analyze your data or present it a certain way in print, create a report. For example you might print one report that groups data and calculates totals and another report with different data formatted for printing mailings labels.

 

EXERCISE 1

Save the database as “YOUR NAME”

Table 1

FIELDS NAME	DATA TYPE
EMPLOYEE ID	NUMBER
PROFFESSION	TEXT
DEPARTMENT	TEXT
AGE	NUMBER
GENDER	TEXT
FIRST AWARD	TEXT

 

EMPLOYEES TABLE 2

EMP ID	PROFFESSION	DEPARTMENT	AGE	GENDER	FIRST  AWARD
1001	ECONOMIC	ACCOUNTS	33	MALE	RANGE ROVER
1002	PROGRAMMER	IT	27	MALE	DISCOVER
1003	DOCTOR	HEALTH	36	FEMALE	FREELAND
1004	ANALYST	IT	40	MALE	FORD
1005	SECRETARY	PERSONNEL	30	FEMALE	INTCOOLER
1006	TUTOR	EDUCATOR	28	FEMALE	RAV4
1007	ECONOMIST	ACCOUNTS	60	MALE	VITARA
1008	ANALYST	IT	30	FEMALE	MARK 11
1009	PROGRAMMER	IT	50	MALE	CHASER
1010	ENGINEER	MANUFACTURE	26	MALE	BLUEBIRD
1011	SECRETARY	PERSONNER	35	FEMALE	CROWN
1012	AUDITOR	AUDITING	40	MALE	VOLVO

 

TABLES 2

FIELD NAME	DATA TYPE
EMPLOYEE  ID	NUMBER
PROFESSION	NUMBER
DEPARTMENT	TEXT
AGE	TEXT
GENDER	TEXT
SECOND AWARD	TEXT

 

EXERCISE 2

EMP ID		PROFFESSION		DEPARTMENT		AGE	GENDER	SECOND AWARD
1001		ECONOMIST		ACCOUNTS		33	MALE	HOUSE
1002		PROGRAMMER		IT		27	MALE	COMPUTER
1003		DOCTOR		HEALTH		36	FEMALE	LAB FACILITIES
1004		ANALYST		IT		40	MALE	COMPUTER
1005		SECRETARY		PERSONNEL		30	FEMALE	CELL PHONE
1006		TUTOR		EDUCATOR		28	FEMALE	STATIONARIES
1007		ECONOMIST		ACCOUNTS		60	MALE	HOUSE
1008		ANALYST		IT		30	FEMALE	COMPUTER
1009		PROGRAMMER		IT		50	MALE	COMPUTER
1010		ENGINEER		MANUFACTURE		26	MALE	TOOL KIT
1011	SECRETARY		PERSONNEL			35	FEMALE	CELL PHONE
1012	AUDITOR		AUDITING		40		MALE	MARK II

Create the following Queries and save them as Query 1 to 11

1. List all analysts
2. List all male doctors
3. List all doctors
4. List all employees whose age is between 30 and 45 inclusive.
5. List all employees who are educator department and programmers
6. Display all secretaries and show the field’s first award, age and department
7. List all male employees
8. List all doctors with age between 35 to 50 inclusive
9. List all female employees
10. Create a report using related tables 1, Employee ID, Profession, Department, first Award, table 2, second award, age, Gender.
11. Create a form using related table
12. List all ends with letter R
13. List all begins with letter E

 

EXERCISE 3

Questions

1.  Create a new database using EMPLOYEES RECORDS as the file name save it.

2.  Create a table with the following structure

FIELDS NAME	DATA TYPE	DESCRIPTION
Emp ID	Number	Employee  identity
Ename	Text	Employees First name
Lname	Text	Employee Last Name
Company Name	Text	Employee Company
Salary	Currency	Employee Salary
Start date	Date/time	Employee Start Date
Department	Text	Employees  Department

3.  Set the primary key and save the table using EMPLOYEES RECORDS as the name

4.  Add the following records to the employees records table.

EMP  ID	F NAME	L NAME	COMPANY NAME	SALARY	START DATE	DEPARTMENT
11	Grace	Kimaro	TBL	200000	1/1/2002	Accounts
12	Ally	Omari	TANESCO	500000	2/3/1990	Sales
13	Rehema	Chanyika	BOT	400000	12/5/2001	IT
14	Juma	Madaha	PPF	600000	3/3/1995	Finance
15	Salama	Mwinyi	BOT	600000	4/4/2002	Marketing
16	Eva	Adugo	TTCL	300000	2/2/2002	IT
17	Donesta	Simon	TANESCO	400000	1/2/2000	Finance

 QUESTION 2

1. Create the following Queries using employees database and records table
2. List all employees who earn more than 400000, save a query as salary
3. Produce a list of employees in finance department save a query as department
4. List staff that started after 12/12/2001 save a query as department
5. List staff whose company name is BOT save as BOT

QUESTION 3

1.       Create a form using Employees records table

2.       Create a form using salary query

3.       Create a form using start date query

4.       Create a form using BOT query

QUESTION 4

1. Create a Report using Employees records table
2. Create a Report using BOT query
3. Create a Report using start date query
4. Create a Report using salary query
5. Create a Report using Department query.

 

EXERCISE 4.

QUESTIONS

1. Create the following database called PASSION
2. Create a table called Days within Passion

COUNTRY	CITY	RAINFALL	DAYS	CAUSES	ZIP	POPULATION
Tanzania	Dodoma	200	90	Floods	255	1200
Kenya	Nairobi	400	90	Malaria	196	6000
Uganda	Kampala	700	98	Floods	395	4000
Zambia	Lusaka	900	95	Stomachache	289	7000
Zimbabwe	Harare	300	94	Yellow fever	890	8000
America	Washington	100	93	Car accident	250	5000
Sudan	Khartoum	300	92	Yellow fever	780	4000
Cuba	Havana	800	90	Malaria	900	3000
India	Delhi	900	98	starvation	991	2000

3. List all the country caused the floods
4. List the numbers of population which is greater than 5000
5. List all the country ends with A
6. List all the country which zip codes begin with 2 displays the field year, caused and country
7. Sort days in ascending order by name and format the text by purple color
8. List all country below 700 rainfalls and less than year 98
9. Create a report displays the field country, year and zip
10. Create the form table days, label your form by “floods “and insert date and time.

 

EXERCISE 5

1. Create the following database called VICASI

2.    Create a table called CUST  

3. Cust ID should be the primary key

CUST ID	F NAME	L NAME	ADDRESS	CITY	ZIP	COUNTRY
100	JAY	KABALE	103	MBY	92472	TZ
101	WILLY	WILSON	1273	DSM	92492	TZ
102	PATRICE	MSUYA	1478	ARS	92467	TZ
103	NORA	ANTONY	23	MG	99002	TZ
104	ALAN	JUMA	43	DSM	92262	TZ
105	Y VONE	HAJI	327	DOM	92090	TZ
106	GEORGE	JAMES	951	MBY	92469	TZ
107	JOY	KAYUMBA	346	IR	90299	TZ
108	RAYMOND	MAGANJA	88	ARS	92492	TZ
109	POSCO	RODGER	720	MBY	92421	TZ
110	PETER	MANGI	612	DSM	90023	TZ
111	HAPPY	JERRY	820	KIL	91137	TZ
112	SUDDY	EDSON	950	ARS	93246	TZ
113	NEEMA	GILLS	1125	IR	92247	TZ
114	HAWA	ALBERT	532	MG	90024	TZ
115	ROSE	BAKER	1237	DOM	91025	TZ
116	ATINDA	HASHIM	116	MG	91038	TZ

4.  Create the following query and save ad DEMO 1 to 6

5.  List all cities which are not from MBY and ARS

6.  List all cities which are DOM

7.  List all cities which begin with M displays the field FNAME, ZIP, CITY, ADDRESS

8.  Create a query which the city ID who are than 110

9.  Create a query which shows the city of IR and DSM

10. Sort the address to descending order in your cust table and change the front to be COURTER NEW and size 11 blue colors

11. Create a form by using design view use DEMO4 and the header must be the cities which start with M and the footer must be ST PIO TRAINING COLLEGE

 

EXERCISE 6

1.    Create the database called DENTIST

2.    Create the table called DENT

FIELD SIZE	DATA TYPE	FIELD SIZE
Parent Name	Text	12
Date of birth	Date/time	Short date
Age	Number	Long Integer
Appointment date	Date/time	Short Date
Appointment time	Date/time	Short time
Place	Text	4
Attend	Yes/no	Yes/no

 

Child Name	Parent Name	Date of birth	Age	Appointment date	Appointment time	Place	Attention
James bolter	Anne Bolfer	7/3/97	2	7/25/99	11:00	MMC	N
Jack Marci	M Marcy	11/5/97	1	7/7/99	14:15	UMMU	N
Kato Granty	J.Grani	5/6/97	2	8/1/99	14:00	UMU	Y
Lee Smith	C.Smith	6/6/97	4	8/1/99	14:30	MMC	YY
Kim Davis	D.G  Davies	2/28/97	2	8/13/99	15:00	AGA	Y
Mauth Willy	L.Willy	7/25/96	2	8/14/99	14:15	OR	Y
Henry Artha	G.Artha	7/3/97	3	8/18/99	14:30	AGA	Y
John Thomas	M.Thomas	3/3/96	1	8/13/99	15:32	OR	Y

3.    Create the following query save the as DEN to DEN5

4.    List children who attended MMC

5.    Display the list of children less than 2years inclusive

6.    List all children who were born before 6/6/97

7.    List all children who did not fulfill their appointment

8.    Last all children who are more than 3 years inclusive

9.    Arrange the names of the children in the table above in ascending order.

 

EXERCISE 7.

ID NO	FIRST NAME	LAST NAME	SEX	PROFESSION	AGE	SALARY
1	DELIA	ABIGAIL	F	ACCOUNTANT	40	50000
2	ALEXANDRA	GILIAN	M	TEACHER	22	62000
3	LEYLA	FRANK	F	DRIVER	33	54000
4	ALEX	JIMMY	M	CLEANER	45	98000
5	MARIAMU	PROTAS	F	DRIVER	60	96000
6	MINA	JOHN	F	CLEANER	47	62000
7	MIRIAM	ABDUL	F	ACCOUNTANT	54	51000
8	TATU	ABDALLAH	F	TUTOR	62	57000
9	JUMA	ROBERT	M	TUTOR	35	68000
10	FELIX	TOM	M	TEACHER	21	63000
11	NGANGA	FINCH	M	PLUMBER	61	98000
12	RICHARD	MKAKA	M	PLUMBER	54	78000
13	TINA	NASSORP	F	PROGRAMMER	58	96000
14	YUSUPH	MKOKI	M	PROGRAMMER	59	78000
15	EVELYN	DAWSON	F	TUTOR	52	74000
16	ROSE	REDFORD	F	DOCTOR	32	71000
17	ISSAC	FRIDAY	M	DOCTOR	27	56000
18	ALEX	MAGEMBE	M	NURSE	29	63000
19	CHACHA	KISHIMBA	M	NURSE	24	68000
20	ALFRED	WILLIAM	M	DRIVER	50	61000
21	ZUBEDA	PHILIPS	F	TEACHER	62	87000
22	MALIKI	AHMED	M	CLERK	25	95400
23	JAFARI	JUMA	M	CLERK	62	62300
24	MRASHI	JAMES	F	NURSE	20	75000
25	JOSEPH	PETER	M	DOCTOR	24	74000

 

QUESTIONS

1. List all with F name beginning with J
2. List all with age less than 30 or greater than 60
3. All who earn greater than 70000 and are female
4. All accountant and cleaners
5. All with F name containing the letter X and Z
6. All with age between 45 and 55
7. All with L names ending with A or ending with H
8. All who are 62 years old
9. All clerks and doctors 
10. All males who are less than 50
11. Show Alex and Zubeda
12. All with salaries between 70000 and 80000

 

 

EXERCISE 8

1.       Create in a database and call it KISIWANI

2.       Create the following table and call it WORKERS

ID	NAME	SEX	M STATUS	PAYMENT	DEDUCTIONS	GROSS PAY
1001	ALICE WAVINYA	F	DIVORCED	10000	2100
1002	JOHN MWANGI	M	SEPARATED	25000	8500
1003	NICK OTIENO	M	SINGLE	48000	12000
1004	JACINTA CHEBET	F	MARRIED	12000	3000
1005	ALI HASSAN	M	MARRIED	8000	1500
1006	MARY NDENGWA	F	DIVORCED	65000	13000
1007	HELLEN ELWARK	F	SINGLE	21000	12500
1008	SONIA PATEL	F	MARRIED	5000	3000
1009	ALEX WAKOLI	M	MARRIED	32000	14000
1010	ERIC WARUI	M	SINGLE	15000	3500
1011	NANCY WAMBUYA	F	MARRIED	8000	3400

3.       Create a query to calculate gross pay: PAYMENT -DEDUCTIONS

4.       Create the following queries

a)    List all with ID 1004 to 1009(ID)

b)    List all male workers who are married (MARRIED)

c)    List all payment greater than 25 000 (SALARY)

d)    List all who earn less than 10000(LOW)

e)    List all with names ending with A(A)

f)     List all who are married and earn more than 10000(SAL)

g)    List all with name starting with J(J)

5.       Create the following

a)    A form for the query married (layout –justified)

b)    A form for the table workers (layout- columnar)

c)    A form for the query low (layout – datasheet)

6.       Create the following reports

Ø   A report for the table workers

 

EXERCISE 9

1. Create a project called MTAKUJA database
2. Create a table set ID NO as the primary key
3. Save the table as PAY SLIP
4. Enter the record as it appears on the worksheet provided

ID NO	FISRT NAME	LAST NAME	SEX	PROFESSION	AGE	DATE OF APPOINTMENT	SALARY
1	DELIA	ABIGAIL	F	ACCOUNTANT	40	5/1/2000	50,000.00
2	ALEXANDER	GILIAN	M	ENGINEER	35	6/1/2000	35,000.00
3	FIONA	DAWN	F	SECRETARY	45	3/1/2000	35,000.00
4	GWENDILINA	DOUGLAS	F	ENGINEER	40	6/2/2000	50,000.00
5	FLORENCE	RALPH	FM	ENGINEER	40	5/1/2000	60,000.00
6	SAID	SULEMAN	F	CLERK	25	7/3/2000	25,000.00
7	AIKA	RAMADHANI	M	CLEANER	40	6/1/2000	15,000.00
8	ROBIN	JOSEPH	M	MASSENGER	22	3/3/2000	15,000.00
9	WILLIAM	WYNE	M	SURVEYER	35	5/1/2000	20,000.00
10	JOSEPH	WILLIUM	F	ENGINEER	45	5/1/2000	50,000.00
11	ASHA	JUMA	M	ENGINEER	40	6/1/2001	60,000.00
12	MWANAIDI	HASSAN	F	CLERK	40	3/1/2000	25,000.00
13	PAUL	SAID	M	CLENER	25	5/1/2000	15,000.00
14	SULEIMAN	ALEXANDRER	M	MASSENGER	40	7/3/2000	15,000.00
15	RALPH	SULEIMAN	M	SURVERYER	34	5/1/2000	20,000.00
16	FIONA	GWENDILINI	M	CLANER	34	6/1/2000	15,000.00
17	RAMADHANI	FLORENCE	M	CLERK	33	3/1/2000	25,000.00
18	JOSEPH	SAID	M	CLENER	48	7/3/2000	15,000.00
19	WYNE	WILLIUM	F	CLERK	45	6/1/2000	25,000.00
20	ASHA	ROBIN	F	CLENER	34	3/3/2000	15,000.00
21	JUMA	WILLIAM	M	ENGINEER	23	6/1/2000	55,000.00
22	ALLY	JOSEPH	M	MASSENGER	46	6/1/2000	60,000.00
23	HASSAN	ALLY	M	ENGINEER	43	3/1/2000	15,000.00
24	ZAKARIA	JOSHUA	M	ENGINEER	35	8/2/2000	50,000.0
Specify and save as the name relevant to whatever you have manipulated a)    a)Male of age between 30 g and 40 sort age in descending order b)    b)All those last name STARTS WITH “S” c)    c)All female who are engineers sort name in ascending order d)    d)All those first name ends with “A” e)    e)Find those whose salary is 20000 or below 20000 f)     f)Find those who are engineer and their last name start with “J” having the salary 50000 or greater g)    g)Find those whose first name start with “a” having four letter sort last name in ascending order             FORM &REPORT 6.Create a form for those whose salary is 20000 or below and produce their report Create form that used to enter value in table pay slip. The salary primary format should be currency to accept $sign 8.Produce a report for female who are engineer omitting their ID NO ,Sex and Profession, report heading should be FEMALE ENGINEERS 9. Produce a report that is a pay slip for all in company MTAKUJA.

Exercise 10

Sylvia is the chairlady for the World Starts With Me Club. She has been told to use a database management system (DBMS) to help the club maintain records on the club's members. Perform the following operations just like Sylvia would.

        (a)       Create a database file called World Starts that has the following fields of data for each member:

                                                                                                                                                                                                         (2 marks)

        (b)           Input the following data save the table as WSM                                                                                         (14 marks)

Last Name	First Name	Membership NO	Date of registration	Age	Fee	Fully paid
Lusomo	Davy	1234	12/1/01	15	100	Y
Roy	Elvis	5858	10/1/01	15	50	N
Hannah	Toto	595	08/1/01	14	0	N
Hertier	Ndihano	966	12/1/01	15	0	N
Aggie	Njambi	238	10/1/01	13	70	N
Hussein	Shukri	868	12/1/01	15	100	Y
Otieno	Ibra	628	12/1/01	15	100	Y
Muthoni	Sharone	744	11/1/01	16	60	N

(c)    Create a report that contains Members first name, number, date of registration and payment status for those registered on 12/1/01                                                                                                                                                          (4 marks)

(d)   Make the Membership number primary key                                                               (2marks)

(e)    Format the fee figures to 2 decimal places.                                                                (1 mark)

(f)    Save the query as Club24                                                                                           (2 marks)

(g)   Create another query that would be used to display those last name starts with letter H and aged 15 name it "Age 15"                                                                                                                                                         (5 marks)

(h)   There was an outcry that the registration amount is very little, using anotherupdate the members fee by 10% save the table as WSM2                                                                                                                                             (4 marks)

(i)     Excluding the FULLY PAID field, create a report and give it the title: World Starts With Me Club.                                                                                                                                                                                  (4 marks)

(j)     Save the Report as Our World.                                                                                               (2 marks)

(k)   Create a tabular form and get the total fees collected. Save it as From fees.                        (6marks)

(l)     Print Club24, Our World, WSM, WSM2                                                                                     (4 marks)

 

Exercise 11

2.      The tables below, STUDENT, SUPERVISOR and SUPERVISIONS are extracts of records kept in MOKASA UNIVERSIRY for project supervisions.

 

Student Number	Name	Gender	Project  Fee Paid
C001	Ken	M	32000
C002	Joy	F	27800
C003	Lero	M	18900
C004	Moth	F	42700
C005	Ben	M	45000

Table 1 STUDENTS TABLE

 

Supervisor Number	Name	Department
L220	Alex	Mechanical
L230	Sakaja	ICT
L240	Roy	Electronics
L250	Mati	Education
L260	Joy	Human Resource

Table 2  SUPERVISOR S’ TABLE

 

Supervision Number	Supervision Date	Student Number	Supervisor Number	Project Title
100	12/03/2015	C001	L220	Java
200	22/03/2015	C003	L230	Website
300	17/03/2015	C004	L240	Robotics
400	02/03/2015	C001	L220	Java
500	18/03/2015	C002	L240	Robotics
600	12/03/2015	C004	L230	Java
700	11/03/2015	C002	L250	Database
800	12/03/2015	C003	L220	Java
900	12/04/2015	C005	L250	Database
1100	12/04/2015	C002	L250	Database

Table 3  SUPERVISIONS TABLE

 

a)      Using a database application software, create a database file named PROJECT                (1mk)

b)     Create three tables named STUDENT, SUPERVISOR and SUPERVISIONS as shown above.                                                                                                                                                             (9mks)

c)      Set the primary key for each table.                                                                                         (3mks)

d)     Create relationships among the tables.                                                                                    (2mks)

e)      Enter the data in the table STUDENT, SUPERVISOR and SUPERVISIONS as shown above.                                                                                                                                                 (9mks)

f)       Create a form for each table above.                                                                                        (3mks)

g)      Create a query named BALANCE to display student name, Gender, project fee balance per student, given that the total   project fee   is Ksh.50000.                                                      (4mks)

h)     Create a query named BALANCE2 to display students’ Names, project title whose fee balance is aboveKsh.20000.                                                        .                                                           (4mks)

i)        Create a bar chart based on question (h) to display students Name and balance. Save as BALANCE CHART.                                                                                                        (3mks)

j)       Create a report named SUPERVISION to display Students Names, Project Title, names of supervisor, and supervision Dates. The records in the report should be grouped by students’ Name and the number of supervisions by each student should be displayed.                            (5mks)

 

k)     Title the report as supervision per lecturer.                                                                             (2mks)

                                   

l)        Print  the  following:

·         Tables: STUDENT, SUPERVISOR   and SUPERVISIONS                            (2mks)

·         Queries: BALANCE  and BALANCE2                                                             (2mks)

·         REPORT: SUPERVISIONS                                                                              (1mk)

 

 

 

 

Exercise 12

G4s Company is an organization that has employed several workers. In order for it to monitor the performance of its workers and the different duties assigned to its workers, the company needs a database to organize the information required.

(a)        Create a database file and name it G4S COMP.                                                                   (2mks)

(b)        (i)   Using the table below create the appropriate fields and split the data into two tables

“EMPLOYEE DETAILS’ and ‘PERSONAL DETAILS’.                                             (14mks)

EMPLOYEE NO.	NAME	DEPARTMENT	MARITAL STATUS	SALARY	AGE
2213	JOHN CLAY	DRIVER	MARRIED	8,000.00	35
2214	ROSE JOHNS	CLERK	MARRIED	10,000.00	40
2215	PETER ROGERS	DOCTOR	MARRIED	50,000.00	45
2216	JED OTIENO	ACCOUNTANT	SINGLE	20,000.00	25
2217	VINCENT JED	DRIVER	SINGLE	8,000.00	20
2218	ALLAN LIMO	GROUNDSMAN	SINGLE	4,000.00	22
2219	PETER OLOO	ASS. MANAGER	MARRIED	80,000.00	35
2220	HUSSEIN KIMANI	CASHIER	SINGLE	15,000.00	26
2221	ROBERT KIBANI	WATCHMAN	SINGLE	5,000.00	28
2222	JANE LESSOS	SECRETARY	MARRIED	6,000.00	31
2223	LUCY OJWANG	CLERK	MARRIED	8,000.00	30

 

(ii)        Create screens for each table for inputting the data in the table above                                  (6mks)

(iii)             For each of the tables, choose the most appropriate key.                                           (3mks)

(iv)       Create a relationship between the two tables.                                                                        (4mks)

(c)        Create a query from the two tables ‘employee details’ and ‘personal details’ to display the fields Name, Department and Salary for those employees who earn more than 10,000.00. Save as

EARN ABOVE 10K.                                                                                                                        (6mks)

(d)       (i)         Generate a tabular report with landscape orientation from the tables to display the fields in

the following order.                                                                                                    (6mks)

EMPLOYEE NO.     NAME            SALARY      DEPARTMENT      AGE

(ii)        Sort records in the report in alphabetical order of the name field.                             (2mks)

(iii)       Compute the total of salary for all the employees and place it below the salary column. Save as SALARY.                                                                                                                  (5mks)

(e)                Print EMPLOYEE DETAILS TABLE, PERSONAL DETAILS TABLE, EARN ABOVE   10K and SALARY.                                                                                          (2mks)

 

Exercise 13

 

1.  The management of BIMA HARDWARE has requested you to design a database management system (DBMS) that can be used to update records of its daily transactions.

Required.

1.      Create a relational database called BIMA with the following tables and fields. (6 Marks)

1. Customertable.

 

CUSTID

CUSTNAME

CITY

 

2. Ordertable.

 

TRANSID

CUSTID

PRODUCTS

QUANTITY

DATEOFORDER

 

3. Producttable.

 

NAME

PRICEPERITEM

 

2. Determine the primary keys and the foreign keys in the above tables and create a relationship between them. (4 Marks)

 

3. Create a form called frmproduct to enter the following details in the products table.  (4 Marks)

 

NAME	PRICEPERITEM
Cement	600
Crown paint	1200
Barbed wire	780
Iron sheet	560
Hammer	450
Padlock	500
Pliers	230
Wire mesh	895

 

4. Create one form called frmcustomers to enter the following details in the customers table and orders table.                                                                                                             (8 Marks)

 

Customertable

 

CUSTID	CUSTNAME	CITY
1	Joseph otieno	Nairobi
2	Haman njoroge	Kiambu
3	Mary mutua	Machakos
4	Mainajoe	Nairobi

 

Ordertable

 

TRANSID	CUSTID	PRODUCTS	QUANTITY	DATEOFORDER
1	1	Cement	10	2/3/2012
2	2	Cement	12	2/3/2012
3	1	Padlock	5	3/3/2012
4	4	Wire mesh	6	3/3/2012
5	3	Hammer	3	12/3/2012
6	2	Cement	2	12/3/2012
7	1	Iron sheet	20	20/3/2012
8	4	Cement	15	20/3/2012
9	3	Crown paint	8	21/3/2012
10	3	Barbed wire	1	21/3/2012

 

5. Create a query called datequery to extract all those items bought after 3^rd of march 2012, displaying the customer name, product name, and date of order fields.                                                       (3 Marks)
6. Create another query called Cementqueryto extract all those customers who bought cement in March 2012 and create a field showing how many bags of cement each customer had bought. (3 Marks)
7. Create a grouped report per customer called customerreportdisplaying the product, dateoforder, quantity and price.                                                                    (5 Marks)
8. Modify customerreport by doing the following.
1. Create a text box with label Ttlperordernext to price and write a function to compute total amount per order.                                                                    (3 Marks)
2. Compute the subtotal amount each customer has to pay for his orders for the whole month with a label subtotal.                                                                      (3 Marks)
3. Compute the Grand Total for the sales that Bima hardware has made the whole month with a label GrandTotal.                                                               (3 Marks)
4. Insert a header BIMA HARDWARE MARCH SALES in the report having font size 20 and center it across the page.                                                                    (3 Marks)
9. Print the following.                                                                                         (5 Marks)
1. Customertable, Ordertable, Producttable.
2. Datequery, cementquery.
3. Customerreport.

 

Exercise 14

1.      The information below was extracted from Masita vehicle selling business

Buyer Name	Buyer Address	Buyer Town	Vehicle Reg NO	Vehicle Type	Vehicle Make	Vehicle price	Buyer Number	Amount paid
Peter	254	Nakuru	KAJ 001	Matatu	Nissan	1200000	B001	800000
John	678	Eldoret	KAJ 002	Bus	Mazda	2400000	B002	2000000
Ken	963	Nairobi	KAJ 003	Saloon	Toyota	800000	B003	600000
Peter	147	Nakuru	KAJ 004	Pick up	Peugeot	1000000	B004	700000
Roy	456	Bungoma	KAJ 005	Lorry	Isuzu	3000000	B005	2000000
Glen	789	Webuye	KAJ 006	Pick up	Toyota	1800000	B006	1600000
John	678	Eldoret	KAJ 007	Bus	Scania	7500000	B002	7500000
Ken	963	Nairobi	KAJ 008	Matatu	Toyota	1300000	B003	1300000
Phillip	159	Kisumu	KAJ 009	Saloon	Nissan	900000	B007	900000
Peter	254	Nakuru	KAJ 010	Pick up	Isuzu	1500000	B001	1200000
Ken	357	Kisumu	KAJ 011	Saloon	Peugeot	700000	B008	700000
Glen	789	Webuye	KAJ 012	Bus	Isuzu	10000000	B006	9500000
Peter	147	Nakuru	KAJ 013	Matatu	Nissan	2700000	B004	2700000

(a)    Create a database file named Masita Vehicles(2 marks)

(b)   Using the information in the table, create a table to hold vehicle detail and another to hold buyer details. Name them tblvehicle and tblbuyer respectively  (4 marks)

(c)    Choose the most appropriate data types and enforce referential integrity between two tables. (2 marks)

(d)   Create different input screen with navigation buttons for each table, giving them appropriate title. Name them frmvehicle and frmbuyer. Use them to enter data into the tables. (6 marks)

(e)    Make a table with name tblPayment. The payment table should contain the following fields

BuyerName, Vehicle Reg NO, Vehicle Price, Amount Paid and Balance (6marks)

(f)    Display a report only showing the details of the buyers who have cleared paying for the vehicle. Name the report rptcleared with ‘’CLEARED BUYERS’’ as the title of the report. (6marks)

(g)   Masita has a discount policy of 5% offered to buyers who make payment in full. Display a report showing the details of buyers who benefited from this policy and the amount of money they paid after the deduction. Save the report as rptdiscount(3mks)

(h)   Using the two tables create an outlined report showing the customer details, the total amount paid by each customer and the total amount received by CMC during this time. Name the report rptnilbal and the title as ‘SUMMARY REPORT PER BUYER.’’(5 marks)

(i)     Create a query to display the vehicle details with balances of less than 500,000 but not less than 300,000. Name the query as qrymidbal.(7marks)

(j)     Create a report showing the vehicle type, the total sales for each type and the grand total.(3 marks)

(k)   Set frmvehicle as a startup feature in the database (2marks)

(l)     Protect your database with a password  masita (2marks)

(m) Print  tblpayment,rptcleared, and rptnilbal and qrymidballandscape orientation with footers being your last name and index number at the centre of the page (2 marks)

Exercise 15

A firm keeps its details in a computer database.  The information below contains details obtained from two tables of the database.  Study the tables and answer the questions that follow.

 

Employees table

 

EmployeeID	EmployeeName	Department	Job Title	Salary
7369	Mark Koech	Research	Clerk	48000
7499	Philip Meme	Sales	Salesman	16000
7521	Mohamed Ali	Sales	Salesman	12500
7566	Kennedy Simiyu	Research	Manager	39750
7698	David Kamau	Operations	Manager	38500
7782	Titus Ole Simian	Accounting	Manager	34500
7788	John Onyango	Operations	Analyst	30000
7821	Patel Shah	Operations	Analyst	25000

 

            Department Table

 

DeptCode	Department	Location
10	Accounting	Nairobi
20	Research	Nakuru
30	Sales & Marketing	Mombasa
40	Operations	Kisumu

 

            Required:

(a)                Create a database that can be used to store the above data and save it as

MACAL in the disk provided.                                                                      (10mks)

 

(b)               Using appropriate primary and foreign keys create a relationship between

the two tables.  Enforce referential integrity between the tables.      (4mks)

 

(c)                Validate the primary key entry to exactly four and two characters for the

EmployeeID and DeptCode fields respectively.                                                        (4mks)

 

(d)               Create a form for each table and use it to enter the records shown in the

tables above.  Save the forms as EmployForm and DepartForm

respectively.                                                                                                    (6mks)

 

(e)                It is required that the dates on which the employees were hired be included

in the database.  Koech was hired on 10/06/1998.  Meme on 15/08/1996. 

Mohamed on 16/03/1996, Onyango on 09/03/2003, the rest were hired on

13/03/2004.  Insert a new field, name it Date of Hire in the Employees

table and enter the field.                                                                                 (5mks)

 

(f)                Create a query that displays employees who were employed after year

2000, save the query as LatestEmployees.                                        (4mks)

 

(g)               Create, a, Report that displays the Employee Name, Job title Department

name and Salary, grouped according to location.  

Save the report as EmployeeReport.                                                   (4mks)

 

(h)        (a)        Create a query to display the employees and their job description.

                                    Save it as EMPTYPE.                                                             (4mks)

 

(b)               Create a pie chart based on the query in h(a) above to display the

proportions of employees in various job descriptions. 

Save the report as CHART.                                                                (4mks)

 

            (i)         Print:

                        (i)         Employees and Department table designs.

                        (ii)        Employee and Department forms.

(iii)             LatestEmployees Query.

(iv)             EmployeesReport.

(v)               The Chart

 

Exercise 16

            The data in the table was extracted from a survey data on employment.

 

                                                Table 1: EMPLOYEE TABLE

Name	Year of birth	Employee ID NO.	Employer ID	Job category
LYDIAH	1980	13144	01	GK4
SAMMY	1970	11100	04	GK3
GITONGA	1984	14010	02	GK1
SANDRA	1976	12110	05	GK1
GACHORE	1973	11410	03	GK2
PAULINE	1968	10570	04	GK3
MIRIAM	1990	11040	05	GK3
ELIAKIM	1998	15978	03	GK2
BECKY	1992	17192	02	GK4
OSEWE	1993	18965	05	GK4

 

                                    Table 2: EMPLOYMENT TYPE                           

Job Category	Job Description
GK1	Casual
GK2	Temporary
GK3	Contract
GK4	Permanent

                                   

Table 3: EMPLOYER TABLE

EMPLOYER ID	EMPLOYER NAME
01	ONYANGO
02	WAMBUA
03	OSHIRO
04	KATANA
05	AWINJA

 

            a)         i) Create a database named “STAFF” to store the above                                    (14mks)

                        ii) Create relationships between the tables                          (4 ½ mks)

                        iii) Use forms to enter data into the tables                                      (10 ½ mks)

b)         i) Generate a report to display the name year of birth, age and employer’s name for the employees who will be over 30years old by the year 2015                                   (10mks)

            ii) Compute the mean age of employees on the report you created in b(i) above.  (2mks)

c)         i) Create a query to display the employees and their job description. Save the query as “STAFF TYPE”                                                                        (3mks)

            ii) Create a pie chart based on the query in c(i) above to display the proportion of employees in various job description.

            Save the report as CHART

d)         Print    i)          Three tables

                        ii)         Two reports

                        iii)        Output of query results for STAFF TYPE.

 

Read More