Transcript Access2007_TablesPartI

ACG 4401
Access 2007
Tables Part I
Datasheet vs. Design View
Creating the Database

Start Access


Getting Started Page Appears
Choose Blank Database

Type in filename



Extension will be .accdb
Choose your folder
Click Crate Button
Backing up the Database

Office Button

Manage

Back Up Database
What the database file
contains?

Tables






New database starts with one (blank)
Forms
Reports
Queries
Macros
Modules
Creating Tables

Datasheet View

Fast and Easy



Drawbacks


Similar in Appearance to Excel
Columns become fields
Data types are “guessed” by Access
Design View



Full Control over each field in your table
Full control over Data Types
Full control over other Field Properties
Datasheet – Creating Table




Select your table (or create a new one)
Enter data in each column
Use a different column for each field
Add Column Names



These will be the names of your fields in
your table
Double-click the column heading and edit
Save your table
Multiple Datasheets


Each table will appear as a Tab
(Access 2007)
Each table will appear as a separate
Window (Access 2003 or earlier)
Design View

Complete Control over the structure of your Tables

Fields/Attributes



Data Types
Other Data Properties
Used to:

Add Fields

End of Table


Between Fields


Move to the row below where you want the new field

Right-click, Choose Insert Rows
Rearrange Fields


Move to last row of field list
Just drag and drop
Delete Fields
Fields Descriptions

Description will appear in the Status
Bar

Control: Provide prompting/feedback to
users
For Each Field




Enter Field Name
Enter Data Type
Enter Description (optional)
Enter/Change Data Type Properties
What are the Data Types










Text
Memo
Number
Currency
Date/Time
Yes/No
Hyperlink
Attachment
AutoNumber
OLE Object
Text


Any Character accepted
Limited to 255 characters


~ 1 paragraph
Properties:

Text Length


