MS Access - a relational database management system tool

Creating a New Database

Start Access

[MSAccess 2007 start screen]

When Access is first opened, a dialog box will appear. To create a new database, select New Blank Database. You have several choices. But when you try to create one, Access first prompts you for a name, and for a place to save your file.

Name your databases something useful. Don't use Database1.accdb because they will be difficult to find if someone else has used the same default names. Press Create and the Database Window appears.

Microsoft Office Access 2007 creates files in a new file format that cannot be opened with earlier versions of Access.

[top]

Objects in the Access Database Window

Continuing with our color schema in which we use
terms relevant to entity relationship models
and their correlates in Access
and, when appropriate, their correlates in Excel

[MSAccess 2007 database objects]

an Access Table (an entity set or a worksheet) is a collection of data about a specific topic

  • Tables organize and store data into
    entities in Entity Relationship Models
    records in Access
    rows in Excel.
    and
    attributes in Entity Relationship Models
    fields in Access
    columns in Excel.
  • all databases contain one or more tables
type ⇒⇒⇒⇒⇒⇒ ⇓⇓⇓⇓⇓⇓
ERModel entity set composed of entities which have attributes
Access tables composed of records which have fields
Excel worksheets composed of rows which have column cells

a Query is generated to view, change, and analyze data from one or more tables

  • Queries retrieve and process your data
  • Queries can combine data from different tables, update your data, and perform calculations on your data

a Form is an object designed primarily for data input and display

  • Forms control data entry and data views
  • Forms provide visual cues that make data easier to work with

a Report is an object designed for formatting, calculating, printing and summarizing selected data

  • Reports turn the data in your tables and queries into documents for communicating ideas

[top]

Views of each object

Tables

Design View and Datasheet View

[MSAccess 2007 table design view]

An entity set is the list of given entities/records within a given entity class that are currently in the database/table. In Access, an entity set is a table.

Primary and Foreign Keys

  • a Primary Key is a set of attributes/fields that uniquely identifies an entity/a record.
  • a Foreign Key is a copy of the Primary Key of one entity class that appears as
    an attribute in another entity class
    a field in another table
    and helps define the relationship between
    entities
    records
    in the two
    classes
    tables
  • these keys link together the related entity classes/tables in a relational database

Queries

Design View, Datasheet View and SQL (Structured Query Language) View

[MSAccess 2007 query SQL view]

Forms

Design View, Form View and Datasheet View

[MSAccess 2007 forms view]

Reports

Design View, Print Preview and Layout Preview

[MSAccess 2007 reports view]

[top]

Plan the Database First

  1. Determine the need or purpose of the database
  2. Decide which kinds of things (the tables) the database should include
  3. Specify the fields that comprise the tables and determine which fields in each table contain unique values
  4. Decide how the tables are related to each other, then review and finalize the design
  5. Construct and relate the tables and populate the database with sample data
  6. Create forms, queries, and reports to use with the data

Access Database Design Help

[top]


entity relationship models | ACCESS - A RDBMS | create a database | next session