Return to IFS 105 Notes!

Access
Project 1

Creating a Database Using Design and Datasheet Views

What is Access 2003?

• Data entry and update - adding, changing, and deleting data, including mass changes in a single operation

• Queries - receive instant answers to complex question based on the data in a database

• Forms - produce attractive and useful forms for viewing and updating data

• Reports - create sophisticated reports for presenting your data

• Web Support - allows saving of objects (tables and reports) in HTML format to be viewed by a browser. Can create data access pages to allow real-time access to data in a database via the Internet.

Creating a Database

• Database - collection of tables in Access
• DBMS (Database Management System) - software allows you to create and manage data in a database
• Note: In some DBMSs every table, query, form, or report is stored in a separate file, in Access all these objects are saved in a single .MDB file.
• Access File Formats - by default Access 2003 creates a new database in Access 2000 format

 Terms of a Database

Table - group of records (relations or files) Record - group of related fields (row in table) Field - a single item in record (column in table) Primary key (unique identifier) - will appear in only a single record in the table

 2 Parts of Database Table

1. Structure - field names, types, descriptions (Created in Design View)
2. Records - data for the fields (added in Datasheet View)

Speech Recognition and Speech Playback

• Office Speech Recognition software - when installed with microphone, can speak names of toolbar, buttons, menus, menu commands, list items, alerts, and dialog box controls, such as OK and Cancel. Can also dictate field entries, such as text and numbers.
• Language bar - used to indicate whether to speak commands or dictate field entries

2 ways to display Language bar

1. click Language Indicator button in taskbar tray status area, click Show the Language bar on menu
2. click Tools on menu, point to Speech command, click Speech Recognition command on the Speech submenu

Creating Structure

1. Field name - unique name
2. Data type - type of data (Numeric, Character, Currency)
3. Description - detailed documentation

Rules Field Names

1. Max length 64 characters
2. Contain letters, digits, spaces, most punctuation
3. Cannot contain periods, exclamation points, or square brackets [ ]
4. Same name cannot be used for 2 different fields in same table

Data Types

1. Text - any characters
       default width is 50 characters
2. Number - only digits (positive / negative) arithmetic operations
3. Currency - only dollar amounts (displayed with dollar signs, commas, and 2 decimal places)

Note: Access automatically sets field size for numeric and currency fields

 Correcting Errors in Structure

BACKSPACE - correct error at cursor position Point to entry - click left mouse button, retype Added an extra field - select it use DELETE key Forgot a field - use Insert Row from Edit menu Wrong field key field - point to Key entry for field, click mouse, click Set Primary Key button

 View Data in Table 2 Ways

1. Datasheet view - table represented as a collection of rows and columns
2. Form view - displays a single record at a time
   note: forms are not restricted to data from a single table, can incorporate data from multiple tables as well as special types of data like pictures and sounds

• Navigation buttons - bottom of screen used to move between records in database

 Correcting Errors in Data

• BACKSPACE - correct errors at cursor position
• Add Extra Record - select it, press DELETE
• Access refuses new record - duplicate primary key
  • Esc key to remove record from screen trying to add

Designing a Database

• Database design - refers to arrangement of data into tables and fields
• Simple principle: Design to remove redundancy
• Redundancy - means storing the same fact in more than one place

Redundancy Problems

1. Wastes Space on Disk
2. Updating difficult
3. Possibility of inconsistent data exists

Solution: place redundant data in separate table

In the Lab 2 (HOMEWORK #7)

• Page AC 59&60

End

Creating a Database Using Design and Datasheet Views

Computer Assignment 7

Return to IFS 105 Notes!