Friday, 20 March 2015

Ms Access Relationships Exercises 3



Ms Access Relationships Exercises 3

1.      Create a database called Mololine express.
2.      Create the following tables in the database. (Primary Keys are Underlined)
a)      Drivers
Fields                    Data type                   
Drivers_ID            Number(Long Integer)
FirstName             Text(20)                     
LastName              Text(20)                     
Address                 Text(50)                     
Date_Of_Birth      Datetime
Hire_Date             Date/Time
Salary                    Currency (General Number)

b)      Vehicles
Fields                    Data type
Vehicle_ID           Number(Long Integer)
Reg_No                 Text(8)                       
Capacity                Number (Long Integer)
Make                     Text(50)                     
Type                      Text(50)                     
Date_Acquired     Date/Time                  

c)      Shifts
Fields                    Data type                   
Trip_No                Number(Long Integer)
Drivers_ID            Number (Long Integer)
Vehicle_ID           Number (Long Integer)
Route_No             Text(5)
Shift_Name           Text(50)

d)     Routes
Fields                    Data type
Route_No             Text(5)
Distance                Integer                        
Fare                       Currency
Destination            Text(50)                     
3.       Enter the following records in the tables.
Table: Drivers
Drivers_ID
Last Name
First Name
Birth Date
Hire Date
Address
1
Aketch
Nancy
12/8/1948
5/1/1992
P.O. Box 4501 Kisumu
2
Mutunga
Andrew
2/19/1952
8/14/1992
P.O. Box 6733 Mombasa
3
Kasavuli
Janet
8/30/1963
4/1/1992
P.O. Box 6747 Maragoli
4
Thatcher
Margaret
9/19/1937
5/3/1993
P.O. Box 35667 Nairobi
5
Mulumba
Steven
3/4/1955
10/17/1993
P.O. Box 644 Bungoma
6
Wamalwa
Michael
7/2/1963
10/17/1993
P.O. Box 876 Bungoma
7
Koskei
Robert
5/29/1960
1/2/1994
P.O. Box 260 Litein
8
Chepkurui
Laura
1/9/1958
3/5/1994
P.O. Box 8955 Nakuru
9
Kitui
Anne
1/27/1966
11/15/1994
P.O. Box 754 Bungoma
10
Mutuku
Caroline
3/5/1973
1/11/2000
P.O. Box 1342 00400 Nairobi
11
Nyambane
Walter
12/6/1975
6/15/1999
P.O. Box 7803 Kitui
12
Wasike
Levy
6/13/1978
8/4/2003
P.O. Box 11789 00400 Nairobi

Drivers are paid a uniform salary of Sh. 12000/-. Update your table to reflect these details.
Table: Routes
Route NO
Distance(Kilometers)
Fare(Ksh.)
Destination
305A
384
600.00
Nyeri
305D
288
450.00
Muranga
290B
589
550.00
Kakamega
290A
512
550.00
Kisumu
514B
189
300.00
Nakuru
519
487
400.00
Kitale
514A
350
400.00
Eldoret
609A
615
500.00
Mombasa
609B
710
800.00
Malindi
609C
542
500.00
Voi
1011A
1241
1500.00
Kampala
1011B
1005
1500.00
Jinja
1012A
950
1000.00
Mbale
2011
1600
1800.00
Dar-e-salaam







Table: Vehicles
Vehicle_ID
Reg Number
Make
Capacity
Type
Date_Acquired
1          
KAP 893J
SCANIA 310HP
67
BUS
1/25/2003
2          
KAK 788K
ISUZU
62
BUS
5/11/2000
3          
KAD 655A
ISUZU
62
BUS
6/12/1999
4          
KAR 700H
SCANIA 310HP
72
BUS
2/25/2004
5          
KYW 415
NISSAN DIESEL
55
BUS
6/11/1990
6          
KAP 644M
TOYOTA HIACE
14
VAN
9/27/2003
7          
KAQ 255G
TOYOTA HIACE
14
VAN
5/8/2001
8          
KAR 214P
ISUZU INTERCOOLER
70
BUS
4/2/2004
9          
KAL 579C
ISUZU
60
BUS
5/13/2000
10       
KAQ 192V
MITSUBISHI CANTER
25
MINI BUS
9/25/2001
11       
KAN 234U
MITSUBISHI CANTER
25
MINI BUS
4/8/2002
12       
KAP 562L
PEUGEOT 505GL
8
VAN
12/18/2003
13       
KAQ 894W
PEUGEOT 505GL
8
VAN
1/16/2003
14       
KAP 012L
SCANIA 310HP
70
BUS
4/6/2003
15       
KAQ 331K
ISUZU INTERCOOLER
70
BUS
5/14/2003
16       
KAR 444P
SCANIA 310P
72
BUS
2/2/2004

