[PDF] Microsoft Access Basics & Database Fundamentals

View - Download Microsoft Access Basics & Database Fundamentals

Previous PDF

Next PDF

MicrosoftAccessBasics

DatabaseFundamentals

training@health.ufl.edu

Updated:2/01/2017

3.0hours

Microsoft Access is a relational database application. It is the perfect tool when you begin to outgrow your data

collection in Excel. With Access, you can obtain better collection results by creating user-friendly forms with

rules to protect the validity of your data. You can create queries to analyze and filter your data, and reports that

can be regenerated anytime you need them. Topics for this workshop include database concepts, planning a

database, and a hands-on introduction to tables, queries, forms, and reports.

PandoraRoseCowart

Education/TrainingSpecialist

UFHealthITTraining

C3Ͳ013Communicore(352)273Ͳ5051

POBox100152prcowart@ufl.edu

Page1

WhatisaDatabase?

on

MedRec#FirstNameLastNameDOBDoctor

123Ͳ456JackNimble06/08/72Edwards

987Ͳ654JillPail08/27/65Lewis

753Ͳ951MaryBluebell12/08/51Edwards

Hereisasimpledatabaseofourdoctors:

EmpID#FirstNameLastNamePhone#

999Ͳ999KenEdwards555Ͳ1234

888Ͳ888LauraLang555Ͳ4567

777Ͳ777YolandaLewis555Ͳ7890

WhyuseMicrosoftAccess?

ourdata,without therepetitionthatmayoccuranywhereelse. tolimit

Theyareusedtoviewand

allowyoutogroupand Page2

PlanningtheDatabase

1. InputͲWhatdatadoIalreadyhaveforthedatabase?

2. OutputͲWhatinformationdoIwanttogetoutofthedatabase?

3. ProcessͲWhatdoIneedtodotogetthere?

Sometimesithelps

essentialto rulesforyourTables.

DesignRules

OrganizingData

shouldcreateasecondtable totrackthenumberedfields.

NoDerivedFields

Table.

DataisbrokendownintoSmallestLogicalParts

DescriptiveFieldNames

ShandsSystemNumber?

UniqueFieldNames

bothTablesintoonedatabaseobject.

NoCalculatedFields

fieldtypeforthetable.This Page3

UniqueRecords

inyourdataset,youcanuseanAutoNumber.

BasicAccessObjects

andQueries view.Eachkindofobjecthasitsownpurpose.

Tables

ͲFieldNames(thecolumnheadings)

ͲThetypeofdatastored

inafield(DataType).Inthisworkshopweuse:

DataTypeDescription

NumberLimitedtoNumbersonly

Date/TimeAllowsDatesand/orTimesonly

ortotypeinalistofyourowncreation. Page4 onthesettingsintheDesignView.

Vocabulary

Tablesmakeupadatabase

Table -Onesetofrelateddata

Queries

ormorerelatedTablesand/orotherQueries. addedormodifiedinaQuery,willbesaved

Record

Field Table

RecordSources-Tablesand/or

Queriescontainingthedata

Fields-Fieldnamesfromthe

aboverecordsourceand expressionstobuildnewfields

SortOrder-Orderoftheresult,in

orderofposition

Criteria-Limitationsappliedtothe

finalresult Page5 Forms

View,LayoutView,andFormView.

wanttoseeDr.Edward'sPatients,useaQuery. theTable.TheTableisthetrue andsizeofthefields,andthe objectslikeotherFormsandReports. Page6

Reports

theReportagain. printed. fields.

Access2016Basics-ClassExercise

Page7

ClassExercise

CreatetheDatabase

1. OpenMicrosoftAccess

2. ChooseBlankDesktopDatabase

3. Clickontheyellowfolderattheendofthe

FileNameboxandbrowseforthedesktop

4. Usethefilename:PatientAppointments

5. ClickCreate

ExploretheWindow

1. CloseTable1withtheXundertheribbon,orbyrightͲclickingonthenameofthetable

2. ExploretheRibbon

