LO1.doc

Entoto TVET College Training, Teaching and Learning Materials Development yes

ENTOTO TVET COLLEGE under

Ethiopian TVET-System INFORMTION 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 net topic1



&age 2 o' 0-


Inforation !"eet 1

Oening 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 eample( #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 arie 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 anal3e data in di''erent 4a3s1 #ou can also use them as a source o' records 'or 'orms, reports, and data access pages1 pages 1 ,ors *  '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 

Reorts *  report is an e''ective 4a3 to present 3our data in a printed 'ormat1 5ecause



3ou have control over the sie 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 organie organie 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 Ecel1

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


&age - o' 0-


ll Ms ccess data)ases 'iles are saved 4ith etension .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 4iard o''ers limited options to customie 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%ei&%e et"od3 )ut it re6uires one to de'ine each data)ase element separatel3B1

$asic Data&ase Concet ( 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%eents4 Data are ra5 facts1 It tells the truth a)out a)out somethingH somethingH a person, a place, an object, etc 1 

Eample( AB 8Noel” is a data. “Noel” is my name, so

Nae

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”

Inforation is a collection o' data (ra !acts) hich is contained in " !ile (table in #ccess) Example: IDNu&er LNae ,Nae 'nae 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 LNae MOE/+++ Cuevas MOE/+++* &angani)an MOE/+++: Cerna MOE/+++0 !ipolito MOE/+++Ohm3 


,Nae

'nae

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


&age 6 o' 0-


Naing 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 saces in o&7ect naes. 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 Eample( do not name 3our 'ile as 8Date9 )ecause 8Date9 is a reserve 4ord in Ms ccess1 



Do not use das"es in data&ase naes. Dash 8/8 4as o'ten recognied recognied )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 naes 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 naes s"ort &ut eaningfu%. This is sel'/eplanator sel'/eplanator3 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 Its 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( "etrticle$tats53ee; or getrticle$tats53ee;1 getrticle$tats53ee;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


&age = o' 0-


s3stem1 %sing datat3pe/st3le pre'ies '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 'lei)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



&age ? o' 0-



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 worsheet!lie 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( AB 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 Nae

Firstname ge

Data Te

Tet Integer

hich onl3 means to sa3 that 3ou cannot enter a num)er value in the irstname 'ield, li;e4ise 3ou cannot enter a tet 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 tet1 &$' Noel21 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 nae @ 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



&age  o' 0-


Eample o' a ta)le ( Ta)le name( !tudentsTa&%e > columns Q > 'ields Row Record

IDNu&er

LNae

,Nae

'nae

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

"ME!***&# 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 +ueas is a Male 4hose )irthda3 is on &&!*-!&/0 and 4ho lives in Aratilo.

1 Record

Data Tes

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( Tet( allo4s 'or the storage o' an3 ;ind o' data, characters, digits and special characters1 It has a de'ect length o' -+ characters 4ith a maimum 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 



'eo( is used used 'or 'or tet tets s o' more more than than **-- char charac acte ters rs such such as comm commen ents ts or

eplanations1 It has a maimum length o' <-1-:< characters1 ccess recommends that to store store 'ormatt 'ormatted ed tet or large large documen documents, ts, rather to create create an OLE O).ect 'ield than a Memo 'ield1 Learning "uide st Re Revision


&age B o' 0-


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 sie 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 

DateTie4 'or the introduction o' date and time 'rom the 3ear ++ to @@@@1



Currenc4 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 esNo4 #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 Ecel spreadsheet, a Microso't ord

document, graphics, images, sounds, or other )inaries1 

<er%in>4 tet or a com)ination o' tet and num)ers stored as tet and used as a

h3perlin; address1  !iperlin; is a tet 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 4iard 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


&age  o' 0-


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 ecept a period A1B, an eclamation point AB, an accent grave AB, and )rac;ets AS B1 The3 also canJt )egin 4ith leading spaces 

Friar ;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 Friar ;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



&age 1 o' 0-


!e%f*C"ec> 2 Name:____________________






&age 11 o' 0-


Inforation !"eet -

Creating data&ase o&7ects for3 Huer and reort

Eample o' a 'orm ,ors

 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 ,ors a &e created & eit"er4  

Form 4iard Design vie4

+reating orms using a wi1ard

Creation o' a 'orm )3 using a 4iard 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 )o1 Each control in the design vie4 o' a 'orm 4as )eing dragged 'rom the control tool)o1 Learning "uide st Re Revision


&age 12 o' 0-


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 4iard Design vie4 $GL Vie4

+reating 4ueries using a wi1ard

Creation o' a 6uer3 )3 using a 4iard is a 'astest and eas3 4a3 to etract 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

Eample o' 6uer3 in design vie4



&age 1- o' 0-


+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