Table: Shifts
Drivers are assigned the following shifts schedules. Add this information in the Shifts table.
Drivers_ID
Route_No
Shift
Vehicle_ID
1
290A
DAY
2
5
519
NIGHT
3
8
514B
AFTERNOON
6
3
514A
MORNING
7
7
1011A
NIGHT
1
10
305D
DAY
5
6
290B
NIGHT
4
4
609C
DAY
9
11
609A
NIGHT
14
2
609B
NIGHT
15
9
1012A
DAY
16

Add more drivers to the shifts table for your practice.


Setting table properties



Empno
EDUC
JOBCAT
SALARY
SALBEGIN
PREVEXP  

Gender

1
15
Custodial
57000
27000
144
M
2
16
Manager
40200
18750
36
F
3
16
Custodial
103750
27510
70
F
4
12
Manager
42300
14250
103
M
5
12
Manager
26250
11550
48
M
6
16
Manager
38850
15000
17
M
7
12
Manager
21750
12750
315
M
8
15
Manager
24000
11100
75
F
9
12
Manager
16950
9000
124
F
10
15
Manager
21150
9000
171
F
11
15
Manager
31050
12600
14
F
12
15
Manager
41100
20250
27
M
13
17
Custodial
82500
34980
207
F
14
16
Manager
54000
18000
11
M
15
12
Manager
26400
10500
0
F
16
12
Manager
24300
10950
5
M
17
12
Manager
24750
14250
193
F
18
12
Manager
22950
11250
0
M
19
8
Manager
25050
10950
8
M


















Required
Using Create table by entering data, make sure you give your fields appropriate data types
Restrict the Field gender so that they only take one character
Then validate the SALBEGIN so that it takes only a figure that is below 20,000
For the PREEXP and EDUC restrict them so that they take two numbers only.

 Relationships1

1.      Create three tables with the following fields as shown in the relationship window.
 2.      Implement the relationships
 3.      Create reports to display the total cost of tickets collected

Relationships2

  1. Create a database and name it First Database
  2. In this database create three tables as follows: -
v  Table1 should have the following fields Student Id, Student name, Course Id and Tutor. Make student Id the primary key and save it as student details                        
v  Table 2 should have the fields Course Id, course name, Student id and tutor. Make course id to be the primary key and save this table as courses
v  Table 3 is to have the following field tutor, course id and student id. Make tutor the primary key and save this table as tutors.
  1. Create appropriate relationships between the three tables.


Importing and Linking tables

  1. Create a database and give it the name exercise1.

  1. In this database create a table with the fields Course name, Course tutor, Number Of students, Time taught. Save it as practice table.

  1. Make the field to have the following properties: -

    • Course name                        Field size of 40
    • Course tutor                          Field size of 45
    • Number Of students            -Field size of 40
    • Time taught                          -Input mask to be long time

  1. Enter 10 records in this table

  1. Add the following field to this table Comments. For this field make the data type to be text and it should have the description “How the course was performed”

  1. Make the table have the format “comic sans ms”, color “Red”, size “14”, underline.

  1. Create another database with the name exercise2.

  1. In this second database create a table with field of your choice. Save it with the name practice2 table

  1. Import the table practice from exercise database to your first database

  1. Create a third database and name it exercise3. Create a table in this database and name it practise3 table.

  1. Link the table practise2 to database exercise1

  1. Import the table practise3 to the database exercise2

 Select Queries1


Create a database to hold the information in the table below.

1.      Use QUEURIES  to determine
(a)  The number of people with a basic salary greater than 20,000
(b)  The number of people with a basic salary less than 30,000
(c)   The number of people with a basic pay greater then or equal to 17,000 and less than or equal to 30,000
(d)  All the employees in each department
(e)  All employees from research department
(f)   All employees from finance department
(g)  All employees from computer or finance department
(h) All employees having the first character of the name as J
(i)   All employees with the name ending with s
(j)    All employees from the computer department and have age greater than 30
(k)  All employees who were employed on or before 10/11/80.


Action Queries1

Name
Windows 98
Ms word
Ms excel
Joseph
56
67
87
Andrea
67
54
34
Beatrice
87
56
43
Neddy
23
45
67
Dorah
68
98
69
Titus
45
65
64
Ayub
49
48
59
Carol
87
98
58
Catherine
45
67
65

From the above table create queries to do the following: -
  • Delete the records of neddy
  • Increase by 2 the windows marks
  • Change the names of people starting with c to be mercy
  • Make another table using the fields name and ms excel
  • Using a query add the total marks for the student and rename this column Total Marks.


Action queries 2

Create the following table
Name
Age
Height
Weight
Country
Ann
23
1.45
60
Kenya
Beatrice
45
1.23
67
Uganda
Andrew
20
1.89
89
Kenya
Brenda
45
1.20
56
Tanzania
Cate
12
.89
20
Kenya
Joe
40
1.50
56
Uganda
Jane
45
2.3
67
Kenya
Karen
26
2.0
40
America

