PARTII:Query-By-Exampleinaccess

PARTII:Query-By-ExampleinAccess

Thenextobjectiveisforyoutopracticecreatingqueriesbyexample inMicrosoft Access.Inparticular,you are to dothe following.

i.RetrieveinformationfromthedatabaseusingQuery byExample(QBE).Thequeriesaretobesavedaspart of the database.

ii. Examine the SQL statements generatedbyeachofyourQBEqueries.

Select queries

Aselect query is oneofseveral kindsofqueries that might bemadeonadatabase(othersincluding update and delete). It is a request toretrieve informationfromadatabase.InAccess,thequery is typically createdby givinga symbolic example ofthe informationto be retrieved.This technique is calledQuery-by-Example(QBE).Itusesa special formcalledthe QBE grid. Inthis sectionanexample is presentedtoshowthe general details ofthe approach.At theendofthesectionis alist ofqueriesthatyouaretocreateandsaveaspart ofthedatabase.

Recall fromthe Part I labthat the recordtype for the grade database is the following.We must refer tothis recordtypeincreatingaquery.

Student(SSN, First, Last, Street, City, State, Zip,

Phone,Major,Class,Birthdate,Aid,Gender,HrsAttempted,HrsEarned,QualPoints)

Grades(SSN, Secid,Grade)

Sections(Secid,Cournum,Semester,Year,Instr)

Course(Cournum,Courname,Credithr,Dept)

Supposethat weneedthefollowinginformationfromthedatabase.

List theSSN,Last,Major,Cournum,Secid,andGradeforall studentsinsection82.

Tocarry out this request usingMicrosoft AccessQBE,webeginby goingtotheCreate ribbon. Because we want to createanewquery,weclickthe QueryDesign button.

On the initial display of theQBE grid, the grid may be partially hidden bythe“ShowTable”dialogbox.Atableis addedtotheupperportionoftheQBEgridby eitherdoubleclickingthename ofthetableorby highlightingthe tablename andclickingthe Addbutton.

We will needtoadd Sections,Grades,andStudent tothe grid. (Why those three? Seeifyoucananswerthat questionforyourself.) Uponcompletionoftheaddingofthesetables,theQBEgridhastheformshownbelow.

QBEGridwithAddedTables,Fields,andShowBoxes

IntheQBEgrid,fieldnameshavebeendraggedfromthetablesinthetoppart tothefieldrowinthegrid. This can alsobeaccomplishedbydouble-clickingonthefieldnames. Foranyfieldwhosevalueistobedisplayed,the “Show”boxforthat columnhasbeenclicked.Inthis case,all thefieldsaretobedisplayed.

Inthecriteriarow,theformula=82has beenenteredfor the sectionidentification field, secid. This causes only thoserowshaving82asthesecidtobedisplayed.Criteriacanbeenteredinthis way forasmany fieldsasneeded. Fieldshavingselectioncriteriacanbeincludedthat arenot tobedisplayed(“Show”boxnot checked).The followingimportant rulesholdforcriteriaontheQBEgrid.

Criteria in the same QBE grid row are combined using the andoperator. Criteria in different QBE grid rows are combined using the oroperator.

ClickingtheRunbutton(exclamationpoint)intheQuery Tools: Designribboncausesthequery tobeexecuted withtheresult displayedintheDatasheet Viewwindow.Youmay togglebetweenthedesignview,thedata view and (this will become relevant soon)theSQLviewbyclickingthe View buttonintheribbon.

The Datasheet View of the Query

Note that a given query can be givena name andsavedas part ofthe database. This is what youwill be doinginthis assignment.

Note that aquery canbebasedontheresults ofapreviously savedquery.Inmany cases(laterinthecourse, andinacoupleoftheassignedqueriesbelow),theeasiest methodtocreateacomplexquery is tocreateand save intermediate results as queries, andthencombine themto get the final result.

Queriesyouaretocreate.

Create the following queries. For the sake ofsimplicity, giveyouranswersthenames“query1”,“query2”,etc.. (Ordinarily you’dusemoremeaningful names.) Savethemaspart ofthedatabase.

1.List thesocial security number,first name,last name,andmajorofall CSmajors.

2.List thelast name ofthestudent,thecoursenumber,thesectionidentification,andgradeforall coursestaught in 2011.

3.List thelast namesandsecidforstudentswhomadeagradeofB insection82oragradeofAinsection83.

4.List thefirst name andlast name ofall studentswho havemadeagradeofAinany course. Donot repeat names. (HINT: Right-clickanywhereinthequery gridandchoose“Properties”. Seeifyoucanfigureout how toavoiddisplayingrepeatedrecords.)

5.List thesocial security numberandlast name ofstudentswhohavemadeat least oneAandat least oneB. Do not repeat names. Donot includethenamesofstudentswhomadeeither anAoraB. (HINT:Onewaytodo this is tocreatetwosimple queriesfirst,thendoanotherquery onthetwothat yousaved. Note that relating twotablesinaquery is just likerelatingtheminthe“Relationships”window–usingdraganddrop. You’ll needto dothis.)

6.List thesocial security numberandlast name ofstudentswhohavenevermadeagradeofA. (Thisoneis abit moredifficult thantheothers. Giveit yourbest shot andseeifyoucanfigureit out. What makesit difficult is actually that QBEdoesn’t provideanobviousway todoit. Aswe’ll seelater,it’smucheasiertodowiththe SQLlanguagedirectly. Ahint fornowis that onceyourelate twotablesinaquery –perhapsoneofthequery tablesyoucreatedforquery #5?–youcanright-clickonthelinkbetweentheminthequery windowand choose“JoinProperties”. Oneoftheoptionsthat presentsitselfis very useful tothis query. Anotheruseful hintisthatyoucansearchforrecordswithnovalueforaparticularattributebysearchingforNULL.)