Week 7: Access Chapter 1 & 2

Week 7:Access Chapter 1 & 2 DivyaGanesan divya.ganesan@mail.wvu.edu February 24, 2014

Creating Tables • Create fields in Design View • Import data from another database or application • Examples: Excel spreadsheets or XML • Enter data directly into rows in Datasheet view

Creating Fields in Tables • Field names should be meaningful • Rules for naming fields: • Length can be up to 64 characters • Can include letters, numbers and spaces • Access uses CamelCase notation • Use uppercase letters for each first letter of each new word • Example: LastName, FirstName

Field Data Types • Every field has a data type • Determines: • The type of data that can be entered • The operations that can be performed on that data • Access recognizes 10 data types

Access Data Types • Number • Text • Memo • Date/Time • Currency • OLE • AutoNumber • Hyperlink • Attachment • Yes/No

LookUP Fields • A lookup field can display a value that is bound to another value in a source data table or value list. • lookup list • The field gets its data from an existing table or query in the database. • The tables are related and when the values in the data source changes, the current data are available in the lookup field. • lookup value list • The lookup field gets its data from a list of values should br typed in by the user when the field is created. • Used when there are limited set of values that do not change often.

Working with Field Properties • Field property • the characteristics of a field that determine how the field looks and behaves • Caption property • a more readable label that will appear in the top row in the Datasheet view and in forms and reports. • It overrides the field name. • Validation rule • ensures that the correct type of data is entered or that the data does not violate other enforced properties.

Other Field Properties • Field Size • Format • Input Mask • Default Value • Validation Text • Required • Allow Zero Length • Expression • Result Type • Indexed

Indexing to Retrieve Data Quickly • Primary Key is always indexed with No Duplicates • Provides quick sorting and retrieval of data based on the primary key • For non-primary key fields, it is sometimes beneficial to set the Indexed property to Yes (Duplicates OK).

Importing Data • Import & Link • Brings data into an Access database. • Data sources that can be used include: Excel, Access (from other Access files), ODBC Databases, Text files, XML files, and others. Click Excel to import spreadsheet data External Data Tab Click XML file to import xml data

Importing Data from XML Click Browse to find a xml file

Importing Data from XML Import Options

Importing Data from Excel Click Browse to find a spreadsheet Decide what you want to do with the data

Import Data from Excel (continued) Choose the worksheet to import Preview of the worksheet data Click Next to continue

Import Data from Excel (continued) Column headings Click Next to continue

Import Data from Excel (continued) Change Indexed property to Yes (No Duplicates) Click Next to continue

Import Data from Excel (continued) AID becomes the primary key Click Next to continue

Import Data from Excel (continued) Accounts becomes the table name Click Finish to import the data

Relationships • Relationships • A set of rules describing how tables in a database are related • Can be used to • Efficiently combine data from related tables • Create queries, forms, and reports

Relationships • Relationships should be created after the tables are created, but before any sample data is entered • Most common method of connecting two tables is using a primary key from the primary table to the foreign key in the related table

Foreign Key • Foreign key is a field in one table that is also a primary key of another table CustomerID is the primary key of the Customer Table CustomerID is the foreign key in the Account Table (duplicates are allowed)

Relationships Window • To Open Relationship Window • Click on Database Tools tab in the Ribbon • Select Relationships

Creating Relationships • A common field • Used to relate two tables together • Join lines • Allow relationships between two tables to be created on a common field • Three types of relationships between tables: • Enforce referential integrity • Cascade update related fields • Cascade delete related records

Referential Integrity • Ensures that data cannot be entered into a related table unless it first exists in the primary table • Example: • Banks would not want to offer a loan to an individual unless that individual was already established as a customer of the bank. • Thus, the customer has to be entered into the Customer table, before a new loan can be made in the Loan table with that customer’s ID.

Establishing Referential Integrity • Open Relationships dialog box • Select Enforce Referential Integrity checkbox • prohibits the user from entering a foreign key value in a related table unless the primary key value already exists in the primary table. • prohibits the user from deleting a record from one table when there are related records in other tables.

Set Cascade Options • Cascade Update Related Fields • when the primary key is modified in a primary table, Access will automatically update all foreign key values in a related table. • Cascade Delete Related Records • when the primary key is deleted in a primary table, Access will automatically delete all records in related tables that reference the primary key.

Backing Up a Database • Backups are critical to a database • To back up files, click the File tab, then click Share from the list of options. • From this list of options, click on Back Up database and the Save As dialog box will open. • Access provides a default file name, including the original file name and adds on the current date • Default extension for Access databases is accdb

Questions?

Week 7: Access Chapter 1 & 2