Building The Database

1
Building The Database
Chapter 2
It is only the farmer who faithfully plants
seeds in the Spring, whoreaps a harvest in the
Autumn.
BC
Forbes
2
Level 2 ObjectivesPopulating and Relating the
Database Tables

• Import data into a database
• Set a tables primary key
• Create foreign keys
• Create one-to-many and many-to-many relationships
• Use a subdatasheet to view related records in a
  table

3
Populating the Database Tables

• Populating database
• Load tables with data
• Enter data in datasheets
• Import data

4
Copying Records from One Table to Another

• Import existing data from another table
• Table structure must be identical
• Select all of records
• Click copy button
• Click edit on menu bar
• Click paste append
• Correct errors with AutoNumber field record
  numbers that should be incremented sequentially
• Delete AutoNumber field from table
• Add it back

5
Importing Data from an Excel Workbook

• Can import data and create table at same time
• Review contents of workbook to understand how it
  is arranged
• If column heading names comply with rules for
  naming fields in access
• Access uses them as field names data imported
• If column headings absent or do not comply
• Access assigns generic field names

6
Importing Data from an Excel Workbook (continued)

• Access can import most data from worksheet
• Not graphics
• Formulas converted to numbers
• Hyperlinks imported as text data
• Usually entire worksheet imported
• Can import range of data

7
Import Spreadsheet Wizard
8
Setting a Primary Key Field
9
Working with Primary and Foreign Keys

• Primary key and foreign key counterparts
• Must have same data type and field size
• Fields must contain identical values
• Primary key value must exist before entering
  corresponding record
• If referential integrity set
• User cannot enter null value into primary key
  field
• Required property
• Nonprimary key field
• Ensure that users enter value into field

10
Working with Primary and Foreign Keys (continued)

• Works to make data retrieval faster
• Access creates index for primary key field
• Index
• List maintained by database
• Associates field values in indexed field with
  records that contain field values

11
Creating an Index

• Increase speed at which access searches
• Open table in design view
• Select field to index
• Click indexed property
• Create index for any field
• Except fields of type
• Memo
• Hyperlink
• OLE object

12
Creating an Index for a Nonprimary Key Field
13
Creating an Index

• View indexes created in table
• Click indexes button
• Create indexes for as many fields as necessary to
  optimize searches in database
• Records indexed when table saved
• Updated automatically as records added deleted,
  or changed
• As database grows
• Indexes might slow down database

14
Creating an Index (continued)

• Increases size of database
• Slows down database
• Must update index as users add change and delete
  records
• Add indexes as needed
• When improved performance necessary
• Delete indexes to
• Increase speed
• Reduce file size

15
Creating One-to-Many Relationships Between Tables

• Open relationships window
• Click relationships button
• Relationship has certain properties
• Type
• One-to-many
• One-to-one
• Many-to-many
• Attributes
• Specify how to manage changes when records
  updated or deleted

16
Relationships Window
17
Edit Relationships Dialog Box
18
Creating a Many-to-Many Relationship Between
Tables

• Use junction table to create many-to-many
  relationship
• Create 1M relationships between both tables and
  junction table

19
Using a Subdatasheet to View Related Records

• After importing data into database
• Good idea to open each table in datasheet view
• Check data for problems
• Plus box appears for relationships
• Click to view subdatasheet

20
Level 2 Summary

• Import data from
• Other Access tables
• Excel
• Index
• Used to aid searches
• Create relationships using Relationships Window