Brooms 8 yes 90 rake 9 yes 23 bucket e design query

BROOMS
8 Yes 90 RAKE
9 yes 23 BUCKET
e) Design query that would display the following fields as shown below and write down the formulae
for getting the total score and criteria for extracting the records below (10marks)
Admin
Admission
number
uniform S Name KCPE
Mark
maths English Kiswahili TOTAL
SCORE

1 Yes chepkuto 250 89 90 90 269
10 Yes wekesa 450 78 9 90 177
2 Yes Jane 400 67 90 7 164
f) Design a report that would sort the following in ascending order of the following fields, total
score, and KCPE score, name the admission number and the report should display all the fields. Save
the report as administration (5mks)
g) Print, administration and admin (2mks)
*RCH*
Question 1.
Ms – Access Database
(a) Create a database file called VIDEOS and use it to enter the details that follow. (3 mks)
(b) Create a table called Customer using the following structure. (4 mks)
Field Name Data type Size Description Caption
Customer ID Number 3 3 – digit unique number Customer ID
Last Name Text 25 Enter the Last Name Last Name
First Name Text 15 Enter First Name First Name
Address Text 50 Address Address
Birth Date data/time Enter yyyy/mm/dd Date of birth
Home Tel Text 14 Home Tel. Home Tel.
Work Tel Text 14 Work Tel. Work Tel.
Email Text 40 Email Address Email Address
Gender Place Text 1 M or F Gender
Work Place Text 40 Place of employment Place of employment
(i) Make the Customer ID Field the primary key. (1 mk)
(ii) Format the Last Name and First Name fields to convert characters to upper case by using >.
(iii) Enter Yes in the required property of the Last Name, First Name and Gender fields. (1 mk)
(iv) Format the Birth Date field yyyy.mm.dd to input the birth dates ,year, month abd day.(1 mk)
(v) Use the input mask for the Home Telephone and work Telephone fields. The input mask should
be (999) 999 – 999.
(vi) Format the Gender field to convert the character to upper case using >. (1 mk)
(vii) Enter the M or F for the Validation rule field of the gender Field. (1 mk)
(viii) Enter the Only valid entries are M or F in the validation Text of the Gender Field.(1 mk)
(ix) Enter Yes in the required property of the gender field. (1 mk)
(x) Save the tables as Customer. (1 mk)
Custo
mer ID Last
Name
First
Name
Address Birth
Date
Home
Tel.
Wor
k
Tel.
Email
Gende
r
Work
place
(245) Bankers

201 JOHNSO
N
MICHEL
E
RUBY
PARK, ST
PHILIP
1969/02/
15
431-
2916
(246
)_
2196
[email protected]
b.com
F Night
Club
202 CHAND
LER
TRYONE BANK
HALL, ST.
MICHAEL
1969/03/
05
(246)
431-
8971
(246
)
422-
1119
M
GE
Electron
ics
203 BANFIE
LD
SHIRLE
Y
71 LONG
BAY
DEVELOPM
ENT
1970/10/
26
(246)
5441 [email protected]
com
F
204 ISIDORE EYSTHE
R
4MT
STANDFAS
T, ST.
JAMES
1968/11/
10
(246)
433 –
9172
(246
)
422
–
1999
F
Queen
Elizabet
h
Hospital
205 CONNE
L
WINSTO
N
CANE
GARDEN,
ST.
THOMAS
1971/12/
10
(246)
433 –
9172
(246
)
417-
4177
[email protected]
ail.com
M
Transpo
rt
Board
206 KELLM
AN
ARLENE ST.
PATRIICKS,
CHRIST
CHURCH
1974/06/
10
(246)
421 –
2031
(246
) 426
–
2979
[email protected]
ail.com
F
KMGP
207 PAYNE REBA FOSTER,
HALL, ST.
GEORGE
1967/01/
03
(246)
423-
6823
(246
) 423
–
9741
[email protected]
beach.com
F
Core
Crystal
Hotel
208 GRANT FREDER
IC H
GRAPEHAL
L,
ST. LUCY
1962/09/
09
(246)
417 –
2280
M
209 SMITH IRENE ASHTON
HALL, ST.