MS Access Lab Exercise

(1)

Management of Infor

Management of Information Systems and Techn

mation Systems and Technology

ology

MGT 186

Relational Database System using MS Access Relational Database System using MS Access

This

This exerexercise cise is is for for you you to to practice practice using using MS MS Access Access in in the the lab, lab, and and to to provide youprovide you with help as to how to work with access. In many regards, it is similar to your for! with help as to how to work with access. In many regards, it is similar to your for! grade" pro#ect. The goal withthis pro#ect is to learn how to create and use an MS grade" pro#ect. The goal withthis pro#ect is to learn how to create and use an MS Access database, not to rush

Access database, not to rush through the exercise.through the exercise.

$A%T I & T'( )I*( ST%+T+%(S $A%T I & T'( )I*( ST%+T+%(S -u

-ur r ddatataabbasase e shshalall l bbe e to to kkeeeep p ttraracck k of of sasalelespsperersosons ns in in a a ccomomppananyy. . ThThee SA*(S$(%S- table

SA*(S$(%S- table shall be made up of the

shall be made up of the following /elds0following /elds0

There

There should should be be a a table table of of customers customers who who are are ustomers ustomers of of the the salespersons, salespersons, thethe +ST-M(%

+ST-M(%

table. It should be made up of the

(2)

$A%T II & ST($ 12 ST($, %(ATI3 T'( SA*(S$(%S- TA1*( $A%T II & ST($ 12 ST($, %(ATI3 T'( SA*(S$(%S- TA1*( 4. )irst open up MS Access. lick in

4. )irst open up MS Access. lick in the reate a ew 5atabase" box6s use the reate a ew 5atabase" box6s use a 1lanka 1lank 5atabase". Then click -7.

5atabase". Then click -7.

8. 2ou6ll be asked to save the new database. 5own below, click in the )ile name" 8. 2ou6ll be asked to save the new database. 5own below, click in the )ile name" box, and call this (mployee.mdb". %emember &

box, and call this (mployee.mdb". %emember & this database will have two tablesthis database will have two tables.. lick reate.

lick reate.

9.

The The blank blank database will database will be be presented. The presented. The TTables" ables" tab tab will will already be already be selectedselected for you. ote that you

for you. ote that you can’tcan’t select anything but ew" on the select anything but ew" on the right. lick on it.right. lick on it. :. A new dialog box is opened. lick on

:. A new dialog box is opened. lick on Design ViewDesign View, and then -7. 2ou will do this, and then -7. 2ou will do this for both tables.

for both tables.

;. ow you6ll start setting up your table. In the )ield ame column, give the /eld its ;. ow you6ll start setting up your table. In the )ield ame column, give the /eld its identifying name. 2ou may use the suggested /eld names above. Then use the Tab identifying name. 2ou may use the suggested /eld names above. Then use the Tab key to tab over to the 5ata Type. <hen you do, a drop!down" arrow will be key to tab over to the 5ata Type. <hen you do, a drop!down" arrow will be displayed, as well as the default of

displayed, as well as the default of Text".

Text". =.

=. -nc-nce e you you do do thithis, s, the the 33eneeneralral" " tab under tab under ))ielield d $r$ropeopertirties" es" belbelow ow wiwill ll bebe selected. It is here that you give the /eld its properties, such as /eld width, the > selected. It is here that you give the /eld its properties, such as /eld width, the > sign in )ormat for the

sign in )ormat for the OfceOfce andand StateState /elds, which makes them u/elds, which makes them uppercase, and youppercase, and you create the ?alidation %ule and

create the ?alidation %ule and

?alidation Text, as described in the previous class session. ?alidation Text, as described in the previous class session.

@. 3o ahead and complete all the /elds. <hen you6re done with each table, before @. 3o ahead and complete all the /elds. <hen you6re done with each table, before you leave

you leave

5esign ?iew, you will need to set up the $rimary 7ey before you go to 5atasheet 5esign ?iew, you will need to set up the $rimary 7ey before you go to 5atasheet ?iew

?iew. Actually, you need to . Actually, you need to do this before formatting the input do this before formatting the input mask for the mask for the SSSS asas

a Social a Social

Security umberB Security umberB

C to do

C to do this, place your cursthis, place your cursor anywhere on the or anywhere on the row containing the /eld to row containing the /eld to bebe designated as the $rimary 7ey.

designated as the $rimary 7ey. C now click on the little k

C now click on the little key icon on the standard tool barey icon on the standard tool bar. The $rimary 7ey wi. The $rimary 7ey willll be assigned.

be assigned. D. ow click on

D. ow click on the 5atasheet view icon. the 5atasheet view icon. 22ou6ll be prompted to save ou6ll be prompted to save the table. all itthe table. all it

Sale

Salesperspersonson and click -7. After the save is complete, the 5atasheet view will beand click -7. After the save is complete, the 5atasheet view will be displayed.

displayed.

E.

ow enter in all of your ow enter in all of your data for thedata for the SalespersonSalesperson T Table. (nter about = rable. (nter about = records.ecords. $A%T III & %(AT( T'( +ST-M(% TA1*(

$A%T III & %(AT( T'( +ST-M(% TA1*( 4. ow that you have created the

4. ow that you have created the SalespersonSalesperson T Table, able, and and entered entered the the data, data, you6reyou6re ready to create the

ready to create the CustomerCustomer T Table.able.

8. )ollowing Steps 4 through E in $A%T II, create the

8. )ollowing Steps 4 through E in $A%T II, create the CustomerCustomer T Table and able and enter enter thethe data, using the same steps for

data, using the same steps for corresponding /elds.corresponding /elds. 9. MA7( S+%(

9. MA7( S+%( T'AT'AT T'( +ST-M(% %(T T'( +ST-M(% %(-%5S -T-%5S -TAI SA*(S$(%S- I5FsAI SA*(S$(%S- I5Fs <'I' (GIST I

<'I' (GIST I T'( SA*(S$(%S- TA1*(.T'( SA*(S$(%S- TA1*(.

:.

(nter about ; (nter about ; recordsrecords

..

$A%T I? & %(AT( T'( %(*ATI-S'I$ $A%T I? & %(AT( T'( %(*ATI-S'I$

(3)

4. (xit your table after you6ve entered all the data. 4. (xit your table after you6ve entered all the data.

8. lick on the Tools drop!down menu from the menu bar. lick on the %elationships 8. lick on the Tools drop!down menu from the menu bar. lick on the %elationships command.

command.

9. The Show Table dialog box is displayed0 9. The Show Table dialog box is displayed0

lick on each table and then click on lick on each table and then click on AddAdd lick on the common /eld in the

lick on the common /eld in the SalespersonSalesperson tabltable, and drag e, and drag it to it to the samethe same common /eld in the

common /eld in the CustomerCustomer table.table.

<hen you do, the %elationships dialog box will be displayed. on/rm the <hen you do, the %elationships dialog box will be displayed. on/rm the /elds displayed & and their tables. Then click in the (nforce %eferential Integrity /elds displayed & and their tables. Then click in the (nforce %eferential Integrity check box.

check box.

lick on the reate button. The relationship has been created. lick on the reate button. The relationship has been created. :. lose out of %elationships." If asked to save it, click H2es.H.

:. lose out of %elationships." If asked to save it, click H2es.H. $A%T ? & %(AT( T'( %($-%T +SI3 T'( %($-%T <IA%5 $A%T ? & %(AT( T'( %($-%T +SI3 T'( %($-%T <IA%5 4. lick the %eport" tab. lick ew.

4. lick the %eport" tab. lick ew. 8. lick the

8. lick the %eport <iJar%eport <iJard. Then click on d. Then click on the drop!down arrow to choose the the drop!down arrow to choose the table totable to report from. hoose the

report from. hoose the SalespersonSalesperson table.table.

9. lick on the /elds you wish to report, and then the right arrow, to place the /eld 9. lick on the /elds you wish to report, and then the right arrow, to place the /eld in the

in the

Selected )ields" box. To place A** the /elds in the box, click the double!arrow. Selected )ields" box. To place A** the /elds in the box, click the double!arrow.

:. After you6ve done this, if you want to remove a /eld, click on the /eld in the :. After you6ve done this, if you want to remove a /eld, click on the /eld in the Sel

Selected )ected )ieldields" box and s" box and then thethen the let-pointing single or (to remove all) double-let-pointing single or (to remove all) double-arrow

arrow buttons.buttons. ;. T

;. To rearrange the /elds, o rearrange the /elds, you may hyou may have to remove them ave to remove them from the right, and from the right, and replacereplace them from the left in diKerent seLuences.

them from the left in diKerent seLuences.

)ollow each step in the wiJard process to complete your report. At the end of the )ollow each step in the wiJard process to complete your report. At the end of the process, you6ll be prompted to give the report a name. 5o that, and click )inish". process, you6ll be prompted to give the report a name. 5o that, and click )inish". The report will

The report will be previewed for be previewed for you. After previewing it, you you. After previewing it, you may exit the may exit the report. If report. If you were to print it, you would click on

you were to print it, you would click on the $rint icon.the $rint icon.

$A%T ?I ! %(ATI3 +(%I(S simpleB $A%T ?I ! %(ATI3 +(%I(S simpleB 4. lick the ueries" tab. lick ew. 4. lick the ueries" tab. lick ew. 8. lick on 5esign ?iew and click H-7H 8. lick on 5esign ?iew and click H-7H

9. )rom the HShow TableH dialog box, click on the Salesperson Table and click HAddH. 9. )rom the HShow TableH dialog box, click on the Salesperson Table and click HAddH. The table

The table will be will be added to added to the the view table view table area above area above the the grid in grid in your Luery your Luery designdesign view. ow click on HloseH.

view. ow click on HloseH.

:. In the uery grid, /rst column, click on the drop!down arrow on the )ield row. :. In the uery grid, /rst column, click on the drop!down arrow on the )ield row. hoose the

hoose the

Sa

Salelespsperersoson n IDID /eld/eld. . oow w do do ththe e sasame me fofor r eaeach ch cocolulumnmnNs Ns /e/eldld, , seselelectctining g anan

Salesperson Salesperson Table

Table /eld to display. 5onNt select all the /elds ! the idea here is to select only the/eld to display. 5onNt select all the /elds ! the idea here is to select only the /elds you want.

/elds you want.

;. <hen youNve selected your /elds, save the Luery by clicking on the diskette icon ;. <hen youNve selected your /elds, save the Luery by clicking on the diskette icon and giving your Luery the name Salesperson uery. ow run the Luery by clicking and giving your Luery the name Salesperson uery. ow run the Luery by clicking on the red exclamation point icon in the toolbar at the top of the screen. 2our data on the red exclamation point icon in the toolbar at the top of the screen. 2our data will be displayed.

will be displayed.

=. -nce youNve done that, click on

(4)

(xperiment by changing your Luery, and

(xperiment by changing your Luery, and running it each time, in running it each time, in the following ways0the following ways0 !! llicick on tk on the She Sorort rot row in a cow in a colulumn omn othther ter thahan thn thee Salesperson IDSalesperson ID andand

select Ascending. select Ascending.

%un the Luery and view the results. ow change back to 5esign ?iew and %un the Luery and view the results. ow change back to 5esign ?iew and select

select

5escending for the same /eld and run it. ?iew the results. Try this with 5escending for the same /eld and run it. ?iew the results. Try this with several /elds.

several /elds.

!! llicick k oon n tthe Shohe Show w bbututtoton n iin n 5e5esisiggn n ??ieiewwB B fofor r a a //eleld d sshohoululd d bbee checked ! so HuncheckH itB and run the Luery. That /eld should not be checked ! so HuncheckH itB and run the Luery. That /eld should not be present. heck the /eld back again.

present. heck the /eld back again.

!! IIf yf yoou hau havveenNnNt ct chohosseen tn thhee StateState /eld to be in your Luery, click on the/eld to be in your Luery, click on the next empty /eldNs drop!down arrow and choose it. ow youNre going to next empty /eldNs drop!down arrow and choose it. ow youNre going to selec

select, or t, or /lter out, recor/lter out, records. lick in the ds. lick in the riteriteria row for theria row for the StateState /eld/eld and key in O3A. obviously, you will have to have some employees in and key in O3A. obviously, you will have to have some employees in 3A, and you should have some in other states tooB Tab out of the /eld. 3A, and you should have some in other states tooB Tab out of the /eld. ow run the Luery.

ow run the Luery.

!! TTry try the she samame see sellececttioion win withth Se!Se! being M and then ). 2ou should havebeing M and then ). 2ou should have some records of each sex so that this /lter works, displaying only the some records of each sex so that this /lter works, displaying only the male records and then, the

male records and then, the female recordsBfemale recordsB

!! 22ou caou can do tn do the samhe same thine thing witg with the h the ompaompany ar ny ar 22es P o es P o /eld, /eld, using using itit to /lter only those Salespersons who

to /lter only those Salespersons who drive a company cardrive a company car..

Don’

Don’t

t forget

forget to

to Tu

Turn

rn in

in your

your lab

lab activity

activity to

to our

our Google

Google Classro

Classroom

om to

to avoid

avoid

latelate

submission.

Activity No. 2

Overview Overview

This another lab activity will introduce you to database concepts using Microsoft Access. This another lab activity will introduce you to database concepts using Microsoft Access. Run MS Access

Run MS Access

•

• Under Start -- Progras! choose MS Access.Under Start -- Progras! choose MS Access.

"reate a U#$%&RS$T' (atabase "reate a U#$%&RS$T' (atabase

•

• "hoose )"reate a new database using *lan+ Access database) - clic+ O,"hoose )"reate a new database using *lan+ Access database) - clic+ O,

•

• Save it on the des+top with the nae )U#$%&RS$T'.db)Save it on the des+top with the nae )U#$%&RS$T'.db)

"reate (&PARTM&#T table "reate (&PARTM&#T table

•

• (ouble-clic+ on )"reate table in (esign view)(ouble-clic+ on )"reate table in (esign view)

•

• ill in the following fields and types to create the tableill in the following fields and types to create the table



$$&&//((##AAMM&& ((AATTAATT''PP&& (

(5)

(

(&&PPTT#A#AMM&& TT&&00TT *

*UU$$//(($$##11 TT&&00TT P

P22OO##&& TT&&00TT

•

• "hoose the (&PT $( as priary +ey"hoose the (&PT $( as priary +ey

o

o right clic+ on the row and choose )Priary +ey)right clic+ on the row and choose )Priary +ey)

•

• Save the table 3clic+ on the save icon4Save the table 3clic+ on the save icon4

•

• nae it )(&PARTM&#T)nae it )(&PARTM&#T)

&nter data into (&PARTM&#T table &nter data into (&PARTM&#T table

•

• ro the 5indow enu! choose )University(atabase) to view choicesro the 5indow enu! choose )University(atabase) to view choices

•

• (ouble-clic+ on )(&PARTM&#T)(ouble-clic+ on )(&PARTM&#T)

•

• ill in the following data! using tab to ove fro one cell to the ne6till in the following data! using tab to ove fro one cell to the ne6t

(

(&&PPT T $$(( ((&&PPT T ##AAMM&& **UU$$//(($$##11 PP22OO##&& "

"SS"" ""OOMMPPUUTT&&R R SS""$$&&##""&& TT''//&&RR 778899--::88;;<< M

MTT22 MMAATT22 TT''//&&RR 778899--::77;;== P

P22'' PP22''SS$$""SS PPAASSTTOORR&& 778899--::::>>:: &

&##11 &&##11//$$SS22 $$##((&&PP&&##((&&##""&& 778899-->>::;;:: *

*$$OO **$$OO//OO11'' **$$OO//OO11$$""AA/ / SS""$$&&##""&& 778899--99>>>>;;

•

• Save the table by clic+ing on the save iconSave the table by clic+ing on the save icon

"reate aculty table "reate aculty table

•

• 1o bac+ to University (atabase window1o bac+ to University (atabase window

•

• "hoose )"reate table in (esign view)"hoose )"reate table in (esign view)

•

• "reate table with the following fields"reate table with the following fields



$$&&//( ( ##AAMM&& ((AATTA A TT''PP&& 

AA" " $$(( TT&&00T T 33PPRR$$MMAARR' ' ,,&&''44 /

/AASST T ##AAMM&& TT&&00TT 

$$RRSST T ##AAMM&& TT&&00TT (

(&&PPTT TT&&00TT *

*((AATT&& ((AATT&& R

RAA##,, TT&&00TT 2

(6)

•

• Save the table as )A"U/T')Save the table as )A"U/T')

"reate aculty-(epartent relationship "reate aculty-(epartent relationship

•

• On the Tools enu! choose Relationships...On the Tools enu! choose Relationships...

•

• 2ighlight dept and clic+ )Add)2ighlight dept and clic+ )Add)

•

• 2ighlight faculty and clic+ )Add)2ighlight faculty and clic+ )Add)

•

• $cons for each table appear on the screen$cons for each table appear on the screen

•

• "lic+ and drag fro )(&PT $() in dept table to )(&PT) in faculty table."lic+ and drag fro )(&PT $() in dept table to )(&PT) in faculty table.

•

• $n the dialog bo6! clic+ on "reate$n the dialog bo6! clic+ on "reate

•

• Save the relationship by clic+ing on the save iconSave the relationship by clic+ing on the save icon

Add da

Add data to facuta to faculty tablelty table

•

• $nsert the following data into the faculty table -- notice that each faculty entry has a dept$nsert the following data into the faculty table -- notice that each faculty entry has a dept

that corresponds to an e6isting dept entry that corresponds to an e6isting dept entry 

AA" " $$(( //AASST T ##AAMM&& $$RRSST T ##AAMM&& (&(&PPTT **((AATT&& RARA##,, 22$$RR& & ((AATT&& ;

;;;>>;;>> SSMM$$TT22 ??OO&& ""SS"" ;;==@@::>>@@<<==BB AASSSSTT ;;<<@@::>>@@<<====77 ;

;;;>>:::: ??OO##&&SS AA//$$""&& MMTT22 ;>;>@@<<<<@@<<==88:: AASSSSTT <<::@@::>>@@::;;;;<< ;

;;;::::>> RR$$((AA'' ??&&####$$&&RR &&##11 <<::@@<<::@@<<==88;; AASSSSOO"" ;;77@@;;::@@<<====99 ;

;;;>>>>:: //AA##&& //AA##""&& ""SS"" ;;@@;;::@@<<==BB== UU//// ;;<<@@;;<<@@<<====BB ;

;;;99>>>> ??UU##$$PP&&RR //UU""'' MMTT22 ;>;>@@::<<@@<<==BB AASSSSOO"" ;;>>@@;;>>@@<<====77 As+ soe

As+ soe CuestionCuestions 3Cueries4s 3Cueries4

•

• On the University(atabase window! clic+ on the Dueries option 3left side colun4On the University(atabase window! clic+ on the Dueries option 3left side colun4

•

• (ouble-clic+ on )"reate Cuery in (esign view)(ouble-clic+ on )"reate Cuery in (esign view)

<.

<. Show aShow all depll departartentents in dess in descencending oding order brder by naey nae



 clic+ on the first ield bo6 and choose dept.naeclic+ on the first ield bo6 and choose dept.nae



 clic+ on the first Sort bo6 and choose Ascendingclic+ on the first Sort bo6 and choose Ascending



 clic+ on the second ield bo6 and choose dept idclic+ on the second ield bo6 and choose dept id



(7)

:.

:. Show thShow the naee naes and ran+s and ran+s of all faculs of all faculty ebty ebers in the "Sers in the "S" depa" departenrtentt



 "reate a Cuery in (esign view as above"reate a Cuery in (esign view as above



 Add bo Add both Ath A"U/T"U/T' an' and (&PT d (&PT to the Cto the Cuery by douery by double-clic+ing uble-clic+ing on theon the

naes naes



 $cons for both tables will appear in the top part of the Cuery$cons for both tables will appear in the top part of the Cuery



 $n the first three ield bo6es! choose A"U/T'./AST #AM&!$n the first three ield bo6es! choose A"U/T'./AST #AM&!

A"U/T'.$RST #AM&! and A"U/T'.RA#,. *e sure to chec+ the A"U/T'.$RST #AM&! and A"U/T'.RA#,. *e sure to chec+ the )Show) bo6 to indicate that you want these fields to show in the result. )Show) bo6 to indicate that you want these fields to show in the result.



 $n the fourth ield bo6! choose (&PT.(&PT $(. $n the "riteria bo6! type$n the fourth ield bo6! choose (&PT.(&PT $(. $n the "riteria bo6! type

E )"S"). This will only choose those rows where the (&PT $( E )"S"). E )"S"). This will only choose those rows where the (&PT $( E )"S").



 #ote that the two tables are autoatically lin+ed by (&PT $( because of#ote that the two tables are autoatically lin+ed by (&PT $( because of

the relationship that was set up above. (ouble-clic+ on the lin+ between the relationship that was set up above. (ouble-clic+ on the lin+ between the tables to see the properties of the ?oin. 'ou will see that the default is the tables to see the properties of the ?oin. 'ou will see that the default is to )Only include rows where the Foined fields fro both tables are eCual). to )Only include rows where the Foined fields fro both tables are eCual). This eans that the Cuery will only show results where the (&PT $( is the This eans that the Cuery will only show results where the (&PT $( is the sae in both tables.

sae in both tables.

Use the steps above to create a new table for students Use the steps above to create a new table for students

•

• 2ere is the structure of the student table2ere is the structure of the student table



$$&&//((##AAMM&& ((AATTAATT''PP&& /

/AASSTT ##AAMM&& TT&&00TT 

$$RRSSTT ##AAMM&& TT&&00TT S

STTUU(($$(( TT&&00TT M

MAA??OORR TT&&00TT

*

*$$RRTT22((AATT&& ((AATT&& 1

1RRAA(( ((AATT&& ((AATT&& A(%$SOR

A(%$SOR T&0TT&0T

•

• *e sure to create a priary +ey*e sure to create a priary +ey

•

• "reate a relationship fro the studentGs departent 3MA?OR4 to the departent table"reate a relationship fro the studentGs departent 3MA?OR4 to the departent table

•

• "reate a relationship fro the studentGs advisor 3A" $(4 to the faculty table"reate a relationship fro the studentGs advisor 3A" $(4 to the faculty table

•

• $nsert data representing at least <; students$nsert data representing at least <; students

"reate the following Cueries "reate the following Cueries

(8)

•

• /ist all students! sorted by graduation date./ist all students! sorted by graduation date.

•

• /ist all students advised by (r. Sith./ist all students advised by (r. Sith.

•

• or every student in the "S" aFor! list his@her advisor.or every student in the "S" aFor! list his@her advisor.

"hallenge Proble "hallenge Proble

•

• Add ano Add another table ther table represenrepresenting classeting classes. The fs. The fields shouields should include ld include at least tat least the classhe class

code! the nae of the class! the nuber of students ta+ing the class! the seester the code! the nae of the class! the nuber of students ta+ing the class! the seester the class is offered! the departent offering the class and the faculty teaching the class. class is offered! the departent offering the class and the faculty teaching the class.

•

• /in+ the classes to departent 3providing the class4 and faculty 3teaching the class4./in+ the classes to departent 3providing the class4 and faculty 3teaching the class4.

•

• ill in interesting data 3at least <; classes4.ill in interesting data 3at least <; classes4.

•

• "reate the following Cueries"reate the following Cueries

<.

<. /ist the /ist the naes naes of all cof all classes olasses offered ffered by the by the "oputer "oputer Science Science departedepartent.nt. :.

:. /ist the /ist the faculty faculty ebers ebers teaching teaching classes classes with owith ore thare than B; n B; studentsstudents.. >.

>. /ist /ist the the clasclass wis with thth the oe ost sst studtudentsents.. 9.

9. /ist th/ist the depe departartentents teacs teaching hing clasclasses in ses in all :;all :;;B.;B.