Transcript Microsoft Access 2003

Microsoft Access 2003 - Intro
Class 2
Designing Databases and Tables
Naming Conventions


Clearly defines the type and purpose of each
object
A consistent naming convention will




Bring order to your database
Aid in documentation
Make it easier to understand the database (ie: choosing
an object using the report wizard, reading a report
generated by the Database Documenter)
Common naming conventions
consistent)



(choose one and then be
Hungarian
Reddick (http://www.xoc.net/standards/)
Leszynski – Popular
2
Naming Conventions

(cont’d)
Access Requirements

Object names
• up to 64 characters long
• Can include any combination of letters, numbers, spaces, and
special characters, except a period (.), and exclamation point (!),
and accent grave and brackets ([])
• Cannot begin with a space
• Cannot include control characters (ASCII values 0 through 31)


Table, view, or stored procedure names cannot include a
double quotation mark (“)
Table and Query names must be unique in the database.
Naming any other objects with the same name is NOT
recommended
3
Creating a Table in Design View

In Database window, double-click the
option to create a new table using Design
View






Type desired field names in appropriate
column
Choose data type for each field
Type description for each field (optional)
Mark primary key (right-click desired
field)
Set field properties, if desired
Save table
4
Data Types
Data Type
Description
Text
Includes any characters up to a maximum of 255 characters (determined by field size). If the data
includes a mix of numbers and any amount of letters, choose Text. (The default data type is Text).
Memo
Use this data types when Text is not large enough. Like Text, this data type can also have letters
and umbers, but can be much larger – up to a little over 64,000 characters.
Number
Includes various forms of numerical data that can be used in calculations.
Date/Time
Date and time entries in formats showing date, time, or both.
Currency
Use for currency values with up to fours digits after the decimal place. This data type is more
accurate for large numbers than the Number data type, but generally takes up more space.
AutoNumber
Usually this is used to create an identification number for each record. The value for each record
increases by one.
Yes/No
Only two possible values can be in this field. Options include Yes/No, True/False, On/Off. The
default style shows a check box with a checkmark on for Yes, or a blank for No.
OLE Object
This data type allows you to place another file type into your record. Within the field you could
insert a picture (a company logo, for example), a Word document (employee resume), or an Excel
spreadsheet (client summary chart).
Hyperlink
This allows you to insert a Web address such as www.durhamcollege.com, which will launch when
you click it in Datasheet view or on a form. You could also type a path and file name to a file on
your hard drive (C:\docs\myres.doc) or a network drive.
Lookup Wizard
Creates a lookup column, which creates a list of values from which to choose when entering data.
5
Field Properties

Field Properties
•
•
•
•
•
•
•
•
•
•
•
•
•
Field Size
Format
New Value (new in Access 2000)
Precision (new in Access 2000)
Scale
Decimal Place
Input Mask
Caption
Default Value
Required
Allow Zero Length
Indexed
Unicode Compression (New in Access 2000 – stores characters
in two bytes mainly to accommodate language fonts)
6
Field Properties
Field Size for Text data types determines the maximum number of characters you can type.
For Number
data types, the Field Size property can be used to determine how large the number can be and if you can have
decimal places.
Number Type
Description
Byte
Stores numbers from 0 to 255 (no fractions).
Long Integer
(Default) Stores numbers from –2,147,483,648 to 2,147,483,647
(no fractions).
Integer
Stores numbers from –32,768 to 32,767 (no fractions).
Single
Stores numbers from
–3.402823E38 to
–1.401298E–45 for negative values and from
1.401298E–45 to 3.402823E38 for positive values.
Double
Stores numbers from
–1.79769313486231E308 to
–4.94065645841247E–324 for negative values and from
1.79769313486231E308 to 4.94065645841247E–324 for positive
values.
7
Field Properties
(cont’d)
Format
Used especially for Date/Time data types – how you want the values displayed.
You can also type codes in the Format property box. For example, type m/d/yyyy
for a date type to display 1/1/2000.
Caption
This will appear at the top of the column in Datasheet view. Captions will
automatically carry through to new forms and reports instead of the field name.
Here, you can include spaces, and other punctuation marks not normally allowed
in the field name.
Default Value
This will appear in new records entered in the table.
Input Mask
A template to validate how each character is entered into the field.
Validation Rule
Allows you to verify data as the user enters it into the table.
Validation Text
Appears in a dialog box when a Validation Rule is broken.
Required
The user has to fill in this field before going to another record.
Allow Zero
Length
You can use the AllowZeroLength property to specify whether a zero-length string
(" ") is a valid entry in a table field. Note: The AllowZeroLength property applies
only to Text, Memo, and Hyperlink table fields.
Indexed
Makes searches and sorts on this field go quicker.
8
Indexes

Index






A separate hidden table that consists of
pointers to records or a group of records
Automatically set on primary keys
Makes sorting and searching more efficient
Maximum of 32 per table
Normally index any field you plan to sort on
or search by frequently
Can be added at any point in the development
of the database to improve performance
9
Formatting Tables
When in Doubt, Right-Click
Insert/Delete Fields or Records
 Hide/Unhide, Freeze/Unfreeze



Right-click the field name or the record
selector and choose the desired option
from the pop-up menu
Change column width/row height

Position mouse pointer on line between
field names or rows and drag or
double-click
10
Formatting Tables

(cont’d)
Move/Rearrange Fields
• Click once on field name, then drag field
name until a black line is visible and drag
to new position

Formatting Fields
• Font and style changes made to a range of
data will affect all data in the ENTIRE table
• Remember, tables are meant to store the
RAW data – to make data pretty, put it in a
report!
11
This work is licensed under a Creative Commons AttributionShareAlike 2.5 License.
12