Eample o' a 6uer3 in an $GL vie4 Reorts

Reports provide a means o' organiing and summariing 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



&age 16 o' 0-


AEample o' a reportB

Reorts a &e created & eit"er4  

Report 4iard Design vie4

+reating 6eports using a wi1ard

Creation o' a report )3 using a 4iard 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



&age 1= o' 0-


!e%f*C"ec> Name:____________________






&age 1? o' 0-



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 eample, 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 eecute1 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



&age 1 o' 0-


n eample o' a macro that opens a report 4ith a 'ilename 8RIT$tudentsList9 8RIT$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 AV5B 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



&age 1B o' 0-


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 Froerties 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 iediate iediate 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 )o1 The one on the right is the &rocedure )o1 #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


&age 1 o' 0-


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 Ecel1 %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 tet, controls )ound to data, and sections1 !ections * #ou use sections to displa3 tet, 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


&age 2 o' 0-

Entoto TVET College Training, Teaching and Learning Materials Development Cation %sed to displa3 captions 'or tet )oes 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 tes 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 eample, a page might pu)lish the sales per'ormance 'or each region in 4hich 3ou do )usiness1 %sing epand 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 reorting.

This t3pe o' data access page ma3 include a &ivotTa)le list, list, similar to a Microso't Ecel &ivotTa)le report, report, that lets 3ou reorganie the data to anal3e it in di''erent 4a3s1 The page might contain a chart that 3ou can use to anal3e 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 Ecel1 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 Eplorer, 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 Eplorer -1+ 4ith $ervice ∾ * A$&*B or later1 Using data access ages in 'icrosoft Access Learning "uide st Re Revision


&age 21 o' 0-


#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:____________________






&age 22 o' 0-


Oeration !"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%% Frogras W 'icrosoft OfficeW 'icrosoft Access 2-

A*B A%% Frogras

A:B'icrosoft Office

AB clic; !tart $utton

A0B 'icrosoft Access 2-



&age 2- o' 0-




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 nae (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

(-) Te a ,i%e nae

(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 on !ile name but !or this purpose let’s just use M2irst89. M2irst89. 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 M2irst89.mdb. M2irst89.mdb.

Creating a Ta&%e

'ter creating a 5lan; data)ase, 3ou are no4 read3 to create 3our 'irst o).ect, lets start 4ith the Ta)le1



&age 26 o' 0-


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 !olloing !ields as !ollos' ,ie%d Nae

Data Te

Descrition (describes each !ield 5 optional)

$tudentID Lastname Firstname Middlename 5irthda3 "ender Course

Tet Tet Tet Tet Date Tet Tet

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 siu%ate it in '! Access..

'ter creating a )lan; data)ase 4ith a 'ile name M2irst89.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



&age 2= o' 0-


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 8ccountanc39H 8ccountanc39H Computer Computer Engineering1 Engineering1



&age 2? o' 0-


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 couldnt input the same value 'or that 'ield1 Note4 For the Data te 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 net 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 forJ1 In the lo4er portion o' the 4indo4 ,ie%d roerties3 please re'er )elo4(  









,ie%d si0e de'ines the num)er o' characters 3oure a tet fie%d can contain1 ,orat 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 Inut '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
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 dont 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



&age 2 o' 0-

Entoto TVET College Training, Teaching and Learning Materials Development Va%idation Tet 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 tet 3ou 3ou 4ant1 Eample( 8rong Input9



ReHuired fie%d 4hen set to es 4ould not allo4 3ou to s;ip a certain 'ield leaving it

)lan;1 

Indeed 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


&age 2B o' 0-


To input data, dou)le/clic; the T:!tudentsList ta)le and 3oure no4 read3 to input records to it1

To navigate 'rom records, Navigation &ar

"o to the )eginning record Enter ne4 record "o to the net 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



&age 2 o' 0-


v

Oeration !"eet 2

Creating a for

Creating ,ors

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 4ia 4iard 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


&age - o' 0-


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 eercise, choose the de'ault la3out, the columnar then clic; net1

Choose also the 'orm st3le 3ou 4ant1 gain 'or this eercise, 3ou choose $tandard La3out and then clic; net1

$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 Lets customie it a )it1



&age -1 o' 0-


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 net thing that 3ou 4ill need to do is to create 3our o4n customied navigation )uttons1 Lets no4 go )ac; to 3our 'orms design vie41 No4, epand 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 Net, 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



&age -2 o' 0-


'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



&age -- o' 0-


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 sie on the tool)ar a)ove,

1 iard

*1)utton