Using the above table create queries to do the following: -
·         Delete all the records of people whose names start with A
·         Update the name of Karen to be Joan
·         Increase all the ages by 5
·         All people who come from Kenya
·         Make another table having the fields name, Age and Country in the same database from this table. Let the name of this table be made table
·         Show all people whose names ends with e or country is Kenya
·         All people who come from America
·         All who have an height greater or equal to 1.50
·         All whose height is less than 60
·         All people who don’t come from Uganda


APPEND QUERIES

  1. Create the following table in access
Name                                     Age                                         Country
Ann                                        21                                           Kenya                   
Ann                                        45                                           Uganda                                                 
Alice                                       24                                           Tanzania
Joel                                         56                                           America
Susan                                     12                                           Kenya
Dorcas                                   22                                           Kenya
Doreen                                   45                                           Tanzania

Save this table by using the name MyTable1

2. Create another table in the same database with the following field

Name
Age
Country
Ahmed
30
Dubai
Kamlesh
40
India
Sadam
48
Afghanistan
Diana
15
Australia
Jacko
45
Spain

Save this table by using the name MyTable2

Required

Append the details of MyTable1 to those of MyTable2

Calculation Queries

  1. Create a table in access which has the following fields: -
Student Number, Student Name, Ms Windows, Ms Word, Ms Excel, Ms Access, Ms PowerPoint, VB Programming.

  1. Make Student Number the primary key

  1. Enter 10 records of your choice into this table

  1. Using a query calculate the total marks of the students and let this column have the name Total marks.

Calculation Queries2

1.       Create a table in which has the following columns: -
a.       Student Number, Student Name, Ms Windows, Ms Word, Ms Excel, Ms Access, Ms PowerPoint, VB Programming.


2.       Enter 10 records of your choice into this table

3.       Calculate the total marks of the students and let this column have the name Total marks.

Cross tab queries

  1. Create the following table by use of Microsoft Access

Customer Name
Item
Value(Ksh)
Bobby
Cement
1000
Green
Cement
1500
Jacob
Cement
2000
Bobby
Sand
3500
Bobby
Nails
1000
Jacob
Sand
4000
Green
Nails
500
Green
Sand
3000
Jacob
Nails
2500

Using the above table create an appropriate cross tab query



Forms and Reports.

  1. In your current database create the following table and save it using the name vehicles: -

Car Number
Owner
Color
Position
1
Ahmed
Red
5
2
Susan
Green
1
3
Rose
Orange
7
4
Ted
White
2
5
Diana
Green
8
6
Denis
Blue
9
7
Juma
Red
3
8
Alice
Blue
10
9
Faith
White
4
10
Ken
Yellow
6

Required

  1. Based on this table create the following queries and save them using appropriate names: -
    • All green cars
    • All owners whose names start with D
    • All car numbers greater or equal to 5
    • Cars whose position is less or equal to 5

  1. Create individual forms for the table vehicles and all the queries you created in 2 above. For each of the forms do the following: -
    • Add an appropriate form header and form footer
    • Insert page numbers
    • Insert an object from Microsoft Clip gallery
    • Style them to the blends style
  1. For the vehicles form display the following by use of wizards: -
    • Combo box
    • List box
    • Command button
  2. Create reports for all the tables and queries.




Forms and Reports2

Create the table below and use it to answer the questions that follow.

Personnel Department

NAME
BASIC PAY
DEPARTMENT

AGE

STATUS
Peter
15000
Computer
34
Single
John
17000
Computer
44
Married
Kamau
19000
Finance
33
Divorced
Charles
21000
Research
33
Single
Johns
23000
Research
25
Single
Thomas
25000
Computer
26
Married
Ann
27000
Finance
28
Married
Jane
29000
Finance
29
Divorced
Susan
31000
Finance
24
Divorced
Tina
33000
Research
40
Single
Drew
35000
Computer
20
Married
Hardy
37000
Finance
43
Single
Njeri
10000
Finance
15
Single
Kamau
15000
Computer
35
Divorced
Silantoi
35000
Finance
25
Married
Tina
59000
Computer
33
Single
Moses
70000
Research
56
Divorced
Miriam
32876
Finance
70
Single
Maurice
43876
Computer
98
Divorced
Alphie
48098
Research
32
Single
Albert
6500
Research
12
Divorced
Langat
39000
Computer
70
Single

1.      Create queries to determine: -
§  The number of people with a basic salary greater than 32,000
§  The number of people with a basic salary less than 45,000
§  The number of people who are either married or single.
§   The number of people with a basic pay greater then or equal to 25,000 and less than or equal to 50,000
2. Create forms for the table and for all the queries made in 1 above
3. Create reports for the table and all the queries made
4. Format the forms and the reports accordingly.

Propellerads