Entoto TVET College Training, Teaching and Learning Materials Development yes
ENTOTO TVET COLLEGE under
Ethiopian TVET-System INFORMTION TEC!NOLO"# $%&&ORT $ERVICE Level I
LEARNING GUIDE # 1 %nit o' Competence( Competence ( Module Title ( L" Code ( TTLM Code (
Operate Data)ase pplication Operating Data)ase pplication ICT IT$* M+ L+ + ICT IT$* TTLM+ +-
LO ( Create data)ase o).ects
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 1 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
INTRODUCTION
Learning Guide # 1
This learning guide is developed to provide 3ou the necessar3 in'ormation regarding the 'ollo4ing content coverage and topics 2
Opening and designing D5 application and principles Creating D5 o).ect ta)le Creating D5 o).ect 'orm, 6uer3 and report Creating D5 o).ect macros, module 7 data access page
This guide 4ill also assist 3ou to attain the learning outcome stated in the cover page1 $peci'icall3, upon completion o' this Learning "uide, 3ou 4ill )e a)le to 2
Open and design data)ase application incorporating )asic design principles design principles Create data)ase o).ect according to data)ase usage, as 4ell as user re6uirements Modi'3 data)ase o).ect as re6uired dd and modi'3 data in a ta)le according to in'ormation re6uirements dd, modi'3 and delete records as re6uired $ave and compile data)ase o).ects
Learning Activities
1 *1 :1 01
Read the speci'ic speci'ic o).ectives o' this Learning "uide1 "uide1 Read the in'ormation 4ritten in the 8In'ormation 8In'ormation $heets 9 in pages :/-1 ccomplish the the 8$el'/chec;9 8$el'/chec;9 in pages pages <1 I' 3ou earned a satis'actor3 satis'actor3 evaluation evaluation proceed to 8In'ormation 8In'ormation $heet *91 !o4ever, i' 3our rating is unsatis'actor3, unsatis'actor3, see 3our teacher 'or 'urther instructions or go )ac; to Learning ctivit3 = 1 -1 Read the in'ormation 4ritten in the 8In'ormation 8In'ormation $heet *9 in pages >/+1 <1 ccomplish the the 8$el'/chec;9 8$el'/chec;9 in page page 1 >1 I' 3ou earned a satis'actor3 satis'actor3 evaluation evaluation proceed to 8In'ormation 8In'ormation $heet :91 !o4ever, i' 3our rating is unsatis'actor3, unsatis'actor3, see 3our teacher 'or 'urther instructions or go )ac; to Learning ctivit3 = *1 ?1 Read the in'ormation 4ritten in the 8In'ormation 8In'ormation $heet :9 in pages */-1 @1 ccomplish the the 8$el'/chec;9 8$el'/chec;9 in page page <1 +1 I' 3ou earned a satis'actor3 satis'actor3 evaluation evaluation proceed to 8In'ormation 8In'ormation $heet 091 !o4ever, i' 3our rating rating is unsatis'actor3, unsatis'actor3, see 3our teacher 'or 'urther instructions or go )ac; to Learning ctivit3 = :1 1 Read the in'ormation in'ormation 4ritten in the 8In'ormation 8In'ormation $heet 09 in pages >/*1 *1 ccomplish the 8$el'/chec;9 8$el'/chec;9 in page page **1 :1 I' 3ou earned a satis'actor3 satis'actor3 evaluation evaluation proceed to 8Operation $heet $heet />9 in page *:/0:1 !o4ever, i' 3our rating is unsatis'actor3, see 3our teacher 'or 'urther instructions or go )ac; to Learning ctivit3 =01 01 Read and per'orm per'orm each o' the 8Operation $heets91 -1 Do the 8L& test9 on page 00 Ai' 3ou are read3B and sho4 3our output output to 3our teacher1 #our teacher 4ill evaluate 3our output either satis'actor3 or unsatis'actor31 I' unsatis'actor3, 3our teacher shall advice 3ou on additional 4or;1 5ut i' satis'actor3 3ou can proceed to Learning "uide *1
#our teacher 4ill evaluate 3our output either satis'actor3 or unsatis'actor31 I' unsatis'actor3, 3our teacher shall advice 3ou on additional 4or;1 5ut i' satis'actor3 3ou can proceed to the net topic1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 2 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Inforation !"eet 1
Oening and designing D$ a%ication and rinci%es
Data&ase
data)ase can )est )e descri)ed as a 4a3 o' storing large amounts o' in'ormation1 The data can )e retrieved and 4e can even as; 6uestions o' the data and get ans4ers1 ans4ers1 For eample( #ou ma3 4ant to ;no4 ho4 man3 $tudents enrolled in ever3 occupational occupational level1 's Access is a data)ase management management tool that ena)les one to store relevant data1
This This also also has the capa) capa)ilit ilities ies to retrie retrieve, ve, sort, sort, summ summari arie e and and repo report rt results imme immedi diat atel el3 3 and and e''e e''ect ctiv ivel el31 31 It can can com) com)in ine e data data 'rom 'rom vari variou ous s 'ile 'iles s (tables) throu through gh creat creating ing relati relations onship hips, s, and can ma;e ma;e data data entr3 entr3 more more e''ic e''icien ientt and accurate through the use o' forms1 'icrosoft Access ('! Access) ena)les one to manage all important in'ormation 'rom a
single data)ase 'ile1 ithin the 'ile, one one can use the di''erent di''erent o).ects( o).ects(
Ta&% Ta&%es es * ta)le ta)le is a colle collecti ction on o' data data a)out a)out a spec speci'i i'ic c topic, topic, such as produc products ts or
suppliers1 %sing a separate ta)le 'or each topic means that 3ou store that data onl3 once1 This results in a more e''icient data)ase and 'e4er data/entr3 errors1 +ueries * #ou use 6ueries to vie4, change, and anal3e data in di''erent 4a3s1 #ou can also use them as a source o' records 'or 'orms, reports, and data access pages1 pages 1 ,ors * 'orm is a t3pe o' a data)ase o).ect that is primaril3 used to enter or displa3
data in a data)ase1 #ou can also use a 'orm as a s4itch)oard that opens other 'orms and reports in the data)ase, or as a custom dialog )o that accepts user input and carries out an action )ased on the input1
Reorts * report is an e''ective 4a3 to present 3our data in a printed 'ormat1 5ecause
3ou have control over the sie and appearance o' ever3thing on a report, 3ou can displa3 the in'ormation the 4a3 3ou 4ant to see it 'odu%es * modu module le is esse essent ntia iall ll3 3 a coll collec ecti tion on o' de decl clar arat atio ions ns,, stat statem emen ents ts,, and and procedur procedures es stored together together as one named unit to organie organie 3our Microso't Visual 5asic code1 Microso't ccess has t4o t3pes o' modules( standard modules and modules and class modules1 modules1
Data Data access access ages ages * data access page is a special t3pe o' e) page designed 'or
vie4ing and 4or;ing 4ith data 'rom the Internet or an intranet data that is stored in a Microso't ccess data)ase or data)ase or a Microso't Microso't $GL $erver data)ase1 data)ase1 The data access page ma3 also include data 'rom other sources, such as Microso't Ecel1
In M$ ccess, data is stored once in one ta)le, )ut can )e vie4ed 'rom multiple locations1 hen the data is updated in a Ta)le, Guer3 or Form, it is automaticall3 updated ever34here it appears1
Establishment of Ms Access database Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age - o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
ll Ms ccess data)ases 'iles are saved 4ith etension .d&. data)ase should have a separate ta)le 'or ever3 ma.or su).ect, such as Students records, Students grades, etc..
Production data or Treatment information1 Data should not )e duplicated in multiple
ta)les1 Microso't ccess provides three methods to create a data)ase
Data&ase /i0ard Athough eas3, the 4iard o''ers limited options to customie the
data)aseB %sing a te%ate AThis method 4or;s )est i' one can 'ind and use a template that closel3 matches the speci'ic re6uirementsB Creating a data&ase direct% AT"is is t"e ost f%ei&%e et"od3 )ut it re6uires one to de'ine each data)ase element separatel3B1
$asic Data&ase Concet ( In stud3ing M$ ccess, it is )ut necessar3 necessar3 to understand some )asic
elements o' a data)ase )e'ore proceeding to it1 Data&ase E%eents4 Data are ra5 facts1 It tells the truth a)out a)out somethingH somethingH a person, a place, an object, etc 1
Eample( AB 8Noel” is a data. “Noel” is my name, so
Nae
Noel
it tells something something about is a Name
Gender
(2) “Male” “Male” is Noel’s Noel’s gender. So it tells tells something about about “Noel”. “Noel”. Noel is Male. Male.
Male
a person. person. “Noel”
Inforation is a collection o' data (ra !acts) hich is contained in " !ile (table in #ccess) Example: IDNu&er LNae ,Nae 'nae Gender $da Address
MOE/+++
Cuevas
Noel
&ancho
Male
/+-/@>?
rat;ilo
(this is an in!ormation about a person named “Noel”)
Example of a table (file): IDNu&er LNae MOE/+++ Cuevas MOE/+++* &angani)an MOE/+++: Cerna MOE/+++0 !ipolito MOE/+++Ohm3
Learning "uide st Re Revision
,Nae
'nae
Gender
$da
Address
Noel Mar; &atric; Valder Diana
&ancho &ere $antos Cru ntonio
Male Male Male Male Female
/+-/@>? /+-/@>? /+-/@>? /+-/@>? /+-/@>?
rat;ilo &iasa rat;ilo $idis;ilo rat;ilo
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 6 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Naing Convention
Is manner o' naming naming 'iles and varia)les1 !aving a poor naming naming convention can onl3 onl3 add con'usion, so itJs important that 3ou start 4ith a good scheme, and thin; a)out 4hat the scheme 4ill mean to 3ou1 !ere are the three things to consider in esta)lishing esta)lishi ng a naming naming convention( 1 Does m3 convention ma;e sense to meK Is must )e simple and understanda)le to 3ou1 *1 ill m3 convention ma;e sense to other peopleK Time 4ill come that other people 4ill loo; into 3our data)ase program, so the3 must a)le to understand it1 :1 Can I )e consistent in implementing and en'orcing m3 conventionK ll throu througho ghout ut m3 data) data)as ase e 4or;, 4or;, 3our 3our naming naming conv convent ention ion must must not chang change1 e1
Genera%
!ere are some )asic simple rules 4hen it comes to the name o' an3 o).ect in a data)ase Aincluding the name o' the data)ase itsel'B(
Do not use saces in o&7ect naes. It might seem tempting and cute to have a vie4
named $ales 53 Guarter, )ut this is a nightmare to deal 4ith programmaticall31 Instead 4rite it as 8$ales53Guarter9 8$ales53Guarter9 or 8$ales53Gtr9 or 8Gtrl3$ales9
Avoid using reserved 5ords. This is easier said than done, as there is a ver3 large list
o' current and 'uture reserved 4ords to chec; against1 Eample( do not name 3our 'ile as 8Date9 )ecause 8Date9 is a reserve 4ord in Ms ccess1
Do not use das"es in data&ase naes. Dash 8/8 4as o'ten recognied recognied )3 a computer computer as an operato operatorr Asu)trac Asu)tractionB tionB,, so instead instead o' naming naming 3our o).ect as 8+uarter%*!a%es9 )etter name it as +uarter%!a%es9 or )e )ett tter er use use un unde ders rsco core re inst instea ead d o' a dash dash 8+uarter%:!a%es91 !tart o&7ect naes 5it" a %etter. I see ta)le named *++-$ales and columns and
columns named named , *, :, :, and so on Ato represent represent monthsB1 Ms ccess might might treat it as a num)er and not as a 'ilename1
;ee naes s"ort &ut eaningfu%. This is sel'/eplanator sel'/eplanator3 3 and 'airl3 logical, $lG $lG is
too short1 8$alesFiguresForComp 8$alesFiguresForCompan353FiscalGuart an353FiscalGuarter er is .ust sill31
There is little 4orse than going through a data)ase data)ase schema 4here all the ta)le names are are in LL C&$1 Its li;e Ms ccess ccess is 3elling at 3ou Li;e4ise, Li;e4ise, tr3ing to read a procedure name li;e getallarticlestatistics)34ee; could drive some people to drin;1 I li;e l i;e camel/P&ascal/casing or using underscores, underscores, leaning to4ard the 'ormer in most cases( "etrticle$tats53ee; or getrticle$tats53ee;1 getrticle$tats53ee;1 Use sensi&%e case.
Avoid
t3pe o' data it contains, and i' 'or some reason it does not, then there is al4a3s the metadata ta)les andPor the documentation 3ou should have 4ritten 4hen designing the Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age = o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
s3stem1 %sing datat3pe/st3le pre'ies 'or columns li;e IContactID AintegerB and VEmail AvarcharB not onl3 ma;e the column names harder to read, the3 also ma;e them less 'lei)le1
!e%f*C"ec> 1 Name:____________________
/ritten Test Date:_________________
Instruction: Answer Instruction: Answer all all the questions questions listed below, below, if you have have some clarificationsclarifications- feel feel free to ask your teacher teacher..
&lease as; 3our trainer 'or the 6uestionnaire 'or this $el'/Chec;1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age ? o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Inforation !"eet 2
Creating a data&ase o&7ect ta&%e
Creating D$ Ta&%es
Recentl3, Recentl3, 4e de'ine de'ine it as a file )ut technicall3, it 4as de'ined as a container or a worsheet!lie container 4here 4here the collection o' data has )een stored1 5e'ore 4e proceed to creating 3our 'irst ta)le, 4e need to ;no4 'irst the )asic components o' a ta)le( o Meta Data 2 Data)ase $tructure o Field 2 Column 2 Data o Fieldname o Record / Ro4 / In'ormation 'etadata 2 is a "data about data# or or s3non3mousl3 called ta)le structure that
de'ines 4hat t3pe o' data 3our data isK "iven these data( AB 8Noel# 4hich 4hich is a firstname, 4e can sa3 that it composes o' te$ts , so e de!ine the firstname to firstname to accept te$t only thus it accepts the %alue "Noel#
A*B $upposed that 8 %&9 is the Age o' Noel, Noel, then then 4e 4ill 4ill de'ine de'ine Age as a num)er, speci'icall3 an 'nteger so it 4ill onl3 accept a value 4hich is an integer1 Thus it gave a ta)le structure o'( ,ie%d Nae
Firstname ge
Data Te
Tet Integer
hich onl3 means to sa3 that 3ou cannot enter a num)er value in the irstname 'ield, li;e4ise 3ou cannot enter a tet value to the Age 'ield1 'ield1 In that that case, case, enter entering ing a num) nu m)er er value value to a irstname 'ield 4ould treat it as tet1 &$' Noel21 nd cannot )e used 'or computation1 ,ie%d @ Ever3 column in a ta)le represents a 'ield )3 4hich data has )een stored1 ,ie%d nae @ This re'ers to the name o' ever3 field 1 Records 2 Ever3 row in a ta)le represents a record, 4hich is a collection o' meaning'ul data1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Eample o' a ta)le ( Ta)le name( !tudentsTa&%e > columns Q > 'ields Row Record
IDNu&er
LNae
,Nae
'nae
Gender
$da
Address
MOE/+++ MOE/+++* MOE/+++: MOE/+++0 MOE/+++
Cuevas &angani)an Cerna !ipolito Ohm3
Noel Mar; &a &atric; Valder Diana
&ancho &ere $antos Cru ntonio
Male Male Male Male Female
/+-/@>? /+-/@>? /+-/@>? /+-/@>? /+-/@>?
rat;ilo &iasa rat;ilo $idis;ilo rat;ilo
Field names
"ME!*** is is the data !or data !or *Number !ield. #nd *Number is a fieldname !or fieldname !or that data.
MOE/+++ Cuevas Noel &ancho Male /+-/@>? rat;ilo Ro4 gives an in'ormation1 in'ormation1 It tells that Noel Pancho +ueas is a Male 4hose )irthda3 is on &&!*-!&/0 and 4ho lives in Aratilo.
1 Record
Data Tes
hen creating a ta)le, one 4ill need to speci'3 4hat ;inds o' data are to )e stored in this 'ield1 The di''erent ;inds o' Ms ccess *++: data t3pes are( Tet( allo4s 'or the storage o' an3 ;ind o' data, characters, digits and special characters1 It has a de'ect length o' -+ characters 4ith a maimum length o' *--1 It is normall3 used to store data such as names, addresses, or an3 num)er not used in calculations, li;e telephone num)ers or ip codes1
'eo( is used used 'or 'or tet tets s o' more more than than **-- char charac acte ters rs such such as comm commen ents ts or
eplanations1 It has a maimum length o' <-1-:< characters1 ccess recommends that to store store 'ormatt 'ormatted ed tet or large large documen documents, ts, rather to create create an OLE O).ect 'ield than a Memo 'ield1 Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age B o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
In ccess*++: ccess*++: it is possi)le to arrange or group in a Memo Memo 'ield, 'ield, )ut ccess ccess onl3 uses the 'irst *-- characters 4hen it arranges or groups in a Memo 'ield1
Nu&er( 'or numerical data used in mathematical calculations1 ithin the num)er
t3pe 4e are a)le to speci'3 the sie propert3 o' the 'ield1 The t3pes 53te, Integer and Long Integer allo4 the storage o' num)ers 4ithout decimalsH the t3pes $ingle, Dou) Dou)le le and and Deci Decima mall allo allo4 4 de deci cima mals lsHH the the t3pe t3pe Repl Replic icat atio ion n ID is used used 'or 'or autonumerical codes in replication data)ases1
DateTie4 'or the introduction o' date and time 'rom the 3ear ++ to @@@@1
Currenc4 For For mone moneta tar3 r3 valu values es and and nu nume meri rica call data data used used in math mathem emat atic ical al
calculations in 4hich the data involved contains )et4een one and 'our decimals1 The accurac3 is up to - digits to the le't o' the decimal separator and up to 0 digits to the right o' the same1 ccess recommends the use o' Currenc3 t3pe to avoid the rounding o'' o' num)ers in calculus1 Currenc3 'ield has an accurac3 o' up to - digits to the le't o' the decimal separator and 0 digits to the right1 Currenc3 'ield occupies ? )3tes o' space on disc1 Autonu&er4 a uni6ue se6uential num)er Aincreasing one )3 oneB that ccess assigns assigns ever3 time it adds a ne4 record record to a ta)le1 ta)le1 utonum utonum)er )er 'ields cannot cannot )e updated1 esNo4 #es and No values, values, and 'ields that contain one o' t4o values values A#esPNo, A#esPNo,
TruePFalse or ctivatedPDeactivatedB1 ctivatedPDeactivatedB1
OLE O&7ect4 an o).ect such as a Microso't Ecel spreadsheet, a Microso't ord
document, graphics, images, sounds, or other )inaries1
<er%in>4 tet or a com)ination o' tet and num)ers stored as tet and used as a
h3perlin; address1 !iperlin; is a tet or graphic that 3ou clic; to go to a 'ile, a location in a 'ile, a 4e) page on the Internet, or a 4e) page on an intranet1 Ta&%es a &e created & eit"er4
Ta)le 4iard Design vie4
+reating Tables using design iew user custom customi1ed i1ed wa2 o' ma;ing data storage Creation Creation o' a ta)le )3 design vie4 is a user ta)les1
Each 'ield in the design vie4 o' a ta)le corresponds to a column in the datasheet vie4 o' a ta)le1 Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Designing a ta&%e invo%ves4
Entering uni6ue names o' the columns o' the ta)le in the 8 field name# column o' the design vie41 Names o! !ields and objects in Microso't ccess can )e up to ?6 c"aracters %ong1 The3 can include an3 com)ination o' letters, num)ers, num)ers, spaces, and special characters ecept a period A1B, an eclamation point AB, an accent grave AB, and )rac;ets AS B1 The3 also canJt )egin 4ith leading spaces
Friar ;e is the uni6ue identi'ication identi'ication o' one record1 It 4ill not allo4 a duplication duplication o' the &rimar3 &rimar3 Ue3 Ue3 thus ma;e it uni6ue1 De'ine a Friar ;e 'ield A#ou donJt have to de'ine a
primar3 ;e3, )ut itJs usuall3 a good idea1 I' 3ou donJt de'ine a primar3 ;e3, Microso't ccess as;s i' 3ou 4ant ccess to create one 'or 3ou 4hen 3ou save the ta)leB
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 1 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
!e%f*C"ec> 2 Name:____________________
/ritten Test Date:_________________
Instruction: Answer Instruction: Answer all all the questions questions listed below, below, if you have have some clarificationsclarifications- feel feel free to ask your teacher teacher..
&lease as; 3our trainer 'or the 6uestionnaire 'or this $el'/Chec;1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 11 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Inforation !"eet -
Creating data&ase o&7ects for3 Huer and reort
Eample o' a 'orm ,ors
for is a t3pe o' a data)ase o).ect that is primaril3 used to enter or displa3 data in a data)ase1 Most 'orms are )ound to one or more ta&%es and Hueries in the data)ase1 'ormJs record source re'ers to the fields in the underl2ing tables and 3ueries 1 ,ors a &e created & eit"er4
Form 4iard Design vie4
+reating orms using a wi1ard
Creation o' a 'orm )3 using a 4iard is the Ms ccess pre/de'ined 4a3 o' creating a 'orm )3 simpl3 'ollo4ing the series o' steps and choosing 4hich 'ield and 'ormat 3ou 4ould 4ant 'or 3our 'orm1 +reating orms using design iew
Creation o' a 'orm )3 design vie4 is a user!customi1ed!wa2 o' ma;ing data entr3 'orms, $4itch)oard 'orm and even custom dialog )o1 Each control in the design vie4 o' a 'orm 4as )eing dragged 'rom the control tool)o1 Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 12 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Control Toolbox
+ueries
Huer is a derived item in the data)ase meant to ans4er speci'ic 6uestions that relate to the in'ormation in the data)ase1 It is the means to retrieve relevant in'ormation in one or more ta)les1 Gueries are hand3 during data processing1 +ueries a &e created & eit"er4
Guer3 4iard Design vie4 $GL Vie4
+reating 4ueries using a wi1ard
Creation o' a 6uer3 )3 using a 4iard is a 'astest and eas3 4a3 to etract in'ormation 3ou need 'rom one or more ta)les1 +reating 4ueries using design iew
Creation o' a 6uer3 )3 design vie4 is a user customi1ed wa2 o' setting/up criteria to 'ilter the necessar3 in'ormation 3ou need 'rom one or more ta)les1
Eample o' 6uer3 in design vie4
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 1- o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
+reating 4ueries using S45 iew
Creation o' a 'orm )3 $GL vie4 is another option )ut not normall3 )eing use )3 Ms ccess programmers1 It re6uires an understanding o' the $tructured Guer3 Language statements1
Eample o' a 6uer3 in an $GL vie4 Reorts
Reports provide a means o' organiing and summariing data1 Reports are o'ten used to present an overvie4 highlighting main points and trends1 report can )e a simple list, a status report or a monthl3 production report1 report is made 'rom the data availa)le1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 16 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
AEample o' a reportB
Reorts a &e created & eit"er4
Report 4iard Design vie4
+reating 6eports using a wi1ard
Creation o' a report )3 using a 4iard is a 'astest and eas3 4a3 to create a report )3 'ollo4ing the series o' steps and choosing a pre/de'ined template 'or 3our report1 +reating 6eports using design iew
Creation o' a report )3 design vie4 is a user customi1ed wa2 setting/up 3our report so to meet the users desired output and 'ormat1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 1= o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
!e%f*C"ec> Name:____________________
/ritten Test Date:_________________
Instruction: Answer Instruction: Answer all all the questions questions listed below, below, if you have have some clarificationsclarifications- feel feel free to ask your teacher teacher..
&lease as; 3our trainer 'or the 6uestionnaire 'or this $el'/Chec;1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 1? o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Inforation !"eet 6
Creating data&ase o&7ects acros3 odu%es and data access age
'acros
Macros in ccess can )e thought o' as a simpli'ied programming language 4hich 3ou can use to add 'unctionalit3 to 3our data)ase1 For eample, 3ou can attach a macro to a command )utton on a 'orm so that the macro runs 4henever the )utton is clic;ed1 Macros contain actions that per'orm tas;s, such as opening a report, running a 6uer3, or closing the data)ase1 Most data)ase operations that 3ou do manuall3 can )e automated )3 using macros, so the3 can )e great time/saving devices1 macro is essentiall3 a list o' actions that 3ou appl3 to o).ects to respond to events1 Each action carries out one tas;1 #ou create 3our actions in the order 3ou 4ant them to eecute1 In addition, 3ou speci'3 the arguments o' the actions, giving the program additional in'ormation as needed1 #ou can set conditions 'or each action in a macro to determine 4hether it runs or not1 Run a macro )3 appl3ing it to the event propert3 o' an o).ect1 Once the speci'ied event occurs the macro 4ill run )3 running the all the speci'ied actions1 ctions that have conditions applied to them ma3 or ma3 not run depending on 4hether or not the3 passed the conditional tests1 Once 3ouJve created 3our macros 3ouJll see them listed in the Macros ta) in the Data)ase 4indo41 This 4a3 3ou can attach an3 macro to an3 event propert3 in 3our data)ase1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 1 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
n eample o' a macro that opens a report 4ith a 'ilename 8RIT$tudentsList9 8RIT$tudentsList9 in print previe41 'odu%es
Modules, li;e macros, are o).ects 3ou can use to add 'unctionalit3 to 3our data)ase1 hereas 3ou create macros in ccess )3 choosing 'rom a list o' macro actions, 3ou 4rite modu modules les in the Vis Visual ual 5as 5asic ic 'or pp pplic licati ations ons AV5 AV5B B progra programm mming ing langua language1 ge1 modul module e is a collection o' declarations, statements, and procedures that are stored together as a unit1 module can )e either a class module or a standard module1 Class modules modules are attached to 'orms or repor reports ts,, and and usual usuall3 l3 conta contain in proce procedur dures es that that are speci' speci'ic ic to the the 'orm 'orm or repo report rt the3Jr the3Jre e attached to1 $tandard modules contain general procedures that arenJt associated 4ith an3 other o).ect1 o).ect1 $tandard $tandard modules modules are listed listed under under 'odu%es in the Naviga Navigatio tion n &ane, &ane, 4here 4hereas as class class modules are not1 No4 that 3ou ;no4 ho4 to put together a program, 3ou are read3 to give it a tr31 To create a V5 procedure, 3ou 'ollo4 man3 o' the same steps 3ou 'ollo4 4hen 3ou created macros1 The general steps in V5 programming are as 'ollo4s( 1 Identi'3 Identi'3 the the tas; tas; 3ou 4ant 4ant to to accompl accomplish ish *1 &lan the the steps steps needed needed to accom accomplish plish that that tas; tas; :1 Create the the programming programming code necessar3 necessar3 to implement the steps steps 01 Test Test the the pro progr gram am -1 Re'ine Re'ine the the progr program am <1 Repeat Repeat steps steps 0 and - until the the program program 4or;s 4or;s correct correctl3 l3 #ou create V5 programming code )3 using the V5 Editor, 4hich is descri)ed in the 'ollo4ing section1 /"at is t"e V$A Editor
#ou create V5 programs using the V5 Editor1 To start the V5 Editor, 'irst clic; 3our mouse on the Modules )utton in the Data)ase 4indo41 Then, clic; 3our mouse on the Ne4 )utton1 ccess, in turn, displa3s the V5 Editor, as sho4n )elo4(
Project window
Declaration Section
Procedure Box
Module window
Properties window
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 1B o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
mmediate window
Notice that there are several di''erent parts to the V5 Editor( 5indo5 . This is 4here In the upper le't corner is 4hat the editor re'ers to as the Fro7ect 5indo5. 3ou can see the di''erent elements o' 3our pro.ect and an3 modules that have )een de'ined in the 4or;)oo;1
ust )elo4 the &ro.ect 4indo4 is the Froerties 5indo5 1 !ere 3ou can speci'3 di''erent attri)utes o' 4hatever 3ou have selected in the &ro.ect 4indo41 For most simple development needs, 3ou 4ill never do much 4ith the &roperties 4indo41 To the right o' the &roperties 4indo4, and at the ver3 )ottom o' the screen, is the iediate iediate 5indo5 5indo51 This This is 4her 4here e 3ou 3ou can can eith either er test test part parts s o' 3our 3our proc proced edur ures es duri during ng devel de velop opme ment nt or 3ou 3ou can 'ind 'ind the the immedi immediate ate result results s o' variou various s comm comman ands1 ds1 The Immedi Immediate ate 4indo4 comes in ver3 hand3 during testing and de)ugging, 4hen the3 are necessar31
Note7 ! Note7 ! a indo is not displayed ithin the +# &ditor, you can display it by choosing one o! the options !rom the +ie menu. -or instance, i! you ant to display the roject indo, choose roject &$plorer !rom the +ie menu.
The 'odu%e 5indo5 , 4hich is the largest 4indo4 on the screen, is 4here 3ou do 3our programming1 t the top o' the Module 4indo4 are the t4o drop/do4n lists1 The one on the le't is called the O).ect )o1 The one on the right is the &rocedure )o1 #ou use the O).ect )o to select 4hich o).ect 3ou 4ant to 4or; 4ith1 hen 3ou 'irst create a module, the o).ect is set to the 4ord /eneral, meaning 3ou are 4or;ing on a general module, not on one associated 4ith a particular o).ect in a 'orm or report1 The Frocedure &o is 4here 3ou indicate the name o' the procedure on 4hich 3ou 4ant to 4or;1 I' 3ou choose or speci'3 a di''erent procedure in this )o, the in'ormation ccess sho4s in the Module 4indo4 changes to re'lect the V5 statements 3ou have assigned to that procedure1 The top level o' a module is the 8eclarations sectionH It )egins 4ith the procedure name 3ou indicate in the &rocedure )o 4hen 3ou 'irst create a module1 Ta;e a loo; at the Module 4indo4 AFigure *+/B1 It contains the programming code alread3 de'ined 'or the declarations section1 In this instance, there is onl3 one line o' code de'ined//a statement that indicates the data)aseJs de'ault sort order1
To enter programming statements into a procedure, 3ou t3pe them in the Module 4indo41 s 3ou enter in'ormation, ccess chec;s to ma;e sure it can understand 4hat 3ou t3pe1 In other Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 1 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
4ords, ccess chec;s the s3nta o' 4hat 3ou enter1 #ou use the correct s3nta 4hen 3ou 'ollo4 the V5 rules o' grammar1 #ou can cut, cop3, and paste sections o' code using standard indo4s mouse or ;e3)oard techni6ues1 #ou can per'orm these operations either in the same procedure or )et4een di''erent procedures1 Data Access Fages
data access page is a special t3pe o' e) page designed 'or vie4ing and 4or;ing 4ith data 'rom the Internet or an intranet data that is stored in a Microso't ccess data)ase or data)ase or a Microso't $GL $erver data)ase1 data)ase 1 The data access page ma3 also include data 'rom other sources, such as Microso't Ecel1 %sing a data access page is similar to using a 'orm( #ou can vie4, enter, edit, and delete data in a data)ase1 !o4ever, 3ou can also use a page outside a Microso't ccess data)ase, data)ase , so users can update or vie4 data over the Internet or an intranet1
Farts of a data access age
T"e &od * The )od3 is the )asic )asic design sur'ace sur'ace o' a data access access page1 On a page
that supports data entr3, 3ou can use it to displa3 in'ormational tet, controls )ound to data, and sections1 !ections * #ou use sections to displa3 tet, data 'rom a data)ase, and tool)ars1
T4o t3pes o' sections are sections are t3picall3 used on pages that support data entr3( group header and record navigation sections1 page can also have 'ooter and caption sections1
Grou "eader and footer %sed to displa3 data and calculate values1 Record navigation %sed to displa3 the record navigation control 'or control 'or the group
level1 record navigation section 'or a group appears a'ter the group header section1 #ou canJt place )ound controls in controls in a record navigation section1 Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 2 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development Cation %sed to displa3 captions 'or tet )oes and other controls1 It appears appears
immediatel3 )e'ore the group header1 #ou canJt place )ound controls in a caption section1 Each group level in a data access page has a record source1 source 1 The name o' the record source is displa3ed on the section )ar 'or )ar 'or each section used 'or a group level1
Designing different tes of data access ages
#ou design data access pages in Design vie4 in vie4 in Microso't ccess1 The page is a separate 'ile that is stored outside ccessH ccessH ho4ever, 4hen 3ou create the 'ile, ccess automaticall3 adds a shortcut to the 'ile in the Data)ase 4indo41 Designing a data access page is similar to designing 'orms and reports 3ou use a 'ield list, list, the tool)o, tool)o , controls, controls , and so on1 !o4ever, there are some signi'icant di''erences in the 4a3 that 3ou design and interact 4ith data access pages as opposed to 'orms and reports1 !o4 3ou design the page depends on 4hat it 4ill )e used 'or(
This t3pe o' data access page is o'ten used to consolidate and group in'ormation that is stored in the data)ase, and then pu)lish summaries o' the data1 For eample, a page might pu)lish the sales per'ormance 'or each region in 4hich 3ou do )usiness1 %sing epand indicators, indicators, 3ou can go 'rom a general summar3 o' the in'ormation, in'ormation, such as a list o' all the regions and their com)ined sales total, to speci'ic details on individual sales 4ithin each region1 The data access page might provide tool)ar )uttons 'or sorting and 'iltering the 'iltering the data, as 4ell as 'or adding, editing, and deleting the data in some or all group levels1 levels1 Interactive reorting.
This t3pe o' data access page ma3 include a &ivotTa)le list, list, similar to a Microso't Ecel &ivotTa)le report, report, that lets 3ou reorganie the data to anal3e it in di''erent 4a3s1 The page might contain a chart that 3ou can use to anal3e trends, detect patterns, and compare data in 3our data)ase1 Or it might contain a spreadsheet, spreadsheet, in 4hich 3ou can enter and edit data and use 'ormulas to calculate as 3ou do in Ecel1 Data ana%sis.
Using data access ages in Internet E%orer
data access page is connected directl3 to a data)ase1 hen users displa3 the data access page in Internet Eplorer, the3 are vie4ing their o4n cop3 o' the page1 That means an3 'iltering, sorting, and other changes changes the3 ma;e to the 4a3 the data is displa3ed including changes the3 ma;e 4ithin a &ivotTa)le list or list or spreadsheet spreadsheet a''ect onl3 their cop3 o' the data access page1 !o4ever, changes that the3 ma;e to the data itsel' such as modi'3ing values, and adding or deleting data are stored in the underl3ing data)ase, and there'ore are availa)le to ever3one vie4ing the data access page1 Note4 To vie4 and 4or; 4ith the data access page on the Internet or an intranet, users
need Microso't Internet Eplorer -1+ 4ith $ervice ∾ * A$&*B or later1 Using data access ages in 'icrosoft Access Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 21 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
#ou can also 4or; 4ith a data access page in &age vie4 in vie4 in ccess1 Data access pages can supplement the 'orms and reports that 3ou use in 3our data)ase application1 hen deciding 4hether to design a data access page, 'orm, or report, consider the tas;s that 3ou 4ant to per'orm1
!e%f*C"ec> 6 Name:____________________
/ritten Test Date:_________________
Instruction: Answer Instruction: Answer all all the questions questions listed below, below, if you have have some clarificationsclarifications- feel feel free to ask your teacher teacher..
&lease as; 3our trainer 'or the 6uestionnaire 'or this $el'/Chec;1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 22 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Oeration !"eet 1
Creating a ta&%e
Creating Database Object: Table (In this lesson, we will be using MS Access 2003)
5e'ore 3ou can create a data)ase o).ects, 3ou need to launch 'irst the M$ ccess $o't4are and then create a )lan; data)ase1
To %aunc" '! Access 2-
Dou)le clic; the Icon in the Des;top or Clic; on the !tart )uttonW A%% Frogras W 'icrosoft OfficeW 'icrosoft Access 2-
A*B A%% Frogras
A:B'icrosoft Office
AB clic; !tart $utton
A0B 'icrosoft Access 2-
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 2- o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
To create a &%an> data&ase
'ter launching the M$ ccess *++:, clic; the NE ICON then clic; the 9lan database: 'ter that, a File Ne4 Data)ase Data)ase 4ill pop/up1 pop/up1 T3pe the ,i%e nae (type M2first89.mdb as M2first89.mdb as your !irst table name ) 'or 3our data)ase1 'ter4ards, clic; the +reate )utton1
(2)c%ic> $%an> data&ase..
(1)c%ic> Ne5 icon
(-) Te a ,i%e nae
(6) c%ic> Create &utton
Note7 your !ile name must not ha%e a space. t must be " ord. M2first89 is M2first89 is a %alid !ile name, M2 first 89 is 89 is in%alid. 0ou can de!ine your on !ile name but !or this purpose let’s just use M2irst89. M2irst89. 0ou may or may not include .mdb as an an e$tension name. t ould by de!ault add .mdb to .mdb to your !ilename, thus, ma1e it M2irst89.mdb. M2irst89.mdb.
Creating a Ta&%e
'ter creating a 5lan; data)ase, 3ou are no4 read3 to create 3our 'irst o).ect, lets start 4ith the Ta)le1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 26 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
LetJs no5 start creating a ta&%e..
Creating a ta)le 4ould start )3 creating a ta)le structure structure 4hich 4ould de'ine ever3 'ield in a ta)le1 Let us create a ta)le 3Students4ist and let’s de!ine the !olloing !ields as !ollos' ,ie%d Nae
Data Te
Descrition (describes each !ield 5 optional)
$tudentID Lastname Firstname Middlename 5irthda3 "ender Course
Tet Tet Tet Tet Date Tet Tet
ID num)er o' students Last name o' students First name o' students Middle name o' students 5irth da3 o' students "ender o' students Course o' students
LetJs no5 siu%ate it in '! Access..
'ter creating a )lan; data)ase 4ith a 'ile name M2irst89.mdb, 3our screen should resem)le li;e this11
#ou are no4 read3 to create 3our 'irst data)ase o).ect, the ta)le1 Dou)le/clic; Create ta&%e in design vie5 then a )lan; ta)le structure template template 4ill appear1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 2= o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Enter value 'or ield Name, 8ata T2pe and 8escription as sho4n in the 'igure )elo41
To ma;e a pre/de'ined Course list, clic; the loo;up ta) properties then choose Com)o 5o on the Displa3 Control1 On the $ource List, choose Value List the start t3ping 8Management9 H 8IT9H 8Computer 8Computer $cience9H $cience9H 8ccountanc39H 8ccountanc39H Computer Computer Engineering1 Engineering1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 2? o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
To put primar3 ;e3 to a 'ield, clic; on the 'ield then clic; the ;e3 on the tool)ar a)ove and the the ;e3 4ill appear appear on the le't side o' the ;e31 hen 3ou 3ou set one 'ield as primar3 primar3 ;e3, 3ou couldnt input the same value 'or that 'ield1 Note4 For the Data te please re'er to the In'ormation $heet * 2 Creating data)ase o).ect
ta)le1 To insert ne4 'ields 4ithin the ta)le, in design vie4, right/clic; in the ro4 )elo4 4here 3ou 4ant to add the 'ield, and then clic; Insert Ro5s on the tool)ar1 To add the 'ield to the end o' the ta)le, clic; in the 'irst )lan; ro41 To change 'rom one vie4 to another, ma;e a selection on the Vie5 menu or clic; the arro4 net to the Vie5 )utton and choose 'rom the list that appears1 To iew, enter, and change data easil3 and directl3 in a ta)le, create a X forJ1 In the lo4er portion o' the 4indo4 ,ie%d roerties3 please re'er )elo4(
,ie%d si0e de'ines the num)er o' characters 3oure a tet fie%d can contain1 ,orat is normall3 use 'or a 'ield that has a data t3pe t3pe o' Num)er or or Date1 Clic; on it to
de'ine custom settings 3ou 4ant1 Inut 'as> 4as good in setting/up setting/up initial constant constant values1 $etting an input input mas; 'or ID num)er 'ield to 8MOE/@@@@@@9 4ould mean that 4hen 3ou enter a value 'or that 'ield it 4ould initiall3 give 3ou a value o' 'OE*: : : : : : 1 “666666” allo4s 3ou to enter an3 digit num)ers1 $o, 3ou dont need to t3pe MOE/ )ecause )ecause it 4ould automaticall3 automaticall3 appear1 #ou .ust need to add a
t3pe it an3more1 an3more1 In case o' a Female gender, gender, 3ou can alter it an34a31 an34a31 #ou 4ould save save time 'or all Male inputs since 3ou dont need to t3pe it an3more1 Va%idation Ru%e allo4s 3ou to 'ilter the values 3ou .ust 4ant to input 'or a particular 'ield1 $etting a "ender 'ield 'ield Validation Rule to "Male# or or "emale# 4ould mean that it 4ould onl3 accept a Male and Female value 'or "ender 'ield1 T3ping F or M to it 4ould give 3ou an error/message1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 2 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development Va%idation Tet is the counterpart o' Validation Validati on Rule1 Instead o' displa3ing displa3in g a computer
generated error/message 4hen 3ou input a 4rong value to a 'ield, 3ou can set 3our o4n error/message1 error/message1 #ou .ust t3pe the tet 3ou 3ou 4ant1 Eample( 8rong Input9
ReHuired fie%d 4hen set to es 4ould not allo4 3ou to s;ip a certain 'ield leaving it
)lan;1
Indeed 4hen set to O; (Du%icates O;) 4ould sort out a 'ield alpha)eticall3 and it 4ould accept duplicate values, 4hen set to O; (No Du%icate) 4ould sort out a 'iled alpha)eticall3 )ut 4ould not allo4 3ou to enter the same value (you cannot enter an * number that already e$ist) 1
'ter that, press Ctr%K! on the ;e3)oard to !ave 3our ta)le1 ta)le1 It 4ould as; 3ou to enter a Ta)le Name1 Replace Table& 4ith T
Note( $ame rule applies in naming a ta)le1 No spaces please1 Ma;e it one/4ord li;e T
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 2B o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
To input data, dou)le/clic; the T:!tudentsList ta)le and 3oure no4 read3 to input records to it1
To navigate 'rom records, Navigation &ar
"o to the )eginning record Enter ne4 record "o to the net record "o to the previous record "o to the end record To modi'3 a record, 3ou .ust go to the speci'ic 'ields o' the record and modi'3 it1 To delete a record, clic; on the Record !e%ection &ar the record 3ou 4ant to delete1 The record 4ill )e highlighted then press De% ;e3 on 3our ;e3)oard1 &ress Ctr%K! on 3our ;e3)oard to save changes to 3our ta)le1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 2 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
v
Oeration !"eet 2
Creating a for
Creating ,ors
There 4ere * 4a3s to create a 'orm 1 Creat Create e 'orm 'orm in in desig design n vie4 vie4 *1 Creat Create e 'orm 'orm )3 using using 4ia 4iard rd Let us tr3 to create a for & using 5i0ard. On the le't pane, clic; on the Forms ta) then dou)le clic; the Create for & using 5i0ard 1
Choose all 'ields 'rom the ta)le T
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age - o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
The single greater than A Y B allo4s 3ou to choose selected 'ield 'rom the vaila)le Fields1 To do the opposite Ato deselect the 'ieldPsB, clic; the less than or dou)le less than sign1
'ter that, choose la3out 'rom the list1 For this eercise, choose the de'ault la3out, the columnar then clic; net1
Choose also the 'orm st3le 3ou 4ant1 gain 'or this eercise, 3ou choose $tandard La3out and then clic; net1
$ave 3our 'orm as s design and then clic; Finish. It 4ill then then prompt prompt 3ou 3ou 4ith the 'orm in design vie4 as sho4n sho4n in the 'igure on the right1 Lets customie it a )it1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age -1 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
ust li;e the ta)le, 4hen 3ou run the 'orm in 'orm vie4, it has its navigation )utton )elo4 it that 4ould allo4 3ou to navigate 'rom one record to another and 4ould also allo4 3ou to add records to 3our ta)le using the 'orm1 It 4ould also allo4 3ou to delete records through the record selector1
Record $elector Navigation )uttons
The net thing that 3ou 4ill need to do is to create 3our o4n customied navigation )uttons1 Lets no4 go )ac; to 3our 'orms design vie41 No4, epand the 'orm a )it )3 pointing the cursor on the )ottom rightmost corner then dragging 4hile holding the le't mouse clic; diagonall3 do4n as sho4n on the 'igure on the right1 Net, select all the 'ields )3 dragging the mouse through the 'orm 4hile holding the le't clic; ma;ing sure it 4ould go over the entire 'ields as sho4n in the 'igure on the le't
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age -2 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
'ter selecting selecting all 'ields, drag it all do4n to center center into the 'orm1 'orm1 Do it )3 dragging 4hile 4hile holding the le't clic; o' the mouse1
dd a la)el )3 clic;ing the "Aa# on the Tool)o then dragging it on the top part o' the 'orm .ust up a )it to $tudentsID 'ield as sho4n on the le't1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age -- o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
T3pe $tudents Data)ase on it then ma;e the la)el a )it )igger )3 clic;ing 3our cursor an34here in the 'orm then clic;ing )ac; on the la)el to select it again1 'ter4ards 3ou can no4 change the 'ont and its sie on the tool)ar a)ove,
1 iard
*1)utton
hat 3ou 4ould do net is to create the navigation )uttons )3 using a )utton 4iard on the Tool)o1 To do that, do the 'ollo4ing 5utton iards $teps( 1 Clic; on the the 4iar 4iard d on the tool)o tool)o *1 Clic; the )utton )utton on the the tool)o tool)o :1 Drag the the cursor cursor 4hile 4hile holding holding the the le't le't clic; clic; )elo4 the course 'ield to create a small )utton 01 On the Contr Control ol 5utton 5utton iard, iard, choose choose Recor Record d Navigation then choose "o to 'irst Record Then clic; net1 -1 Clic; the the de'ault de'ault )utton )utton icon picture picture then then clic; net1 <1 Name 3our )utton )utton as 5"o' 5"o'irst irst then then clic; clic; Finish1 :1 Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age -6 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
#ou have .ust created 3our 'irst navigation )utton that 4ould allo4 3ou to go to the 'irst record 4hen 3ou run it in 'orm vie41
Lets no4 create the rest o' the navigation )uttons as sho4n )elo41 1 "o to the 'irst record *1 "o to the previous record :1 Eit * : 0 01 "o to the net Record -1 "o to the Last Record
2. Go to t"e revious record &utton
To do that, do the 'ollo4ing 5utton iards $teps( 1 Clic; on the the 4iar 4iard d on the tool)o tool)o *1 Clic; the )utton )utton on the the tool)o tool)o :1 Drag the the cursor cursor 4hile 4hile holding holding the the le't le't clic; clic; )elo4 the course 'ield to create a small )utton 01 On the Contr Control ol 5utton 5utton iard, iard, choose choose Recor Record d Navigation then choose "o to previous Record then clic; net1 -1 Clic; the the de'ault de'ault )utton )utton icon picture picture then then clic; net1 <1 Name 3our 3our )utto )utton n as 5"o&re 5"o&reviou vious s then clic; clic; Finish1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age -= o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
-. Eit &utton
To do 1 *1 :1
that, do the 'ollo4ing 5utton iards $teps( Clic; on the the 4iar 4iard d on the tool)o tool)o Clic; the )utton )utton on the the tool)o tool)o Drag the the cursor cursor 4hile 4hile holding holding the the le't le't clic; clic; )elo4 the course 'ield to create a small )utton 01 On the Contr Control ol 5utton 5utton iard, iard, choose choose Form Form Operations then choose Close Form then clic; net1 -1 Clic; the the de'ault de'ault )utton )utton icon picture picture then then clic; net1 <1 Name 3our )utton )utton as 5Eit 5Eit then then clic; clic; Finish1
6. Go to net record &utton &utton
To do 1 *1 :1
that, do the 'ollo4ing 5utton iards $teps( Clic; on the the 4iar 4iard d on the tool)o tool)o Clic; the )utton )utton on the the tool)o tool)o Drag the the cursor cursor 4hile 4hile holding holding the the le't le't clic; clic; )elo4 the course 'ield to create a small )utton 01 On the Contro Controll 5utton iar iard, d, choose choose Recor Record d Navigation then choose "o to Net Record then clic; net1 -1 Clic; the the de'ault de'ault )utton )utton icon picture picture then then clic; net1 <1 Name 3our )utton )utton as 5"oN 5"oNet et then then clic; clic; Finish1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age -? o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
=. Go to %ast record &utton
To do 1 *1 :1
that, do the 'ollo4ing 5utton iards $teps( Clic; on the the 4iar 4iard d on the tool)o tool)o Clic; the )utton )utton on the the tool)o tool)o Drag the the cursor cursor 4hile 4hile holding holding the the le't le't clic; clic; )elo4 the course 'ield to create a small )utton 01 On the Contro Controll 5utton iar iard, d, choose choose Recor Record d Navigation then choose "o to Last Record then clic; net1 -1 Clic; the the de'ault de'ault )utton )utton icon picture picture then then clic; net1 <1 Name 3our )utton )utton as 5"oN 5"oNet et then then clic; clic; Finish1
#ou are are no4 no4 'inished 'inished creating 3our navigation )uttons1 Close it then save1 To vie4 vie4 it, dou)le/clic; the F$tudentsList 'orm1 hen it opens, tr3 navigating 'rom one record to another1 Clic; all the )uttons 3ou have made and see ho4 it 4or;s1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age - o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Oeration !"eet -
Creating a Huer
Creating +ueries
There 4ere * 4a3s to create a Guer3 1 Creat Create e 6uer3 6uer3 in desi design gn vie4 vie4 *1 Creat Create e 6uer3 6uer3 )3 usi using ng 4iar 4iard d Once ta)les are availa)le, it is eas2 to create a 3uer2. ust ust clic; clic; on the +ueries, then dou)le/clic; on Create Huer in design vie5 1 In the the !"o5 Ta&%e pop/up, add the T:!tudentsList ta)le1 ta)le 4ill 4ill sho4/up sho4/up on on top part part o' the 6uer31 6uer31 Dou)le Dou)le clic; one )3 one all the 'ields 'ields on the ta)le so it 4ould appear appear on the ta)le/li;e ta)le/li;e sheet sheet )elo41 ll the 'ields that 3ou have have selected selected 4ould appear appear on the 6uer3 vie4 4ith the corresponding records depends on 3our criteria1
Tr3 putting putting 8IT9 on the course course criteria criteria as sho4n a)ove1 a)ove1 Close Close and save 3our 6uer3 as GIT$tudentsList1 GIT$tudentsList1 Dou)le/clic; Dou)le/clic; on the GIT$tudentsLis GIT$tudentsListt to vie4 vie4 the results o' 3our 6uer31 It 4ould then sho4 the list o' all records 4hose course is 8IT9 as sho4n )elo41
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age -B o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
We have ust created a query.
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age - o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
Oeration !"eet 6
Creating a reort
Creating Reorts
There 4ere * 4a3s to create a Report 1 Creat Create e Report Report in in design design vie4 vie4 *1 Creat Create e Report Report )3 )3 using using 4iar 4iard d ust clic; on the Reorts, then dou)le/clic; on Create reort & using 5i0ard 1 Follo4 the series o' steps )elo4 in creating an IT $tudents List( 1 Choose GIT$tudentsList GIT$tudentsList 6uer3 on the Ta)lesPGueries drop/do4n arro41 *1 $elect all 'ields )3 clic;ing the dou)le greater greater than )utton .ust .ust li;e 4hat 4hat 3ou did in the the 'orm 4iard1 Clic; net a'ter1 a'ter1 :1 ust s;ip s;ip the level level groupin grouping g )3 clic;ing clic;ing Net1 Net1 01 On sort records, records, clic; clic; on the Lastname Lastname then then set its order order as scending scending then then clic; net1 -1 Choose the the de'ault la3out la3out 4hich is ta)ular and the the de'ault orientation orientation as portrait portrait then clic; net1 <1 Clic; the de'ault de'ault st3le as 'ormal 'ormal then choose the the Modi'3 reports reports in design design vie4 >1 $ave 3our 3our repor reportt as RIT$t RIT$tudent udentsLis sListt ?1 Clic; on the the Modi'3 Modi'3 the report reports s design design then clic; clic; Finish1 Finish1 @1 It 4ould then launch launch the Report in Design Vie4 Vie4 as sho4n sho4n )elo41 )elo41
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 6 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
$ave it then close it1 it1 To previe4 3our report, report, dou)le clic; clic; on the report report 'ile RIT$tudentsList1 Note4 to run the report, 3ou 4ould need to create a )utton in a 'orm em)edded 4ith macro
code or V5 code that once clic;, it 4ould eecute opening or printing o' a report1
Oeration !"eet =
Creating a acro
Creating 'acros
Lets tr3 to create a Macro that 4ill open that 4ill open the report that 3ou have .ust created1 Clic; no4 on the 'acros, then dou)le/clic; on Ne51 Choose OpenReport in the ction column, then on the Report Name propert3 )elo4 choose the report that 3ou have .ust createdARIT$tudentsLis createdARIT$tudentsListB1 tB1 On the vie4 propert3, propert3, choose choose print previe41 previe41 &ress Ctr%K! to save1 $ave 3our module as MIT$tudentsListReportOpen then close it1
To test 3our macro, dou)le/clic; dou)le/clic; the macro 'ile that 3ou have .ust created1 created1 It 4ould then launch the report previe4 o' RIT$tudentsList report as sho4n )elo41
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 61 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
#ou have no4 success'ull3 created a macro1 Its so simple that 3ou can pla3 around 4ith it1
Oeration !"eet ?
Creating a odu%e
Creating 'odu%es
ust clic; on the 'odu%es, then dou)le/clic; on Ne51 On the Microso't Visual 5asic 5asic Form, t3pe the code 3ou 4ant to per'orm as sho4n )elo41 For this activit3, t3pe the 'ollo4ing code )elo4( $u) )out the$3stemA B Msg)o 8This is a students data)ase9,9)out9 End $u) &ress Ctr%K! to save then save it as Md)out1 Md)out1 #ou have .ust created created a Module 4ith a 'unction that 4ill displa3 the message a)out 3our s3stem1 #ou can no4 close the Microso't Visual 5asic Form1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 62 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
o per!orm the modules, you need to call it in your !orm Eent Procedure to e$ecute or embed to a button by using a i8ard. (see -orm &%ent rocedure lin1s to module codes in the succeeding learning outcome).
Oeration !"eet
Creating a data access ages
Creating Data Access Fages
There 4ere : 4a3s to create a Data ccess &age 1 Create Create data data access access page page in Design Design vie4 vie4 *1 Create Create data data access access page page )3 )3 using using iard iard :1 Edit Edit e) page page that that alre alread3 ad3 eis eistt ust clic; on the Fages, then dou)le/clic; on Create data access age & using /i0ard1 Follo4 these steps in creating 3our 'irst Data ccess &age( 1 Choose T$tudentsList T$tudentsList ta)le on the Ta)lesPGueries drop/do4n arro41 *1 $elect all 'ields 'ields )3 clic;ing clic;ing the dou)le greater greater than than )utton .ust li;e 4hat 4hat 3ou did in the 'orm 4iard1 4iard1 Clic; net a'ter1 a'ter1 :1 On the level grouping, grouping, clic; on Course Field then then clic; the greater greater than )utton1 53 doing this, 3ou are grouping the students records according course1 01 On sort records, records, clic; on on the Lastname Lastname then set its order order as scending scending then clic; clic; net1 -1 $ave 3our 3our page page as as &IT$tu &IT$tudent dentsLis sListt <1 Clic; on Modi' Modi'3 3 the pages pages design design then clic; clic; Finish1 Finish1 >1 It 4ould then launch the Data ccess ccess &age that 3ou 3ou have .ust .ust created in design vie4 as sho4n )elo41
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 6- o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development
To vie4 the data access page, Clic; on Fages then dou)le/clic; the data access page 'ile that 3ou have .ust created1 Tr3 to eplore more the data access pages1
La Test
Fractica% Deonstration Deonstration
Name( Time started(
Date( Time 'inished(
Instructions4 #ou are re6uired to per'orm the 'ollo4ing individuall3 4ith the presence o' 3our
teacher1
&lease as; 3our trainer 'or the instructions 'or this lap test1
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 66 o' 0-
Entoto TVET College Training, Teaching and Learning Materials Development 0our teacher ill e%aluate your output either satis!actory or unsatis!actory. ! unsatis!actory, your trainer shall ad%ice you on additional or1. ut i! satis!actory, you can proceed to the ne$t topic.
Learning "uide st Re Revision
Date( +-/*+ uthor( Noel &1 Cuevas, IT 2 Entoto TVET College
&age 6= o' 0-