Create tables in Table Design view Modify field properties and field descriptions Define primary keys Modify table structure Insert delete and rename fields Create tables in Datasheet view Identify the purpose and nature of tables ID: 743333
Download Presentation The PPT/PDF document "Lesson 2: Building Tables" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Lesson 2: Building TablesSlide2
Create tables in Table Design view
Modify field properties and field descriptionsDefine primary keysModify table structureInsert, delete and rename fieldsCreate tables in Datasheet view
Identify the purpose and nature of tablesPlan tables for a databasePlan table fieldsUnderstand naming conventionsDraw a model of a tableUnderstand data typesUnderstand how the Field Size property affects storage requirements
© CCI Learning Solutions Inc.
2
Lesson ObjectivesSlide3
Modify the layout of a datasheet
Create relationshipsCreate lookup fieldsAdd records to related tables using sub-datasheets
Use Quick Start fieldsUse Application PartsUse User TemplatesEnter records in Datasheet viewImport data into a new tableAppend records to an existing tableImport data as a linked table© CCI Learning Solutions Inc.3
Lesson ObjectivesSlide4
A table is a collection of data about a specific topic
The data is organized into rows and columnsEach column is a field and each row is a record
© CCI Learning Solutions Inc.4Table BasicsSlide5
Remember that a table should store data about one thing, and only one
thingA well-planned database ensures that data is complete, unique and accurateBe sure to have a clear picture of how the database will be usedBegin by listing the data you want to captureThe subject matter categories that are generated in your list will become the tables
© CCI Learning Solutions Inc.5Planning TablesSlide6
Use the smallest fields that make sense – this allows you great flexibility for sorting and searching the data
Remember that it is easier to build in flexibility during the design stage than it is to redesign tables after records have been added or queries and reports have been created based on table data
© CCI Learning Solutions Inc.6Planning FieldsSlide7
Naming Rules and Conventions
A table name can be up to 64 characters longA field name can be up to 64 characters longAvoid using special characters (punctuation marks, slashes, backslashes, etc.) in table names, field names or other object namesEmbedding spaces into a table name or field name can lead to complications in creating expressionsUse camel case, or an underscore (_) to represent a spaceUse meaningful namesEach table within a database must have a unique name
Each field within a table must have a unique name© CCI Learning Solutions Inc.7Slide8
The Information Engineering (IE) model is a standard model for representing table structure
In the IE model, a table is represented by a rectangleThe name of the table appears at the top of the rectangleEach field is listed beneath the table name
© CCI Learning Solutions Inc.8A Model of Table StructureSlide9
Data Types
The properties of a field describe the characteristics and behavior of the data entered into that fieldA field’s data type determines the type of data the field can storeA field can contain only type of dataYou set the data type for a field in Table Design view© CCI Learning Solutions Inc.
9Slide10
Use the upper section of the Table Design view window to specify field names, data types and descriptions
The field name and data type are both required attributesUse the lower section of the window (the Field Properties pane) to specify additional properties
© CCI Learning Solutions Inc.10Data Types – DefiningSlide11
Data Types in Access 2013
Short Text
Stores up to 255 characters of text, a combination of text and numbers or numbers that will not be used in calculations
Long Text
Stores a block of text or a combination
of text and numbers (stored as text) longer than 255 characters
Number
Stores a numeric value that is
not a monetary value. Use this type if you will perform calculations on the field
Date/Time
Stores time-based data
Currency
Stores monetary data. Data
stored in this type of field is not rounded off during calculations and the field is accurate up to 15 digits to the left of the decimal point and 4 digits to the right.
AutoNumber
Automatically provides a unique numeric value for anew record
when it is added to a table. This data type is used in ID fields.
Yes/No
Stores
True or False values. A field with this data type can contain only one of two values: Yes/No, True/False or On/Off.
© CCI Learning Solutions Inc.
11Slide12
Data Types in Access 2013
OLE Object
Used to attach an Object Linking and Embedding (OLE) object to a record. Use the Attachment data type to attach most types of files that do not require OLE. OLE fields do not allow you to attach multiple files to a single record.
Hyperlink
Used to store a hyperlink (up to 2048 characters), such as an
e-mail address or a Web site URL.
Attachment
Used to attach multiple files (such as images, sound files, Excel spreadsheets or Word documents) to a record.
Calculated
Performs calculations on fields within the table. A calculated field cannot perform calculations that involve fields from other tables.
Lookup Wizard
Displays either a list of values that is retrieved from a table or query, or a set of values you specify when you create the field.
© CCI Learning Solutions Inc.
12Slide13
Field properties control how a field’s data is stored, handled or displayed
The data type of the field determines which field properties are available for that fieldThe Field Size property specifies the maximum size for a field, and directly affects the storage and memory requirements for each record
The Field Size property is accessible in the Field Properties pane of the Table Design window© CCI Learning Solutions Inc.13Field Size PropertySlide14
Field Size Property for a Text Field
The default field size for a text field is 255 charactersSpecifying a field size for a text field limits the number of characters that can be stored in the fieldIf you know that a field will store only a limited number of characters, for example, a State field would store only two letters, change the field size for the State field to 2 to give users a visual clue of what type of data is expected
© CCI Learning Solutions Inc.14Slide15
Field Size Property for Number and AutoNumber Fields
Byte
Stores whole
numbers for 0 to 255 in 1 byte of storage
Integer
Stores whole numbers between -32,768
and 32,767 in 2 bytes
of storage
Long Integer
Stores whole numbers between -2,147,483,648 and 2,147,483,647 in 4 bytes of storage
Single
Stores numbers from -3.4 x 10
308
to 3.4 x 10
308
in 4 bytes of storage. Allows fractions and provides decimal precision to 7 digits.
Double
Stores numbers from -1.798 x 10
308
to 1.798 x 10
308
in 8 bytes of storage. Allows fractions and provides decimal precision to 15 digits.
Replication ID
A 16-byte field used to establish a unique identifier for replication
Decimal
Stores numbers from -9.999 x 10
27
to +9.999 x 10
27
in 12 bytes
of storage
© CCI Learning Solutions Inc.
15Slide16
Table Views
Datasheet view – used primarily for working with table data (although you can create tables in Datasheet view). This is the “open” view for a tableDesign view – used to create, view or edit a table’s structure. You cannot see the data in Design view.© CCI Learning Solutions Inc.
16Slide17
Creating Tables in Design View
The Table Design view window has an upper and lower paneType field names, data types and an optional description in the upper pane
Set field properties in the Field Properties pane (the lower pane)© CCI Learning Solutions Inc.17Slide18
Modifying Tables in Design View
You can use Table Design view to change field names, change field types and change field propertiesYou can also add fields, delete fields, insert fields or rearrange fields© CCI Learning Solutions Inc.
18Slide19
Primary Keys
A primary key is a field (or combination of fields) that uniquely identifies each record stored in a tableNo two records in a table may be exact duplicates – two records may, however, contain duplicate data other than the primary keyPrimary key must be uniquePrimary key cannot contain a null value© CCI Learning Solutions Inc.
19Slide20
Selecting a Primary Key
A primary key should beA value that will never changeA value that is extremely unlikely to be nullPoor choices include: people’s names and social security numbersGood choices include unique identifiers issued by an organization or
an ID fieldWhen you use an ID field as a primary key, you can manually enter the ID number or you can elect to have Access create ID numbers automatically by setting the data type to AutoNumberTo specify a field as a primary key select the field in Table Design view, then click the Primary Key command button in the Ribbon© CCI Learning Solutions Inc.20Slide21
Creating Tables in Datasheet View
Use the commands on the Fields tab in the Ribbon when creating tables in Datasheet viewClick any command button in the Add & Delete group to add a fieldClick the More Fields button to display a list of field types and formatting options
© CCI Learning Solutions Inc.21Slide22
Reusing Design Elements
Access 2013 provides several tools that allow you to use (and reuse) pre-defined design elements. These include:Quick Start fields Application partsReusing design elements allows you to create tables and related database objects quickly and easily.
© CCI Learning Solutions Inc.22Slide23
Using Quick Start Fields
Quick Start fields are listed at the bottom of the More Fields listUse Quick Start fields to add pre-defined fields to a
tableEach Quick Start field is automatically defined with a name, data type and formatting (if applicable)© CCI Learning Solutions Inc.23Slide24
Application Parts add functionality to a database
An application part may consist of a single table, or it can comprise several related objects such as tables, forms and queriesClick the Create tab in the Ribbon, then click the Application Parts command button to view the gallery of available objects© CCI Learning Solutions Inc.
24Using Application PartsSlide25
Using User Templates
You can save parts of a database as template objects. You can save tables and other objects as user-defined application parts (*.accdt), and
You can save selected fields as user-defined data type templates (*.accft). You use a user-defined data type just as you would use a Quick Start field© CCI Learning Solutions Inc.25Slide26
User-Defined Data Type Templates
To save a user-defined data typeopen a table in Datasheet viewselect the fields that you want to save for reuseclick the More Fields button
select Save Selection as New Data Type Access saves the new data type template in the following location:Users\<Username>\AppData\Roaming\Microsoft\Templates\AccessOnce you create a user-defined data type, you can select it in the More Fields drop-down list© CCI Learning Solutions Inc.26Slide27
User-Defined Application Parts Template
User-defined application parts templates appear in the User Templates section at the bottom of the Application Parts galleryTo save a database as a template (with or without data)Open the database you want to save as a templateStrip out the objects you do not want to include in the application partSwitch to Backstage view
Click the Save As tabIn the Save Database As section, select Template (*.accdt)Click the Save As command buttonClick the Application Part checkboxIdentify a primary table if necessary© CCI Learning Solutions Inc.27Slide28
Importing Data
You can import data from:Access databasesSQL Server databasesExcel spreadsheetsText filesXML filesHTML documentsOutlook folders
SharePoint listsYou can import data to:A new tableAn existing tableA linked table© CCI Learning Solutions Inc.28Slide29
Importing Tables from Other Databases
Click the External Data tabIn the Import & Link group, click Access to launch the Get External Data WizardSpecify a source database fileSelect the database objects you want to importClick OK
© CCI Learning Solutions Inc.29Slide30
Importing Data from Excel
Click the External Data tabIn the Import & Link group, click Excel to launch the Get External Data WizardSpecify a source database fileSpecify whether you want to:Import source data to a new table
Append a copy of the records to an existing tableCreate a linked tableFollow the instructions on the remaining wizard screens© CCI Learning Solutions Inc.30Slide31
Importing Data to a New Table
Access creates the new table during the import processIf a table of the same name already exists, it will be overwrittenImported data is copied from an outside source – it will not be automatically updated if the source file is updatedChanging data in the Access table does not affect the source file© CCI Learning Solutions Inc.
31Slide32
Appending Records to an Existing Table
Use the Get External Data wizardThe append operation does not overwrite data in the existing tableThe structure and data types in both the source and destination files must match© CCI Learning Solutions Inc.
32Slide33
Importing Data as a Linked Table
Each time the source file is updated, the linked table automatically reflects the most recent changesIn most scenarios, linking allows you to add and update data either in the source file or in the linked table. Exceptions are as follows:When you link to data in a text file, you can add new records in Access, but not update recordsWhen you link to an Excel workbook or a Data Services file, the linked table is a non-updatable recordset. If you want to add, edit or delete data, you must make the changes in the source file.
© CCI Learning Solutions Inc.33Slide34
Working in Datasheet View
© CCI Learning Solutions Inc.
34Slide35
Working with Records – Navigating a Datasheet
© CCI Learning Solutions Inc.35Slide36
Adding, Updating and Deleting Records
Use the arrow keys, the Tab key and the mouse to move from field to fieldAs you enter new or revised data into a table record, a pencil icon displays in the record selector to indicate that the record contains unsaved changesWhen you reach the end of a record, you can press Tab or Enter to automatically save the current record and move the cursor to the next recordYou can also press Ctrl+S to save changes from anywhere in the datasheetYou must confirm deletions
© CCI Learning Solutions Inc.36Slide37
Manipulating a Datasheet
Size and Position Datasheet ColumnsHide or Unhide ColumnsResize FieldsRearrange FieldsFreeze and Unfreeze FieldsCustomize the Appearance of Datasheet DataAdd table descriptionsChange field captions
Insert total rowsChange data formats© CCI Learning Solutions Inc.37Slide38
Printing Datasheet Records
In Backstage view, click the Print tab, then select:Quick Print PrintPrint Preview
© CCI Learning Solutions Inc.38Slide39
Constraining Input – Validation Rules
A validation rule is a criterion that must be met before a record can be savedEnter the criterion in the Validation Rule row in the Data tab of the Property SheetYou can specify optional validation text that will display as an error message if the validation rule is not met© CCI Learning Solutions Inc.
39Slide40
Constraining Input – Input Masks
An input mask is a string of characters that indicates the format of valid input values for a fieldInput mask has 3 parts:First part (mandatory) – includes the mask characters along with placeholders and literal data such as parentheses, periods and hyphens.Second part (optional) – controls whether the embedded mask characters are stored within the field. If the second part is set to 0, the characters are stored with the data. If it is set to 1, the characters are displayed in the field but are not stored as part of the data.Third part (optional) – indicates a single character or space that is used as a placeholder. By default, Access uses the underscore (_) character, but you can specify the character you want to use.
© CCI Learning Solutions Inc.40Slide41
Constraining Input
Default ValuesSpecify a default value for a field to speed data entryType the default value in the Default Value row of the General tab in the Field Properties pane in Table Design viewAuto-incrementing FieldsTo set a field to auto-increment, set the data type to AutoNumberThere can be only one AutoNumber field in a
table© CCI Learning Solutions Inc.41Slide42
Table Relationships
A column in a table that references the primary key of another table is called a foreign keyForeign keys and primary keys represent data relationships
© CCI Learning Solutions Inc.42Slide43
Table Relationships
A relationship is made by matching data in key fields between two tablesWhen creating relationships, remember:Related fields may or may not have the same name but must have the same data typeWhen related fields are Number fields, they must have the same Field Size property settingTo define a relationship using an AutoNumber primary key field, the foreign key must be a Number field with a Field Size property setting of Long Integer
© CCI Learning Solutions Inc.43Slide44
Relationship Types
One-to-one
A relationship in which each record in Table A can have only one matching record in Table B, and vice versa.
One-to-many
Parent-child
A relationship in which a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A. The relationship is established only if the common field is the primary key in Table A and the foreign key in Table B.
Many-to-many
A relationship in which one record in either Table A or Table B can relate to many matching records in the other table. Relational databases cannot directly handle many-to-many relationships, and these must be replaced by multiple one-to-many relationships.
© CCI Learning Solutions Inc.
44Slide45
Representing table relationships
In an IE diagram, you represent relationships by drawing lines between the key fields The “one” side is indicated by drawing a 1 beside the parent keyThe “many” side is indicated by drawing an infinity symbol (
beside the foreign key © CCI Learning Solutions Inc.
45Slide46
Creating Relationships in a Database
Open the Relationships windowAdd tablesDrag the primary key field from the parent table onto the foreign key field in the related table to open the Edit Relationships dialog box
© CCI Learning Solutions Inc.46Slide47
Option 1 is an INNER join. This is the default join type – it includes rows where the joined fields are equal
Option 2 is a LEFT OUTER joinOption 3 is a RIGHT OUTER join© CCI Learning Solutions Inc.
47Table Relationships – Join PropertiesSlide48
Option 1 is an INNER join. This is the default join type – it includes rows where the joined fields are equal
Option 2 is a LEFT OUTER joinOption 3 is a RIGHT OUTER join© CCI Learning Solutions Inc.48
Table Relationships – Join PropertiesSlide49
When referential integrity is enforced …
You cannot add a record to a related table unless a matching record already exists in the parent tableYou cannot change the value of the primary key in the parent table if matching records exist in a related tableYou cannot delete a record from a parent table if matching records exist in a related table© CCI Learning Solutions Inc.
49Slide50
Creating Lookup Fields
Lookup fields create relationships within a databaseA lookup field in a child table displays values stored in the parent tableUse the Lookup Wizard to create a lookup field – the Lookup Wizard writes a SQL statement to display values in the lookup field© CCI Learning Solutions Inc.
50Slide51
Viewing Related Data
When you define relationships, you can see related data in a sub-datasheet in Datasheet view© CCI Learning Solutions Inc.51Slide52
Identify the purpose and nature of tables
Plan tables for a databasePlan table fieldsUnderstand naming conventionsDraw a model of a tableUnderstand data typesUnderstand how the Field Size property affects storage requirementsCreate tables in Table Design viewModify field properties and field descriptionsDefine primary keys
Modify table structureInsert, delete and rename fieldsCreate tables in Datasheet view© CCI Learning Solutions Inc.52Lesson SummarySlide53
Lesson Summary
Use Quick Start fieldsUse Application PartsUse User TemplatesEnter records in Datasheet viewImport data into a new tableAppend records to an existing tableImport data as a linked table
Modify the layout of a datasheetCreate relationshipsCreate lookup fieldsAdd records to related tables using sub-datasheets© CCI Learning Solutions Inc.53Slide54
Review Questions
For a relationship between fields in two tables to be created, the fields must:have the same data type.
have the same name.be stored in separate databases.be defined as lookup fields.Which of the following is the smallest Field Size setting that will allow a number field to store the value 1,452,137.5?IntegerLong Integer
Single
Double
© CCI Learning Solutions Inc.
54Slide55
Review Questions
What is the default Field Size setting for a Short Text field in Access?12Single
Double255A primary key:should use social security numbers whenever possiblecannot be a composite key.should be null whenever possible.
should be a value that will never change.
© CCI Learning Solutions Inc.
55Slide56
Review Questions
Kevin needs to update a field in a table that is linked to data in an Excel workbook. He should:delete the source data file so that it does not interfere with the linked table.
adjust the value directly in the linked table.adjust the value in the source data file.run the linked table update manager.© CCI Learning Solutions Inc.56