tutorial 2 building a database and defining table ...microsoft access 2013 ® ® tutorial 2 building...
TRANSCRIPT
![Page 1: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/1.jpg)
Microsoft Access 2013® ®
Tutorial2BuildingaDatabase
andDefiningTableRelationships
![Page 2: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/2.jpg)
XPXPXPObjectives• Session2.1– Learntheguidelinesfordesigningdatabasesandsettingfieldproperties
– CreateatableinDesignview– Definefields,setfieldproperties,andspecifyatable’sprimarykey
– Modifythestructureofatable– ChangetheorderoffieldsinDesignview– AddnewfieldsinDesignview– ChangetheFormatpropertyforafieldinDatasheetview– ModifyfieldpropertiesinDesignview
NewPerspectivesonMicrosoftAccess2013 2
![Page 3: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/3.jpg)
XPXPXPObjectives(Cont.)• Session2.2– ImportdatafromanExcelworksheet– Createatablebyimportinganexistingtablestructure– AddfieldstoatablewiththeDataTypegallery– Deleteandrenamefields– ChangethedatatypeforafieldinDesignview– SettheDefaultValuepropertyforafield– Adddatatoatablebyimportingatextfile– Definearelationshipbetweentwotables
NewPerspectivesonMicrosoftAccess2013 3
![Page 4: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/4.jpg)
XPXPXPGuidelinesforDatabaseDesign• Case- ChathamCommunityHealthServices– Databasecurrentlycontainsonetable(Visittable)– Userwantstotrackinformationabouttheclinic’spatientsandtheinvoicessenttothemforservicesprovided• Thisinformationincludessuchitemsaseachpatient’snameandaddress,andtheamountandbillingdateforeachinvoice
– Createtwonewtables—namedBillingandPatient—tocontaintheadditionaldatatheuserwantstotrack
NewPerspectivesonMicrosoftAccess2013 4
![Page 5: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/5.jpg)
XPXPXPGuidelinesforDatabaseDesign(Cont.)• Case- ChathamCommunityHealthServices– Afteraddingrecordstothetables,youwilldefinethenecessaryrelationshipsbetweenthetablesintheChathamdatabasetorelatethetables
NewPerspectivesonMicrosoftAccess2013 5
![Page 6: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/6.jpg)
XPXPXPGuidelinesforDatabaseDesign(Cont.)
NewPerspectivesonMicrosoftAccess2013 6
![Page 7: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/7.jpg)
XPXPXPGuidelinesforDatabaseDesign(Cont.)
NewPerspectivesonMicrosoftAccess2013 7
![Page 8: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/8.jpg)
XPXPXPGuidelinesforDatabaseDesign(Cont.)• Identifyallthefieldsneeded
toproducetherequiredinformation
• Organizeeachpieceofdataintoitssmallestusefulpart
• Grouprelatedfieldsintotables• Determineeachtable’s
primarykey• Includeacommonfieldin
relatedtables• Avoiddataredundancy
NewPerspectivesonMicrosoftAccess2013 8
![Page 9: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/9.jpg)
XPXPXPGuidelinesforDatabaseDesign(Cont.)
NewPerspectivesonMicrosoftAccess2013 9
![Page 10: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/10.jpg)
XPXPXPGuidelines forSettingFieldProperties
• NamingFieldsandObjects– Youmustnameeachfield,table,andotherobjectinanAccessdatabase
• AssigningFieldDataTypes– Eachfieldmusthaveadatatype
• DatatypesareassignedautomaticallybyAccessorspecificallybythetabledesigner
• Thedatatypedetermineswhatfieldvaluesyoucanenter forthefieldandwhatotherproperties thefieldwillhave
NewPerspectivesonMicrosoftAccess2013 10
![Page 11: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/11.jpg)
XPXPXPGuidelines forSettingFieldProperties (Cont.)
NewPerspectivesonMicrosoftAccess2013 11
![Page 12: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/12.jpg)
XPXPXPGuidelines forSettingFieldProperties (Cont.)
• SettingFieldSizes– TheFieldSizepropertydefinesafieldvalue’smaximumstoragesizeforShortText,Number,andAutoNumberfieldsonly
– TheotherdatatypeshavenoFieldSizepropertybecausetheirstoragesizeiseitherafixed,predeterminedamountorisdeterminedautomaticallybythefieldvalueitself
• SettingtheCaptionPropertyforFields– TheCaptionpropertyforafieldspecifieshowthefieldnameisdisplayedindatabaseobjects
– Ifyoudon’tsettheCaptionproperty,Accessdisplaysthefieldnameasthecolumnheadingorlabelforafield
NewPerspectivesonMicrosoftAccess2013 12
![Page 13: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/13.jpg)
XPXPXP
• CreatingatableinDesignviewinvolvesenteringthefieldnamesanddefiningthepropertiesforthefields,specifyingaprimarykeyforthetable,andthensavingthetablestructure
• DefiningFields– Whenyoufirstcreate atableinDesignview, theinsertionpointis
located inthefirst row’sFieldNamebox,ready foryoutobegindefining thefirstfieldinthetable
– Entervalues fortheFieldName,DataType,andDescription fieldproperties, andthenselectvalues forallotherfieldproperties intheFieldProperties pane
– Theseotherproperties willappearwhenyoumovetothefirstrow’sDataTypebox
CreatingaTableinDesignView
NewPerspectivesonMicrosoftAccess2013 13
![Page 14: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/14.jpg)
XPXPXPCreatingaTableinDesignView(Cont.)
NewPerspectivesonMicrosoftAccess2013 14
![Page 15: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/15.jpg)
XPXPXPCreatingaTableinDesignView(Cont.)
NewPerspectivesonMicrosoftAccess2013 15
![Page 16: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/16.jpg)
XPXPXPCreatingaTableinDesignView(Cont.)
NewPerspectivesonMicrosoftAccess2013 16
![Page 17: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/17.jpg)
XPXPXPCreatingaTableinDesignView(Cont.)
NewPerspectivesonMicrosoftAccess2013 17
![Page 18: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/18.jpg)
XPXPXPCreatingaTableinDesignView(Cont.)
NewPerspectivesonMicrosoftAccess2013 18
![Page 19: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/19.jpg)
XPXPXP
• SpecifyingthePrimaryKey– Aprimarykeyuniquelyidentifies eachrecord inatable– Accessdoesnotallowduplicatevalues intheprimarykeyfield– Whenaprimarykeyhasbeenspecified, Access forces youtoentera
value fortheprimarykeyfieldinevery record inthetable(entityintegrity)
– Youcanenter records inanyorder,butAccessdisplaysthembydefaultinorderoftheprimarykey’sfieldvalues
– Access responds faster toyourrequests forspecific recordsbasedontheprimarykey
• SavingtheTableStructure– Thelaststepin
creatingatableistonamethetableandsave thetable’s structure
CreatingaTableinDesignView(Cont.)
NewPerspectivesonMicrosoftAccess2013 19
![Page 20: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/20.jpg)
XPXPXPModifyingtheStructureofanAccessTable
• MovingaFieldinDesignView– Tomoveafield,youusethemousetodragittoanewlocationintheTableDesigngrid• YoucanmoveafieldinDatasheet viewbydraggingitscolumnheadingtoanewlocation,doingsorearranges onlythedisplayofthetable’s fields;thetablestructure isnotchanged
• Tomoveafieldpermanently,move thefieldinDesignview
• AddingaFieldinDesignView– Toaddanewfieldbetweenexistingfields,youmustinsertarow
– Beginbyselectingtherowbelowwhereyouwantthenewfieldtobeinserted
NewPerspectivesonMicrosoftAccess2013 20
![Page 21: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/21.jpg)
XPXPXPCreatingaTableinDesignView(Cont.)
NewPerspectivesonMicrosoftAccess2013 21
![Page 22: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/22.jpg)
XPXPXPCreatingaTableinDesignView(Cont.)
NewPerspectivesonMicrosoftAccess2013 22
![Page 23: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/23.jpg)
XPXPXPModifyingFieldProperties
• ChangingtheFormatPropertyinDatasheetView– TheFormattinggroupontheFIELDStabinDatasheetviewallowsyoutomodifyformattingforcertainfieldtypes
– Whenyouformatafield,youchangethewaydataisdisplayed,butnottheactualvaluesstoredinthetable
NewPerspectivesonMicrosoftAccess2013 23
![Page 24: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/24.jpg)
XPXPXPModifyingFieldProperties(Cont.)
• ChangingPropertiesinDesignView– EachoftheShortTextfieldshasthedefaultfieldsizeof255,whichistoolargeforthedatacontainedinthesefields
NewPerspectivesonMicrosoftAccess2013 24
![Page 25: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/25.jpg)
XPXPXPUnderstandingTableRelationships
NewPerspectivesonMicrosoftAccess2013 25
![Page 26: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/26.jpg)
XPXPXP
• AddingRecordstoaNewTable• TheBillingtabledesigniscompleteandyouwouldliketo
addrecordstothetablesoitwillcontaintheinvoicedata• AddrecordstoatableinDatasheetviewbytypingthe
fieldvaluesintherowsbelowthecolumnheadingsforthefields
AddingRecordstoaNewTable
NewPerspectivesonMicrosoftAccess2013 26
![Page 27: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/27.jpg)
XPXPXPAddingRecordstoaNewTable(Cont.)
NewPerspectivesonMicrosoftAccess2013 27
![Page 28: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/28.jpg)
XPXPXPImportingDatafromanExcelWorksheet
NewPerspectivesonMicrosoftAccess2013 28
• WhendatayouwanttoaddtoanAccesstableexistsinanotherfile-- likeWordorExcel-- youcanbringthedatafromotherfilesintoAccessindifferentways• Copyandpastethedatafromanopenfile• Importthedata,whichisaprocessthatallows
youtocopythedatafromasourcewithouthavingtoopenthesourcefile
![Page 29: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/29.jpg)
XPXPXPImportingDatafromanExcelWorksheet(Cont.)
NewPerspectivesonMicrosoftAccess2013 29
![Page 30: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/30.jpg)
XPXPXPImportingDatafromanExcelWorksheet(Cont.)
NewPerspectivesonMicrosoftAccess2013 30
![Page 31: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/31.jpg)
XPXPXPImportingDatafromanExcelWorksheet(Cont.)
NewPerspectivesonMicrosoftAccess2013 31
![Page 32: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/32.jpg)
XPXPXPCreatingaTablebyImportinganExistingTableStructure
NewPerspectivesonMicrosoftAccess2013 32
• IfanotherAccessdatabasecontainsatable—orevenjustthedesign,orstructure,ofatable—thatyouwanttoincludeinyourdatabase,youcanimportthetableandanyrecordsitcontainsorimportonlythetablestructureintoyourdatabase
![Page 33: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/33.jpg)
XPXPXPCreatingaTablebyImportinganExistingTableStructure(Cont.)
NewPerspectivesonMicrosoftAccess2013 33
![Page 34: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/34.jpg)
XPXPXPAddingFieldstoaTableUsingtheDataTypeGallery
NewPerspectivesonMicrosoftAccess2013 34
• TheDataTypegallery(intheAdd&DeletegroupontheFIELDStab)allowsyoutoaddagroupofrelated
fieldstoatableatthesametime,ratherthanaddingeachfieldtothetableindividually
![Page 35: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/35.jpg)
XPXPXPModifyingtheImportedTable
NewPerspectivesonMicrosoftAccess2013 35
• DeletingFieldsfromaTableStructure– Afteryou’vecreatedatable,youmightneedtodeleteoneormorefields (whichalsodeletesallthevaluesforthatfieldfromthetable)
– Beforeyoudeleteafield,youshouldmakesurethatyouwanttodosoandthatyouchoosethecorrectfieldtodelete
– FieldscanbedeletedineitherDatasheetvieworDesignview
![Page 36: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/36.jpg)
XPXPXPModifyingtheImportedTable(Cont.)
NewPerspectivesonMicrosoftAccess2013 36
• RenamingFieldsinDesignView– TomatchthedesignforthePatienttable,youneedtorenametheStateProvinceandZIPPostalfields
– FieldscanberenamedinDatasheetvieworDesignview
![Page 37: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/37.jpg)
XPXPXPModifyingtheImportedTable(Cont.)
NewPerspectivesonMicrosoftAccess2013 37
• ChangingtheDataTypeforaFieldinDesignView– AllofthefieldsinthePatienttable,exceptBirthDate,shouldbeShortTextfields
– ThetablestructureyouimportedspecifiestheNumberdatatypeforthePhonefield-- itshouldbeShortText
– TheDataTypecanbechangedinDatasheetvieworDesignview
![Page 38: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/38.jpg)
XPXPXPSettingtheDefaultValuePropertyforaField
NewPerspectivesonMicrosoftAccess2013 38
• TheDefaultValuepropertyforafieldspecifieswhatvaluewillappear,bydefault,forthefieldineachnewrecordyouaddtoatable
![Page 39: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/39.jpg)
XPXPXPAddingDatatoaTablebyImportingaTextFile
NewPerspectivesonMicrosoftAccess2013 39
• ManywaystoimportdataintoanAccessdatabase– ImportinganExcelspreadsheet– Createdanewtablebyimportingthestructureofanexistingtable
– Importdatacontainedintextfiles
![Page 40: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/40.jpg)
XPXPXPDefiningTableRelationships
NewPerspectivesonMicrosoftAccess2013 40
![Page 41: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/41.jpg)
XPXPXPDefiningTableRelationships(Cont.)
NewPerspectivesonMicrosoftAccess2013 41
• One-to-ManyRelationships– Aone-to-manyrelationshipexistsbetweentwotableswhenonerecordinthefirsttablematcheszero,one,ormanyrecordsinthesecondtable,andwhenonerecordinthesecondtablematchesatmostonerecordinthefirsttable
• ReferentialIntegrity– AsetofrulesthatAccessenforcestomaintainconsistencybetweenrelatedtableswhenyouupdatedatainadatabase
![Page 42: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/42.jpg)
XPXPXPDefiningTableRelationships(Cont.)
NewPerspectivesonMicrosoftAccess2013 42
• ReferentialIntegrity– Whenyouaddarecord toarelated table,amatchingrecordmust
alreadyexist intheprimarytable,preventing thepossibilityoforphaned records
– Ifyouattempttochangethevalueoftheprimarykeyintheprimarytable,Accessprevents thischangeifmatchingrecords existinarelatedtable
– WiththeCascadeUpdateRelatedFields option,Accesspermits thechangeinvalue totheprimarykeyandchangestheappropriateforeignkeyvalues intherelated table
– Ifyouattempttodeletearecord intheprimarytable,Accesspreventsthedeletion ifmatchingrecords existinarelated table.However, ifyouchoose theCascadeDeleteRelatedRecordsoption,Accessdeletestherecord intheprimarytableandalsodeletes allrecords inrelatedtables thathavematchingforeignkeyvalues
![Page 43: Tutorial 2 Building a Database and Defining Table ...Microsoft Access 2013 ® ® Tutorial 2 Building a Database and Defining Table Relationshipshome.ku.edu.tr/~mehmetgonen/indr481_fall2015/indr481_fall2015... ·](https://reader036.vdocuments.us/reader036/viewer/2022062311/5a7124597f8b9aac538c91c2/html5/thumbnails/43.jpg)
XPXPXPDefiningTableRelationships (Cont.)
NewPerspectivesonMicrosoftAccess2013 43