a. Hometab-Clipboard,Sort&Filter,SpellCheck,RefreshData,Formattext b. Create-Createadatabaseobject:Tables,Queries,Forms,Reports c. ExternalData -ImportandExportdata d. DatabaseTools-AdvancedFeaturesofRelationshipsandDataAnalyzers

CreatethePatientsTable

1. ClickontheCreatetabandchooseTableDesign

2. TypethefirstFieldName:PtMedRec#

a. DataType:ShortText,Description:Patient'sMedicalRecordNumber

3. Enterintherestofthefields(descriptionsnotnecessary):

4. SetthePtMedRec#tobethekey

a. Clickonthebigyellowkeyonthetoolbar

5. SavetheTableasPatients

Access2016Basics-ClassExercise

Page8

EnteringFirstRecord

1. TurntotheDatasheetView

2. EnterourfirstMedRec#:123Ͳ456

3. Presstabmovetothenextfield

123Ͳ456SamFranks35255512341/1/1

a. FirstName:Sam b. LastName:Franks c. Phone#:3525551234

Nodashes

d. BirthDate:1/1/1

ExittheDatabase

1. Exitthedatabase,Accesswillprobablynotaskyoutosave

a. Butitdidsavetherecord,itdoessoautomatically.

2. Openyourdatabasefromthedesktop

a. Ifnecessary,EnableContent

3. Openthetable(doubleͲclick)fromthenavigationpane

a. Samisstillthere!

RearrangeFields

1. InDesignView,movePtBirthDateabovethePtPhone#

a. Clickontherowheading,thegreyboxinfrontofthefieldname.ThenClick/Dragthe lineabovethePtPhone#

2. SwitchtotheDatasheetViewandSavethetable

a. Datasavesitself, structuralchangeshavetobesavedmanually

3. Enterthenextrecord

789Ͳ012JacobSmith2/2/923525554321

a. Nohyphensinthephonenumber

AddingFields

1. InDesignView,createPtGender,ShortTextfield,abovePtBirthDate

a. InsertRowsfromDesignTab,orfromtherightͲclickmenu

2. InDataView,enter"Male"(thewholeword)forSamandJacob

Access2016Basics-ClassExercise

Page9

Entera"troublemaker"Record

1. Enterthenextrecord

a. EnterGenderasjustonecharacter b. EnterbirthdateasMarch3,1983;itshouldchangeto3/3/1983 c. Typeinthehyphensforthephonenumber

2. GototheDesignviewandthenreturntotheDataview

a. NoticeJane'srecordmoves.Thisisbecauseby defaultAccesssortsbytheprimarykey bytheprimarykeyfield.

ModifyFieldProperties-FieldSize

1. InDesignView,setFieldSizepropertyofGenderatthebottomofthewindowtobe1

a. Whenyousaveyouwillgetthefollowingwarningmessagesayingdatamaybelost.We wantthistohappen,clickYes. b. Dataislost,ourMaleentriesshouldnowonlyreadM

ModifyFieldProperties-Format

1. InDesignView,settheFormatpropertyforPtBirthDatetobeaMediumDate

a. Noticethereisno"fieldsize"foradatefield,becauseitdoesn'tmatterhowmany b. Accessrecognizesdashes(1Ͳ1

Access2016Basics-ClassExercise

Page10

ModifyFieldProperties-InputMask

1. InDeignView,setanInputMaskforthePhoneNumber

a. ClickintheInputMashPropertyforPtPhone# b. ClicktheBuildbutton(...)attheendofthelinetolaunchthewizard c. IntheInputMaskWizard,PhoneNumberisalreadyselected.Click

FINISH.

d. SaveandViewResults

2. FixJane'sPhoneNumberbytakingouttheextradashes

EnteraNewRecord

1. Enteranewrecord

527Ͳ594DorisJonesF4/4/743525555432

2. ClosetheTable

3. OpenthePatient'sTable

4. ClosetheTable

CreateFemalePatient'sQuery

1. GototheCreateTabandchooseQueryDesign

Access2016Basics-ClassExercise

Page11

2. IntheShowTablewindow,pushtheAddbuttonandthenclosethewindow

3. DoubleͲclickingonthefieldnamestoaddPtFirstName,PtLastName,andPtGender