hat 3ou 4ould do net is to create the navigation )uttons )3 using a )utton 4iard on the Tool)o1 To do that, do the 'ollo4ing 5utton iards $teps( 1 Clic; on the the 4iar 4iard 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 iard, iard, choose choose Recor Record d Navigation then choose "o to 'irst Record Then clic; net1 -1 Clic; the the de'ault de'ault )utton )utton icon picture picture then then clic; net1 <1 Name 3our )utton )utton as 5"o' 5"o'irst irst then then clic; clic; Finish1 :1 Learning "uide st Re Revision


&age -6 o' 0-


#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

Lets no4 create the rest o' the navigation )uttons as sho4n )elo41 1 "o to the 'irst record *1 "o to the previous record :1 Eit  * : 0 01 "o to the net Record -1 "o to the Last Record

2. Go to t"e revious record &utton

To do that, do the 'ollo4ing 5utton iards $teps( 1 Clic; on the the 4iar 4iard 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 iard, iard, choose choose Recor Record d Navigation then choose "o to previous Record then clic; net1 -1 Clic; the the de'ault de'ault )utton )utton icon picture picture then then clic; net1 <1 Name 3our 3our )utto )utton n as 5"o&re 5"o&reviou vious s then clic; clic; Finish1



&age -= o' 0-


-. Eit &utton

To do 1 *1 :1

that, do the 'ollo4ing 5utton iards $teps( Clic; on the the 4iar 4iard 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 iard, iard, choose choose Form Form Operations then choose Close Form then clic; net1 -1 Clic; the the de'ault de'ault )utton )utton icon picture picture then then clic; net1 <1 Name 3our )utton )utton as 5Eit 5Eit then then clic; clic; Finish1

6. Go to net record &utton &utton

To do 1 *1 :1

that, do the 'ollo4ing 5utton iards $teps( Clic; on the the 4iar 4iard 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 iar iard, d, choose choose Recor Record d Navigation then choose "o to Net Record then clic; net1 -1 Clic; the the de'ault de'ault )utton )utton icon picture picture then then clic; net1 <1 Name 3our )utton )utton as 5"oN 5"oNet et then then clic; clic; Finish1



&age -? o' 0-


=. Go to %ast record &utton

To do 1 *1 :1

that, do the 'ollo4ing 5utton iards $teps( Clic; on the the 4iar 4iard 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 iar iard, d, choose choose Recor Record d Navigation then choose "o to Last Record then clic; net1 -1 Clic; the the de'ault de'ault )utton )utton icon picture picture then then clic; net1 <1 Name 3our )utton )utton as 5"oN 5"oNet et 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



&age - o' 0-


Oeration !"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 4iar 4iard 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 GIT$tudentsList1 GIT$tudentsList1 Dou)le/clic; Dou)le/clic; on the GIT$tudentsLis GIT$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



&age -B o' 0-


We have ust created a query.



&age - o' 0-


Oeration !"eet 6

Creating a reort

Creating Reorts

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 4iar 4iard d ust clic; on the Reorts, then dou)le/clic; on Create reort & using 5i0ard 1 Follo4 the series o' steps )elo4 in creating an IT $tudents List( 1 Choose GIT$tudentsList GIT$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 4iard1 Clic; net a'ter1 a'ter1 :1 ust s;ip s;ip the level level groupin grouping g )3 clic;ing clic;ing Net1 Net1 01 On sort records, records, clic; clic; on the Lastname Lastname then then set its order order as scending scending then then clic; net1 -1 Choose the the de'ault la3out la3out 4hich is ta)ular and the the de'ault orientation orientation as portrait portrait then clic; net1 <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 RIT$t RIT$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



&age 6 o' 0-


$ave it then close it1 it1 To previe4 3our report, report, dou)le clic; clic; on the report report 'ile RIT$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 eecute opening or printing o' a report1

Oeration !"eet =

Creating a acro

Creating 'acros

Lets 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 createdARIT$tudentsLis createdARIT$tudentsListB1 tB1 On the vie4 propert3, propert3, choose choose print previe41 previe41 &ress Ctr%K! to save1 $ave 3our module as MIT$tudentsListReportOpen 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' RIT$tudentsList report as sho4n )elo41



&age 61 o' 0-


#ou have no4 success'ull3 created a macro1 Its so simple that 3ou can pla3 around 4ith it1

Oeration !"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



&age 62 o' 0-


o per!orm the modules, you need to call it in your !orm Eent 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).

Oeration !"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 iard iard :1 Edit Edit e) page page that that alre alread3 ad3 eis eistt 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 4iard1 4iard1 Clic; net 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; net1 -1 $ave 3our 3our page page as as &IT$tu &IT$tudent dentsLis sListt <1 Clic; on Modi' Modi'3 3 the pages pages 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



&age 6- o' 0-


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 eplore more the data access pages1

La Test

Fractica% Deonstration Deonstration

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



&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.



&age 6= o' 0-

LO1.doc

Recommend Documents