Monday, 27 April 2015

Ms Access Practice Exercises




Question 1
Create a database to hold the information in the table below. Name it “Cornerstone”. Create a table in your database cornerstone with the following structure and call it IT
Name
Basic pay
Department
Age
Employment date
Peter
15000
Computer
34
12/12/80
John
17000
Computer
44
11/11/80
Kamau
19000
Finance
33
9/10/80
Kioko
21000
Research
33
8/10/80
Mutiso
23000
Research
25
7/10/80
Jason
25000
Computer
26
6/9/80
Marx
27000
Finance
28
5/9/80
Jane
29000
Finance
29
4/8/80
Susan
31000
Finance
24
3/8/80

Use QUERIES 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 salary greater than or equal to 17,000 and less than or equal to 30,000
d)      All the employees from research department
e)      All the employees from finance department
f)       All the employees from computer or finance department
g)      All employees having the first character of the name as J
h)      All employees with the name ending with S
i)        All the employees from the computer department and have age greater than 30
j)        All employees who were employed on or before 10/11/80

Question 2
Name
Windows 98
Ms word
Ms Excel
Joseph
56
67
87
Andrea
67
54
34
Beatrice
87
565
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:
a)      Delete the record of Neddy
b)      Increase by 2 the Windows marks
c)       Change the names of people starting with ” C” to Mercy
d)      Make another table using the fields name and Ms Excel
e)      Using a query add the total marks for the student and rename this column Total Marks.
Question 3
Create the following table and save it as people
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
0.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 the people whose names start with A
     Update the name 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 “MAKE”
      Show all people whose names end with “e” or country is Kenya
      All people who come from America
     All who have an height greater than or equal to 1.50
    All whose height is less than 60     
   All whose third character of the name is “e”
    All people who don’t come from Uganda.
Question 4
a  Create forms for all the tables above. Add an appropriate title to each, date and time  
b Create report for all the tables. Format it accordingly.
Question 5
Create the following table and save as Students

S/No            NAME             PAPER           RESULTS       COLLECTED     COMMENT

132               JOHN                      Pw1               Pass           Y                  Next class
145              MIKE                       Ps2                Pass           n                 Next class
36                PETER                     Ps3               Fail             n                To repeat
566              TOM                       Pw2               Pass            y                 No fees
72                JACK                     Pw1            Pass             n                No fees
85                PETER                   Pg7             Pass             n                 Next class
430              MARK                     Pm6             Fail              y                 Expelled
646              STEPHEN                Po3              Fail              n                Last chance
450               MARY                     Pm5              Fail             y                 Last chance
290               GEORGE                 Pd4              Pass            Y                 Next class
320               PAUL                      DW5          Pass              Y                 Next class
430              JANE                       Pm6           Fail             N                   Expelled
  1. Display students who passed and haven’t collected their results
  2. Students who sat for Pw1 and passed
  3. All students who failed
  4. Students who have passed and have been promoted to the next class
  5. Students who passed and have no fees to continue
  6. Students who failed and have either been expelled or have repeated
  7. Students whose names start with the letter P
  8. Students whose names end with the letter E
Propellerads