For most fields use 50
If you know exact length (e.g. SS#) use the
exact length (e.g. 9)
Memo

Used to store a large amount of
Unformatted text



65k worth of data
~ 1 average book chapter
To Edit large blocks of text (or
anything)

Shift-F2
Number

Field Size depends on Number Type

Byte



Integer





+/- up to 38 zeroes
Decimals to 7 places
Double



-2,147,483,648 – 2,147,483,648
No decimals
Single (Best choice for decimals)


-32,768 – 32,768
No decimals
Long Integer (Access Default)


0-255
No decimals
+/- up to 308 zeroes
Decimals to 15 places
Decimal


+/- up to 28 zeroes
Decimals to 28 places
Currency

Most Efficient for Financial
Calculations
Date/Time


Access stores the Date and Time
Choose how it’s Displayed in the
Format Field
Yes/No

Any field that can be one of two states




Yes/No
On/Off
True/False
Male/Female
Hyperlink



URL
File Address
Email Address
Attachment

Access Stores the file inside the
database




Pictures
Music Files
Word Files
Excel Files
AutoNumber


Remember Primary Keys
Access automatically creates this field type
when you crate a new Table


Unless: Create>>Tables>>Table Design
Default is sequential numbering

Two Options


Random #
Replication ID

Guaranteed to be unique

Useful if database is distributed but will later be
merged
Access 2007
Tables Part II
Data Input Controls
Input Control

From Design

Format the Datasheet

Home>Font





Gridlines
Every other Row Color
Fonts
Text Color
Background
Data Entry Controls

Preventing Blank Fields







Required Property
Allowed Zero Length
Preventing Duplicate Values
Default Values
Input Masks
Validation Rules
Lookups
Preventing Blank Fields

For any required field


Change Required Property to “Yes”
But Access considers “ “ to be data

Change Allow Zero Length Property to
“No”

This also works for number field types if you
want do not want to accept “0” as a number
Preventing Duplicate Values

Indexed Property

Sorts a field in your table


Improves Performance when
searching/querying
Three Options



No (Default)
Yes [Allow Duplicates]
Yes [No Duplicates]
Default Values


Eliminates Need for User Input
Use Default Value Property

Same data is usually entered


For Example, Orlando for city
Dynamic default values

Access inputs the data but it changes based on
condition


Date() Today's date is entered
Text default values must be enclosed in “”
Input Masks

Enforce data value pattern

Phone Number

(407) 823-5739


Social Security Number

111-23-7456


(999) 000-0000
999-99-9999
Inventory Item Number

MB678-Z123

>LL999->L999
Input Mask






Open table in Design View
Select field that will have pattern
Choose Input Mask Property
Click on the small ellipse ...
Choose from the Access provided Masks or Create
your Own
Choose how to store the Data

Store only the data


4078235739
Store the data and extra characters

(407) 823-5739
Creating Your Own Mask

Placeholders


Special Characters


Where should the user input data, and what type
of data is allowed
How to treat data that is entered in the mask
Literals

Formatting for the mask

() in phone number, - for SS #
Placeholder Characters
Character
0
9
#
L
?
A
a
&
C
Description
A required digit
An optional digit
An optional digit or + or A required letter
An optional letter
A required letter or digit
An optional letter or digit
A required character of any type
An optional character of any type
Special Characters
Character Description
!
<
Mask is filled left to right
Converts all characters that follow
to lowercase
>
Converts all characters that follow
to uppercase
Password Any character typed in is replaces
on screen with a *
Field Validation Rules

Restrict data allowed



If data matches rule it can be entered
If data does not match rule Access keeps it out
Applying Validation Rule



Open table in Design View
Select field
Go to Validate Rule Property


Type Expression
Type Error Message in Validation Text Property
Writing Expressions

Validating Numbers


<, >, <>, <=, >=, =, Between
Validating Dates

Dates must be entered between ##



#05/23/2008#
<, >, <=, >=
Date Functions


Date () Today’s date, one second after midnight
Now(0 Today’s date and current time


Validating Text



Starts with
Ends with
Contains


<, >
Like Operator and * wildcard or ? Single character
Combining Validation Conditions


And
Or
Validating Text Examples

Start of text


End of text


Like “*4401”
Contains text anywhere


Like “ACG*”
Like “*A*B*”
Contains text specific place

Like “????7Y2”


Like “####7Y2”


if 1st 4 characters are text
if 1st 4 characters are numbers
Restrict character to certain letters or symbols

Like “[AE]*”


Must begin with A or E
Like “[ABCD]*” or Like “[A-D]*”

Must begin with A, or B, or C, or D
Table Validation Rules


Allows comparison of multiple fields from a table
For example:



Ship Date can NOT be before Order date
Validation Rule = Order Date < Ship Date
To create table validation rule

Open table in Design View




Table Tools > Design > Show/Hide > Property Sheet
Set Validation Rule
Set Validation Text
field names must appear between []


[DateOrdered] < [DateShipped] or
[DateOrdered] <= [DateShipped]
Lookups



Helps to standardize data
Enter a value by choosing from a list
Two Types


List has fixed set values
List comes from another table
Fixed Lookup Lists






Open Table in Design View
Go to field which will have lookup
Change Data type to Lookup Wizard
Choose “I will type in the values that I
need”
Enter the values
Choose if lookup can store multiple
values
Lookup List Property

Disable user ability to add to or
override list


Set the Limit to List property = “Yes”
Allow list modification

Set the Limit to List property = “No”
Access 2007
Tables Part III
Linking Tables
Defining a Relationship

Each relationship links two tables


Primary Key from one table
Foreign key from another table


Primary key value from 1st table
Choose:




Database Tools > Show/Hide > Relationships
Add tables that participate in relationship
Drag the field from one table (the one side) to the related field in
the other table (the many side)
Edit relationship

Set Enforce Referential Integrity


Cascade Deletes


Prevents Insert, Deletion or Update anomalies
You don’t want to do this, but...

It deletes every record in all related tables
Cascade Updates

Any changes made in a link field (the one side) are automatically made
in the linked field (the many side)
Lookups with Related Tables




2nd Lookup Option
Data values come from related table
You must remove any defined relationship before doing the
lookup
Same steps as Lookup described earlier








Choose “I want the lookup column to look up values in a table or
query”
Choose the table
Choose the field for the lookup
Choose a field that describes the lookup field
Choose a field to sort
Choose “Hide Key Column”
Name the lookup
Finish
Access 2007
Queries
Types of Queries

Select


Display data that matches criteria
Action Queries



Append data
Delete data
Update data


Matches criteria
All Queries are Objects stored in the
database
Steps to Create Select Query



Create > Other > Query Design
Select table(s)
Select fields you want displayed




Arrange fields from left to right






Best done by selecting in order you want
But you can drag fields around to re-arrange
To Hide a Column


Double click or
Drag
Choosing * field will select all fields
Uncheck the Show checkbox
Choose a field to sort by
Create your Criteria
Run Your Query
Save the Query
Join Queries

Combine two related tables




Just add two (or more) tables to your
Query Design
Create Relationship


Primary key
Foreign key
If relationship doesn’t already exist
Everything else is the same.
Inner Joins vs Outer Joins

Inner Joins

Only shows linked records



Each Inventory item related to a Vendor
Each Sales order related to a Customer
Outer Joins


Show linked records AND
Other records from one of the two linked tables

All Sales order that match a criteria based on
Customer AND remaining Customers not linked to a
Sales Order
Creating Outer Joins


Right Click Join line in Query
Choose:



Only choose rows where joined fields from both
tables are equal (inner join)
Include All records from “xxxxx” table and only
those records from “yyyyy” table where joined
fields are equal (outer join)
Include All records from “yyyy” table and only
those records from “xxxx” table where joined
fields are equal (outer join)
Creating a Calculated Field


Two Parts:
Calculated field name: Expression

Using field names for the table(s)

Enclose in [] and

[TableName.FieldName]
 [Customer.FirstName]
Math Expressions







+
*
/
^ - Exponentiation 22
\ - Integer Division 5/2 = 2
Mod – Modulas 5/2 = 1 (the remainder)
Writing Expressions


Long Expression?
Shift-F2
Expressions with Text

&


Joins or concatenates fields together
Fullname: [FirstName] & “ “& [LastName]
Query Functions

Function Name()


Inside () put in parameters (if any) that
are needed
Expression Builder



Right Click empty field box in Query
Design
Build
Add/Edit the experssion
Formatting Expressions

Format(Number, format)






Currency
Fixed
Standard
Percent
Scientific
Yes/No


No if number is 0
Yes if number is anything else
Math Functions

Sqr()

Square root


Abs()


Absolute Value
Round()


Sqr(9)
Round(number, # of decimal places)
Val()

Converts text field number to number for a
calculation
Text Functions

Ucase(text)


LCase(text)



Starting from end, gets number of characters
Mid(text, startnumber, endnumber)
Trim(text)


Starting from left, get’s number of characters
Right(text, Number)


Changes text to lowercase
Left(text,Number)


Capitlizes text
Removes blank spaces from either side
Len(text)

Counts number of characters
Date Functions



Date()
Now()
DatePart(partofmonth,
date)










yyyy – 4 digit year
q – Quarter 1 to 4
m – month, 1-12
y – Day of year, 1-365
d – Day , 1-31
w – Day of week, 1-7
ww – Week of the year, 152
h – hour, 1-24
n – Minute, 1-60
S – Second, 1-60
DatePart(“m”,
[DatePlaced]=DatePart(“m”
, Date()) And
DatePart(“yyyy”,
[DatePlaced]=DatePart(“yy
yy, Date())
The order was placed in the
same month and year as
today's month and year
Other Date Functions






DateSerial()
DateAdd()
DateDiff()
MonthName()
WeekdayName()
Format()
Blank Values

Finding blank values


Is Null
Nz()

Two Parameters


Value (usually a field from your query)
What to replace that field with if its Null


0 is the default so you can leave this parameter out
OrderItemCost: Nz([Quanity]) * Nz([Price])
Summarizing Data








Count
Sum
Average
Maximum
Minimum
First
Last
Group By
1.
2.
3.
4.
5.
Create Query
Add table(s)
Add fields
Query Tools|Design >
Show/Hide > Totals
Total row appears in
Query Design form
Total Query Quirks

Each field in the design must be:



Used in a Summary Calculation
Used for Grouping (Sorting)
Used for Filtering
Query Parameters



Flexible Queries
User Supplied Criteria
Steps
1.
2.
3.
Create Query
QueryTools | Design > Show/Hide > Parameters
Choose name and data type for parameter
1.
4.
5.
Data type should match the field data type that you
are using the parameter for
OK
Run The Query
Access 2007
Forms
Why Use Forms

Ease of Use




Reviewing
Editing
Data Entry
Input Process Control
A Simple Form

Two Types

Stacked



Each Field is listed top to bottom
One Record per screen (default)
Tabular



Each Field name becomes a Column
Record’s listed in rows
Similar to DataSheet
Create a Simple Form



Select Table
Create > Forms > Form
Changes to the Form:




Arrange field placement (dragging)
Adjust column widths
Edit field header text
Adjust formatting

Fonts, Colors, etc.
Different Form Views

Form View


Layout View


Where you Review, Add, Edit data
See what your form looks like with Live
data
Design View


Total control over the form
No Live Data
The Layout

Container for various controls on the Form:




Widen one field all others are widened
Move the layout all fields are moved
Rearrange a field in the layout, consistent
spacing is maintained
Create > Forms > Form


Creates One Layout container
You might want more to aid in form design
Using Multiple Layouts

Open form in Design View


Open Property Sheet for Form





Resize for to eliminate all Blank Spaces
Choose Form
Format Tab
Default View
Continuous
To do this from the beginning

Create > Forms > Multiple Items
Split Forms

Two Views of Data



Datasheet
Form Layout
Create > Forms > Split Form
Split Form Properties

Orientation


Size


Top, Bottom, Left, Right
Controls size of datasheet
Datasheet

Read Only

Prevents editing to data in the datasheet
Other Form Properties

Source

Where does the data come from


Allow Edits

No


No, Can’t Delete
Allow Additions


Can’t change anything, but can copy
Allow Deletions


Table or Query
No, Can’t add new records
Data Entry


Yes, the form can only be used to enter data
Form opens blank, no data
Access 2007
Advanced Forms
Using Design View
Design View

No Layout




Access Created Container for Fields
More Control Types
More Responsibility
To crate a form in Design View:

Crate – > Forms -> Form Design
Control Types

Label




Displays list from drop-down
box

Displays value of field
Combines with Label


Checked for Yes
Unchecked for No

Depressed for Yes
Not-pressed for No
Line and Rectangle

Use to separate sections on
the form
User Supplied
Table or Query
Automatically uses for:




Displays entire list on form
Tab Control


Lookup fields
Linked tables
List Box

Toggle Button



Check Box


Fixed Text, anywhere
Combo Box
Text Box



Create Tabbed pages on a
form
Subform


A form inside a form
Usually linked records from
related table
Adding Controls to your Form
1.
2.
3.
4.
5.
6.
7.
8.
Design Tools|Design > Control
Select control you want (click it)
Move control to position on form (release
mouse)
Complete Control Wizard (if any)
View Property sheet for Control
Modify property settings
Change name of control
Format control (optional)
Bound vs. Unbound

Bound Control




Displays value from a field
Changes field value
Adds new data to a field
Unbound

Not linked to any field in the table/query
Control Steps
1.
2.
3.
4.
5.
Create a Control
Move a Control
Resize a Control
Modify a Control
Delete a Control
Changing multiple controls

Select group of controls



Sizing


Selection Box
Align to Left, Right, Top, or Bottom edges
To Widest, To Narrowest, To Tallest, To Shortest
Spacing

Design Tools | Arrange > Position




Make Vertical Spacing Equal
Make Horizontal Spacing Equal
Increase Vertical/Horizontal Spacing
Decrease Vertical/Horizontal Spacing
Form Sections

Detail


Most data fields from table/query
Header/Footer



Title
Summary Information
To create


Right click on Form
Choose Page Header/Footer
Tab Order

Tab Key


Moves cursor to
next field on form
Net field may NOT
be next closest
To Control Tab Order



If you moved fields
around

Fields controls are
numbered





In order of creation
Right-click on form
Choose Tab Order
Choose Details
Section
Auto Order

Access sets order
Drag control to
corrected order
Tab Control Properties

Tab Stop (No)


Auto Tab (only used with Input Mask)


cursor never goes to control;
once field is complete Tab automatically
moves to next control
Tab Index

change the index number of the control
Locked Vs. Enabled Controls

Locked



“Yes”, Can’t edit the field
You can copy the field contents
Enabled


“No”, dims control
No interaction, Can’t edit & Can’t Copy
Performing Calculations



Text Box Control
Unbounded
In Property Sheet, go to Data tab

Add Expression in Control Source
Using the Tab Control

If you have a lot of data to display



Not usually to add new data
Tabs allow you to utilize limited space
And organize related data
Navigation Form using Lists


Make sure Control Wizard is on
Choose List box or Combo Box



Combo Box saves screen space
Place control on the form
Wizard appears:





Find a Record on my Form
Choose field to use for Lookup
Make sure Hide Key column is checked
Enter Text Caption
Finish
The Subform Control


Form with a form
Usually the Many part of a 1:M
relationship


1: part is created as a form with controls
used to create a well designed form
:M part typically uses data sheet view

But can use a designed form as well

Usually would change property to continuous
display
Creating SubForm


Form Design Tools | Design > Controls
Adding manually

Source Object

Table or Query


Designed Form


Displays customized form
Link Master Field


Displays as Datasheet
Field in the form that links to related records in the
sub-form
Link Child Field

Field in sub-form linked to related record(s) in the
main-form
Command Buttons

User Triggered Actions




Types of Actions







Open a Form
Run a Query
Run a Report
Record Navigation
Record Operations
Form Operations
Report Operations
Application
Miscellaneous
You can use these controls to create a menu
system

More about this later.....
Access 2007
Using Forms to Build a Menu
SwitchBoard



A special Form with Command Buttons
One Page is Default
Can have unlimited Pages


Menu
Sub-Menu
Creating SwitchBoard
1.
2.
3.
4.
5.
6.
Database Tools > Database Tools >
SwitchBoard Manager
Choose Yes to Create one
Or Click Edit if one Exists
New, to create new Menu
Write Menu Text and Choose
Command option
Close
SwitchBoard Options

Go to Switchboard






Used to navigate to/from menu and submenu pages
Open Form in Add Mode
Open Form in Edit Mode
Open Report (in Print Preview mode)
Exit Application
Run Macro or Run Code
Starting Access with
Switchboard
1.
2.
3.
Choose Office Button > Access
Options
Click Current Database
Applications Options > Display Form
box
1.
4.
Choose Switchboard Form
Optionally, clear checkbox for
navigation pane
Ensure your Switchboard appears
on Top of open windows




Open Switchboard in Design View
Go to form properties
Other Tab
Pop up to “Yes”