4. ViewDatasheetView

CustomizingaQuery

1. IntheDatasheetviewnoticethesortorderisbyMedRec#

2. IntheDesignview,setQuerytoSortbyPtLastNameAscending

3. GototheDataView,patientsshouldread,FranksthroughWilliams

4. IntheDesignView,settheCriterialineforthe

PtGenderfieldtobeF

a. InDatasheetview,youshouldonlyhavetwopeople:JaneandDoris

5. CloseandsavetheQueryasFemalePatients

Access2016Basics-ClassExercise

Page12

CreatePatientsForm

1. SelectPatientsTablefromleftNavigationPanesoitbecomesthedefaultdatasource

2. OntheCreateTabclickontheFormbutton

3. WeareintheForm'sLayoutview

a. Placeyourmousealongtherightborderofthehighlightedboxandresize

4. Changeto

the"Form"view(firstbuttonontheHomeTab)

5. CreateanewFEMALEpatient,anyoneyouwant

a. Tabuntilyoureachanewblankrecord,orusetheNEWbuttonontheHomeTab b. Makesuretoleavetherecord,"pencilsdown!",movetoanotherrecordorsave

6. OpenthePatientsTABLE,viewnewperson

a. FromtheleftNavigationPane,doubleͲclicktoopen

7. OpentheFemalePatientsQUERY,viewnewperson

a. Newpatienthasbeensaved,eventhoughtheFormhasnotbeensaved b. Ifyoudidnotclosethetableand/or query,youmaynotseethepersonrightaway.

8. Closeall,SaveFormas"Patients"

Notes gotothedesignoftheformandaddit. Data issavedinthetable,andwillappearinallthe createdyoucanreformatthedateanditwillnot changetheformatinanyotherdatabaseobject.

PHONE#:Aswiththeformat,aninputmaskisa

formattinginotherdatabaseobjects.

Access2016Basics-ClassExercise

Page13

CreateSimpleReport

1. SelectTablefromleftNavigationPanesoitbecomesthedefaultdatasource

2. OntheCreateTabclickontheREPORTbutton

3. ThereportopensinLayoutView,adjustthecolumnstofitthedata

4. RightͲclickinanemptyspaceandgotothePrint

Preview

5. CloseandSaveasPatients

CreateGroupedReport

1. SelectPatientTablefromleftNavigationPanesoitbecomesthedefaultdatasource

2. OntheCreateTabclickontheREPORTWIZARDbutton

a. Step1(Selectfields)

Confirmyou'reusing

Table:Patients

Usedoublearrow(>>)

tomoveoverallfields

Access2016Basics-ClassExercise

Page14

b. NextStep2(Grouping)Ͳ

GroupbyPtLastName,

PushtheGrouping

Optionsinthebottom

leftcornerofthe windowandchoose "1stLetter"

Ungroupallfields(noblue

intheleftside)

GroupbyPtBirthDate

twice

GroupingOptionsby

Month&byWeek

Ungroupallfields(noblue

intheleftside)

GroupbyGender

c. NextStep3(sorting)

SortbyLastNameandFirstName

Ascending

d. NextStep4(layout)

Outline

Portrait

AdjustingFields(checked)

e. NextStep6(saving)

PatientsbyGender

Access2016Basics-ClassExercise

Page15

ModifytheReport

1. RightͲclickanywhereonthereportandgotothelayoutview

2. AdjustthePtBirthDatefield,stretchingtowardthePtMedRec#field

3. CloseandsavetheReport

CreateAppointmentsTable

1. FromtheCreateTabchooseTableDesign

2. CreateTableasshownhere

3. SetApptID#tobethePrimaryKey

CreateLookupLocation

1. ChangetheDataTypeforApptLocationtobeLookupWizard

a. Step1ͲIwilltypeinthevaluesIwant b. NextStep2

Gainesville,Starke,Jacksonville

c. NextStep3-LabelApptLocation d. Finish

2. ApptLocationfieldtypestillsaysSHORTTEXT

a.

ViewLookuptabinthepropertiesatthe

bottomofthewindow

Access2016Basics-ClassExercise

