Access 2007 Tutorial 02

COMPREHENSIVEAccessTutorial2BuildingaDatabaseandDefiningTableRelationshipsXPXPObjectives•Learntheguidelinesfordesigningdatabasesandsettingfieldproperties•Viewandmodifyfielddatatypesandformatting•CreateatableinDesignview•Definefieldsandspecifyatable’sprimarykey•ModifythestructureofatableNewPerspectivesonMicrosoftOfficeAccess20072XPXPObjectives•ImportdatafromanExcelworksheet•Createatablebyimportinganexistingtablestructure•Delete,rename,andmovefields•Adddatatoatablebyimportingatextfile•DefinearelationshipbetweentwotablesNewPerspectivesonMicrosoftOfficeAccess20073XPXPGuidelinesforDesigningDatabases•Identifyallthefieldsneededtoproducetherequiredinformation•Organizeeachpieceofdataintoitssmallestusefulpart•Grouprelatedfieldsintotables•Determineeachtable’sprimarykey•Includeacommonfieldinrelatedtables•Avoiddataredundancy•DeterminethepropertiesofeachfieldNewPerspectivesonMicrosoftOfficeAccess20074XPXPGuidelinesforSettingFieldProperties•Youmustnameeachfield,table,andotherobject•ChooseanappropriatedatatypeNewPerspectivesonMicrosoftOfficeAccess20075XPXPGuidelinesforSettingFieldProperties•TheFieldSizepropertydefinesafieldvalue’smaximumstoragesizeforText,Number,andAutoNumberfieldsonlyByteIntegerLongIntegerSingleDoubleReplicationIDDecimalNewPerspectivesonMicrosoftOfficeAccess20076XPXPViewingandModifyingFieldDataTypesandFormattingNewPerspectivesonMicrosoftOfficeAccess20077XPXPCreatingaTableinDesignViewNewPerspectivesonMicrosoftOfficeAccess20078XPXPDefiningaFieldinDesignView•IntheFieldNamebox,typethenameforthefield,andthenpresstheTabkey•AcceptthedefaultTextdatatype,orclickthearrowandselectadifferentdatatypeforthefield.PresstheTabkey•Enteranoptionaldescriptionforthefield,ifnecessary•UsetheFieldPropertiespanetotypeorselectotherfieldproperties,asappropriateNewPerspectivesonMicrosoftOfficeAccess20079XPXPDefiningaFieldinDesignViewNewPerspectivesonMicrosoftOfficeAccess200710XPXPSpecifyingthePrimaryKeyinDesignView•IntheTablewindowinDesignview,clickintherowforthefieldyou’vechosentobetheprimarykey.Iftheprimarykeywillconsistoftwoormorefields,clicktherowselectorforthefirstfield,pressandholddowntheCtrlkey,andthenclicktherowselectorforeachadditionalprimarykeyfield•IntheToolsgroupontheTableToolsDesigntab,clickthePrimaryKeybuttonNewPerspectivesonMicrosoftOfficeAccess200711XPXPSpecifyingthePrimaryKeyinDesignViewNewPerspectivesonMicrosoftOfficeAccess200712XPXPMovingaField•Tomoveafield,youusethemousetodragittoanewlocationintheTablewindowinDesignviewNewPerspectivesonMicrosoftOfficeAccess200713XPXPTheInvoiceTableFields•InvoiceNum,Text,Primarykey,size=4•ContractNum,Text,Foreignkey,size=4•InvoiceAmt,Currency,DecimalPlaces=2•InvoiceDate,Date/Time,Format=mm/dd/yyyy•Paid,Yes/NoNewPerspectivesonMicrosoftOfficeAccess200714XPXPInsertaField•InsertafieldbetweenInvoiceDateandInvoiceAmt.•InvoiceItem,Text,size=40NewPerspectivesonMicrosoftOfficeAccess200715XPXPAddingaFieldBetweenTwoExistingFields•IntheTablewindowinDesignview,selecttherowforthefieldabovewhichyouwanttoaddanewfield•IntheToolsgroupontheTableToolsDesigntab,clicktheInsertRowsbutton•Definethenewfieldbyenteringthefieldname,datatype,optionaldescription,andanypropertyspecificationsNewPerspectivesonMicrosoftOfficeAccess200716XPXPChangeFieldPropertiesforContractTable•ContractNumfield,changefieldsizeto4•CustomerIDfield,changefieldsizeto5•ContractTypefield,changefieldsizeto75NewPerspectivesonMicrosoftOfficeAccess200717XPXPAddingaFieldBetweenTwoExistingFieldsNewPerspectivesonMicrosoftOfficeAccess200718XPXPImportingDatafromanExcelWorksheet•Theimportprocessallowsyoutocopythedatafromasourcewithouthavingtoopenthesourcefile•ClickExternalDataontheRibbon•ClicktheExcelbuttonintheImportgrouptostartthewizardNewPerspectivesonMicrosoftOfficeAccess200719XPXPImportingDatafromanExcelWorksheetNewPerspectivesonMicrosoftOfficeAccess200720XPXPImportingDatafromanExcelWorksheetNewPerspectivesonMicrosoftOfficeAccess200721XPXPImportingDatafromanExcelWorksheetNewPerspectivesonMicrosoftOfficeAccess200722XPXPCreatingaTablebyImportinganExistingTableStructure•MakesuretheExternalDatatabistheactivetabontheRibbon•IntheImportgroup,clicktheAccessbutton•ClicktheBrowsebutton•Navigatetothefile•MakesuretheImporttables,queries,forms,reports,macros,andmodulesintothecurrentdatabaseoptionbuttonisselected,andthenclicktheOKbutton•ClicktheOptionsbuttonNewPerspectivesonMicrosoftOfficeAccess200723XPXPCreatingaTablebyImportinganExistingTableStructureNewPerspectivesonMicrosoftOfficeAccess200724XPXPDeletingFieldsfromtheCustomerTable•DeletethefollowingfieldsfromtheCustomertable:CellPhoneFaxNumberCountyWebPageNotesNewPerspectivesonMicrosoftOfficeAccess200725XPXPDeletingaFieldfromaTableStructure•InDatasheetview,selectthecolumnheadingforthefieldyouwanttodelete•IntheFields&ColumnsgroupontheDatasheettab,clicktheDeletebuttonor•InDesignview,clickintheFieldNameboxforthefieldyouwanttodelete•IntheToolsgroupontheTableToolsDesigntab,clicktheDeleteRowsbuttonNewPerspectivesonMicrosoftOfficeAccess200726XPXPDeletingaFieldfromaTableStructureNewPerspectivesonMi