Access Tutorial 2: Tables

Brydon

Sujets

• F18
• Characters
• Application linéaire
• Selectors API
• Valuese Sao Taliu

Informations

• Publié par: Actya
• Nombre de lectures: 75
• Langue: English

Extrait

Ac ces s T u t oria l 2: T a b l e s 2.1 Intr o d u c tio n: T h e im po rtan ce 2.2 Learn i ng obje c tives of goo d table d esign H o w do I en te r a n d ed i t d a ta i n th e dat ashe et vi ew of a ta bl e? T a bl es ar e w h er e dat a i n a d a ta ba se i s st or ed ; co n se qu en tly , t able s fo rm th e core o f a n y d a ta ba se H o w do I cr ea te a n e w ta bl e? ap pl ic at io n. I n ad d i t i on to ba s i c da t a , Ac c e s s p e rmit s H o w do I se t th e p r i m ar y ke y fo r a ta bl e ? a l a r g e am o unt of do ma i n kn ow l e d ge (su c h as ca p Ho w d o I s p e c if y f i el d pr op er t i es s u c h as t h e t i o n s, d e fa ult va l u es, co nstra i n t s, e t c.) to be store d at i n p u t m a sk a nd cap t i on? t he tab l e l e ve l . W h y wo n’ t a n aut onu m b e r f i e l d r e s t ar t E x tr a ti m e spe n t t h i n ki n g a bou t t abl e de si gn co un ti ng at o n e ? ca n re sult in en orm o us tim e savin g s d u ring Wh at ar e t h e di f f e r e n t t y pe s of k e y s ? l a ter st age s o f th e pr oj ect . N o n tri v i a l cha nge s t o ta ble s an d rela tion ships b e co m e incre a s 2.3 T u to r i a l exercises i n gl y di f f icu l t as the ap pl i c at io n g r ow s i n size a n d comple xity . In this tu t o ria l , you will le ar n to in ter a ct with e x istin g ta bl e s an d d e si gn ne w ta bl es. © Michael B r y don (br ydo n@u n ixg . ub c.ca) 1 o f 18 Pr evi o u s Next Home Las t up dat e : 25 Aug 19 9 72. Tab l e s Tut o ri al exe rci s es su ch a s l e ct ur er s, d e p a r t m e n t he ad s, d epa r t me nt al 2 . 3. 1 D ata s heet b a sics se cr e t a r ies, an d so o n . I f y o u ha v e n o t a l re ad y do ne s o , op e n t h e R e t ur n to th e da tab a se w i n d o w and cr eat e a n e w univ0_ v x .mdb da tab a se file f r o m T u torial 1 . ta bl e as sh ow n i n Figure 2.3 . O pen th e Depar tments ta bl e. The im p o r t an t I n t h e tabl e design w i ndow sh own in Figure 2.4 , e l em en ts of th e da ta shee t v i e w ar e s h own in typ e in th e f o l l o w i ng i n fo rm at io n: Figu re 2. 1 . U se the fie l d sele ctors t o a d just the width of the D e scription Fiel d name D a ta type (optional) DeptNa me fi el d as sh ow n i Figure n 2.1 . A dd the Bio l o g y dep artmen t (BIO L) to th e ta ble, EmployeeI D T e x t us e em plo y ee as s h own in Fi gur e 2 .2 . S .I.N. D el et e t h e “B a s ke t W e av in g ” re c o rd by c lic k i ng FName T e xt First na me on it s rec o rd se lec t or an d p r e ssi ng the De lete LName T e xt L a st n a me ke y . Phone Te x t Salary Cu rre ncy 2 . 3. 2 C reati n g a n e w t a ble I n this se ct i o n you w ill cre a te a n d sa ve a ve ry ba sic S el ect Fi le > Sav e fr o m t h e m a i n m e nu (o r pr ess ske l e to n f o r ta bl e ca l l ed Employees . Th i s tab l e Con t ro l S ) a nd save the ta bl e und er th e n a m e co ul d be use d to ke ep tr ack o f u n i v er si ty e m p l oye e s Employees . 2 o f 18 Pr evi o u s Next Home2. Tab l e s Tut o ri al exe rci s es FIG U RE 2. 1: The dat a sheet vi ew of the Departments ta ble . The fie l d na m e s are sho w n i n the “ f ield R e si ze t h e DeptName column by clicking ne ar selector s ” ac ross t h e t op of t h e columns . the column border and dra g ging the bor der to the right. You ca n te m por arily sort the re cords in a par t i c ul a r order by right c l i c k i ng a n y of th e field selec t ors. The re cords a r e shown a s r o ws. The blac k t r i a ngle indi c a tes the “cur r ent re cord” . The a s t e ri sk (*) i ndi cat es a The gre y boxes are “r ecor d se lectors” . place hol de r for a new r ecor d . The “na v igati on buttons” a t the bottom of the window i ndic a te the c u rre n t r ecor d n u mbe r and a l low you to go di r ectly to the first, previous, next, last, or ne w rec o rd. 3 o f 18 Pr evi o u s Next Home2. Tab l e s Tut o ri al exe rci s es FI GU R E 2. 2: Adding and saving a record to th e table. Add a new rec o rd by clicking in t DeptCode h e f i eld It is sel dom necessary t o of the “new r ecor d ” field ( m a r ked by the a s t e risk). e xpl ic i tly save ne w re c o rd s (o r c h an g e s to e x i s ting rec o rds) since A c ce ss a u to m a ti c a l l y sa ves whene v er you move to anothe r re cord, c l ose t h e table, quit A c c e s s , et c. To permane n tl y sa ve the cha nge to the da t a , click on the re cord se l e ctor (note the icon c h anges fr om a penc i l to a triangle ) . 4 o f 18 Pr evi o u s Next Home2. Tab l e s Tut o ri al exe rci s es FIGURE 2.3 : Cr ea te a ne w ta bl e. C lic k the New button to c r ea te a new ta bl e. Selec t “ d esign view ” (avoid u s ing the table wiz a rd a t this poi nt) . 5 o f 18 Pr evi o u s Next Home2. Tab l e s Tut o ri al exe rci s es F I GURE 2.4 : Use the tab l e design w i ndow to enter t h e fiel d properties for the Employees ta ble. Ente r the fi eld name s a nd da t a types for the five fields. The “ d esc r iption” c o lumn a llows you t o e n t e r a shor t c o m m e n t about th e field (this information i s not proc essed in any wa y by Access). The “f ield proper t ies” section allows you t o e n t e r information about the f i e l d and c ons tr aint s on t h e values for t h e field. 6 o f 18 Pr evi o u s Next Home2. Tab l e s Tut o ri al exe rci s es 2 . 3. 3 S p e c i fy ing the pri m ary key FIG U RE 2. 6: Set t h e field properties for the T a bl es no rm al l y h a ve a p r im a r y key th at un iq ue l y EmployeeID fi eld . i d ent if ie s th e r e cor d s i n th e ta bl e. W hen yo u d e si g n a te a fi el d as th e pr i m a r y key , Acce ss w i l l not al l o w yo u t o e n te r du pl i c ate val u es i n to t he fi el d. F oll o w t h e s t ep s in Fi gur e 2 .5 to set th e p r imary ke y of th e ta bl e to EmployeeID . 2 . 3.4 S ettin g fie l d p r op e r ties In t h is s e c t i o n , y o u wi ll s p e c if y a nu mb er of fi eld pr op er tie s f o r t h e EmployeeID fi el d, as sh ow n i n Figu re 2. 6 . S in ce w e ar e go in g to use th e em pl o y ee s’ So ci al I n sura nce Numbe r (S .I.N.) to un iqu e ly ide n tify t hem, set the Field Size pr o per ty to 1 1 ch ar act e r s ( 9 for n u m b e r s an d 2 for se pa ra ti ng spa c es) S e t t h e Inp u t Ma sk pr op er ty t o t h e f o l l ow ing : 000\ 0 00\ 000;0 S e t t h e Ca ptio n pr op er ty to Employee ID 7 o f 18 Pr evi o u s Next Home2. Tab l e s Tut o ri al exe rci s es F I GURE 2.5 : Se t t h e prim ary ke y f o r t h Employees e ta ble. C lick on the gr ey box beside the f i e l d (or f i e l ds) t h a t form the primary ke y. To selec t more than one fie l d f o r use as the pr i m ar y key, hold down the Co ntro l key while c l ic ki ng on the gr ey boxes. Eit h e r click the ke y shape d i c on i n the t ool bar or select Ed it > Prima r y Key fr om the m e nu. 8 o f 18 Pr evi o u s Next Home2. Tab l e s Discussion S el ect V i ew > Dat a she e t from th e main men u to th ree sma l l do ts ( ) to invo ke t h e inpu t mask w i z ar d. sw i t ch to dat ashe et mo de as sh ow n Fi in gu re 2. 7 . E n te r you r o w n S .I.N. and o b serve the e f fect of F ol low t h e i n s t ru c t ion s p r o v id ed b y t h e wiz ar d as t he in pu t m a sk and ca pti o n on th Emp e loyeeID sh ow n in Fi gu re 2. 8 . P r e ss fi el d . F1 wh ile th e cur s o r is st i ll i n the in p u t mask S el ect V i ew > T a bl e De si gn fr om th e m a i n m enu pr op er ty . Sc ro ll d o w n t h e he lp wi nd ow t o f i n d t h e to re tu rn to de s i g n mo de . me an ing of the “0 ”, “ 9 ” , “> ” an d “ L ” in p u t ma sk S et the fi el d pr op er ti es for FName and LName symbo ls. (n ot e t h at Le ng t h an d Cap t io n ar e t h e on ly t w o pr op er tie s t h a t a r e rel e v a nt f o r t h es e two f i el ds ) 2.4 Discu ssion 2 . 3. 5 U si ng the inpu t mask wi zard 2. 4. 1 K ey termi n o l og y I n th is sect i o n, you wi ll u s e t he i n pu t mask w i za r d to A key is one or mo re field s th at u n iqu e ly d e te rmin e cre a te a co mplex i n pu t mask for a stan da r d field th e i d en tity o f th e rea l wo r ld ob j e ct t hat the reco rd is t y p e . Y o u will al so u s e the h e l p s yste m to le ar n mo re me an t t o r e p r e s en t. F o r ex a m p l e, t h ere is a r e c o rd in a b o u t t h e m ean i ng of the sym bol s used to cr ea te th e stud ent info rm a t ion system t h a t con t ain s info r in put masks. ma ti on a b o u t y o u a s a s t ud en t . T o en s u r e t h a t th e S el ect the Phone f i e l d, mo ve t he cur s or to th e reco rd is a ssociat ed wi t h yo u a n d on l y you , it co n in put mask p r o p e r t y , and click th e b u tto n w i th 9 o f 18 Pr evi o u s Next Home2. Tab l e s Discussion FIG U RE 2.7: Observe the ef fect of t h e input mask and caption properties on the behavior of the EmployeeID field during dat a entry Try enter i ng va rious c h ar acter s a nd number s into the EmployeeID fie l d. P r ess the Esc ape ke y w h en you ar e done t o c l e a r the change s t o the r ecor d . If a c a p tion is spec ifie d, it re plac e s the field name in the fie l d se l e ctor. Note that the input ma s k w i ll not l e t you t ype any cha r ac ters other t h a n num be rs from 0 9. In a ddition , the spa c e s be twee n t h e groups of numbers a r e a dded a u toma ti ca lly. Inpu