Page16

CreateLookupPtMedRec#

1. ChangetheDataTypeforPtMedRectobe

LOOKUPWIZARD

a. Step1.Howdoyouwantyourlookupfieldto getitsvalues?

Iwantthelookupcolumntolookupthe

valuesinaTableorQuery b. NextStep2.Whichtableshouldprovidethe valuesforyourlookupfield?

Table:Patients

c. NextStep3.WhichfieldsofthePatientscontain thevaluesyouwanttoincludeinyourlookup field?

PtMedRec#,PtLastName,PtBirthDate

d. NextStep4.Whatsortorderdoyouwantfor theitems inyourlistbox?

PtLastName,PtBirthDate

e. NextStep5.Howwidewouldyoulikeyour columns?

UNCHECKthehidekeycolumn

f. NextStep6.Whichcolumninyourlookupfield containsthevaluesyouwanttostoreinyour database?

PtMedRec#

g. NextStep7.Whatlabelwouldyoulikeforyour lookupfield?

PtMedRec

h. ClickFinish i. ClickYestothewarningmessage"TheTable mustbesavedbeforetherelationshipscanbe created"

Access2016Basics-ClassExercise

Page17

AddanAppointment

1. InDatasheetviewenteranewrecord

2. Enteranewrecord

PtMed Rec# Appt

Doctor

Appt Date Appt Time Appt

Reason

ApptType

First

ApptType

FollowͲup

ApptType

Emergency

Appt

Location

123Ͳ456Jekyll10/172pMood

SwingsGainesville

ModifyApptTable

1. ChangeCAPTIONpropertyfortheYes/Nofields

FieldNameCaption

ApptTypeFirstFirstAppt

ApptTypeFollowͲupFollowͲup

ApptTypeEmergencyEmergency

2. ChangeApptTimeFORMATpropertytoremovetheseconds

CreateScheduleQuerywithMultipleTables

1. GototheCreateTabandclicktheQueryDesignbutton

2. AddbothTablesandclosetheShowTablewindow

3. DoubleͲclickonthefieldnamestoaddthemtothequery

a. PtMedRecfromAPPOINTMENTS b. PtFirstNameandPtLastNamefromPATIENTS c. ApptDoctor,ApptDate,ApptReasonfromAPPOINTMENTS

Access2016Basics-ClassExercise

Page18

AddanAppointmenttotheQuery

1. AddanewrecordintheDatasheetview

a. SelecttheMedRecforMsWilliams b. ChangeJanetoJaney c. SettheDoctor,Date,Time,andReason

2. CloseandSaveQueryasSchedule

CreatePatientAppointmentForm

1. GototheCreateTabandclicktheFORMWIZARD

a. ChoosetheTable:Patients b. DONOTCLICKNEXT c. ChooseTable:Appointments d. ClickFINISHͲWe areskippingtherestofthesteps

Access2016Basics-ClassExercise

Page19

UsingPatientAppointmentsForm

1. ClickinthePtLastNamefield

a. ClickthebinocularstoFIND(orpressCtrlͲF) b. TypeinWilliams c. ScheduleanotherapptforMsWilliams

2. CreateanewPatient

a. Schedulethemforanappointment

3. CloseandSavetheForm

Access2016Basics-ClassExercise

Page20

ViewtheFinalResults

1. Vieweachobjectinthedatabase

a. YourTables b. YourQueries c. YourReports

BackingupDatabase

1. FromtheFileTabchooseInfo

a. ChooseCompactandRepairquotesdbs_dbs21.pdfusesText_27

[PDF] access 2016 tutorial ppt

[PDF] access 2016 tutorial video

[PDF] access 2016 tutorial youtube

[PDF] access 2016 vba programming for dummies pdf

[PDF] access 3 students book pdf free download

[PDF] access 4 grammar book pdf

[PDF] access 4 student's book pdf free download

[PDF] access 97 tutorial

[PDF] access affordable housing

[PDF] access airwatch api

[PDF] access bars self treatment

[PDF] access consciousness blog

[PDF] access d login brick

[PDF] access d mobile

[PDF] access d'