Transcript Database Tables, Forms

Databases,Tables and Forms
Access Text by Grauer
Chapters 1 & 2
Introduction to a Database




Defining a database
Creating a database in Access
Creating forms
Using forms to filter data
Database


Provide a way to store
a related set of data
Simplest form is a
card file



Each card a record
Each item a field
Data may be text,
dates, numbers, web
links, pictures, etc.
Name: ________________
Address: ________________
City: ________ State: ___ Zip: ______
Member since: ______________
Dues paid? ______
Two Types of Database

Flat File - Spreadsheet

Works well until data repeats


Must enter data repeatedly
Data must match exactly
Two Types of Databases
Relational – 2 or more linked tables
Key Field
Customers
Customer ID
Name
Address
City
State
Zip
Link
Invoices
Invoice No.
Part No.
Unit Price
Units
Customer ID
One-to-Many Relationship
Many-to-Many
Relationship
Parts
Part No.
Description
Supplier
Units In Stock
Database Window
Menu Bar
Toolbar
Database
Window
Object
Buttons
Creating a Table

Using Table Wizard

Designing a table yourself





Field names
Data types
Primary key
Properties
Views--Datasheet or Design
Design View
Primary Key
Field Names
Data Type
Description
Field
Properties
Data Types









Number
Text
Memo
Date/Time
Currency
Yes/No
AutoNumber
OLE
Hyperlink
Properties





Validation
Field
size Rule
Format Text
Validation
Input Mask
Required
Caption
Allow
Zero Length
Default Value
Indexed
Format vs. Input Mask

Format - Defines how data will be displayed




Data stored: 123456789
Format: ###\-##\-####
Displays: 123-45-6789
Input Mask - Displays formatting for input



Causes format to appear on forms, tables
Input Mask: SSN 000-00-0000
Form field shows: SSN ___ - __ - ____
You fill in the underscored blanks
Only the info in the blanks is stored
Table Design Basics



Include all necessary data
Use descriptive names
Store data in smallest parts



Will you ever need to sort by part?
Store address, city, state, zip separately.
Don’t store info based on other data


Don’t store “Age” – age changes!
Store DOB, calculate Age
Datasheet View
Triangle indicates
data has been
saved to disk
Each column is
a field
Each row is
a record
Current Record
Total Number of Records
Database Forms



Each page shows
one record
Field data appears
in text boxes
Other controls can
be added as in
Excel user forms
Creating a Form






Controls--objects
Bound control has data source
Unbound control: no data source
Calculated control: expression as data
source
Properties
Form Wizard
Form Wizard
Click and drag to
move Toolbox
Sizing handles
Label (unbound)
Text Box
(bound to field)
Go To
First Record
Go To Next Record
Go To Last Record
Advanced Forms
Advanced forms
use other controls
Combo Box
(Drop Down List)
Command Button
Check box
Option Group
Filter by Form


No query needed
Allows user to define a limited subset