Databases with Microsoft Access. Using Access to create Databases Jan-Feb 2003

(1)

Databases with Microsoft

Access

Using Access to create Databases

Jan

(2)

What is a Database?

•

• An Organized collection of information about a An Organized collection of information about a subject.

subject.

•

• Examples:Examples:

–

– Address BookAddress Book –

– Telephone BookTelephone Book –

– Filing Cabinet full of documentsFiling Cabinet full of documents –

– PrePre--existing forms (hard copy)existing forms (hard copy) –

(3)

What is Access?

•

• A database management system allowing users A database management system allowing users to input, edit, and verify data.

to input, edit, and verify data.

•

• It is a part of the Office 2000 suiteIt is a part of the Office 2000 suite •

(4)

What makes up a Database?

•

• Records or Record SetRecords or Record Set •

• A set of details about a specific item.A set of details about a specific item. • • ExampleExample – – NameName – – AddressAddress –

(5)

What makes up a Record?

•

• FieldsFields •

• Provide the categories for the details describing Provide the categories for the details describing each record.

each record.

•

• Fields are defined by data typesFields are defined by data types •

• The specific field data within a record is the field The specific field data within a record is the field contents

(6)

In a Nutshell

•

• Database = collection of informationDatabase = collection of information •

• Database is made up of recordsDatabase is made up of records •

• Records are made up of fieldsRecords are made up of fields •

• Fields are defined with data types that become Fields are defined with data types that become field contents.

(7)

The Database Window

• • TableTable • • FormForm • • QueryQuery • • ReportReport •

• Macro (not covered in this class)Macro (not covered in this class) •

• Module (not covered in this class)Module (not covered in this class) •

(8)

Tables

•

• All Access database information is stored in All Access database information is stored in tables.

tables.

•

• Each row of an Access Table is a recordEach row of an Access Table is a record •

• Each column of an Access Table is a fieldEach column of an Access Table is a field •

• Each column is headed by a field nameEach column is headed by a field name •

• The field contents is the specific field data The field contents is the specific field data within a record

(9)

Form

•

• A window for viewing the data in one or more A window for viewing the data in one or more tables.

tables.

•

• Makes it easy to view, input, and edit data on a Makes it easy to view, input, and edit data on a single page.

single page.

•

• Another way to have users input data.Another way to have users input data. •

(10)

Query

•

• A way to ask questions of the dataA way to ask questions of the data •

• Allows you to see or work with a portion of a Allows you to see or work with a portion of a table by limiting the number of fields and by

table by limiting the number of fields and by

selecting specific records.

•

(11)

Report

•

• Formatted information from a table or query Formatted information from a table or query that you can send to a printer.

that you can send to a printer.

•

(12)

Page

•

• Allows you to create WebAllows you to create Web--based forms that based forms that allow users to input and read data from your

allow users to input and read data from your

database.

(13)

Field Data Types

• • TextText • • MemoMemo • • NumberNumber • • Date/TimeDate/Time • • CurrencyCurrency • • AutoNumberAutoNumber • • Yes/NoYes/No •

• OLE ObjectOLE Object •

• HyperlinkHyperlink •

(14)

Text

•

• Text or combinations of text and numbers, such Text or combinations of text and numbers, such as addresses. Also numbers that do not require

as addresses. Also numbers that do not require

calculations, such as phone numbers, part

numbers, or postal codes

•

(15)

Memo

•

• Lengthy text and numbers, such as notes or Lengthy text and numbers, such as notes or descriptions

descriptions

•

(16)

Number

•

• Numeric data to be used for mathematical Numeric data to be used for mathematical calculations, except calculations involving

calculations, except calculations involving

money.

•

(17)

Date/Time

•

• Dates and TimeDates and Time •

(18)

Currency

•

• Currency values. Use the Currency data type to Currency values. Use the Currency data type to prevent rounding off during calculations.

prevent rounding off during calculations.

Accurate to 15 digits to the left of the decimal

point and 4 digits to the right.

•

(19)

AutoNumber

•

• Unique sequential (incrementing by 1) or Unique sequential (incrementing by 1) or

random numbers automatically inserted when a

record is added.

•

(20)

Yes/No

•

• Fields that will contain only one of two values, Fields that will contain only one of two values, such as Yes/No, True/False, On/Off.

such as Yes/No, True/False, On/Off.

•

(21)

OLE Object

•

• Objects (such as Microsoft Word documents, Objects (such as Microsoft Word documents, Excel spreadsheets, pictures, sounds, or other

Excel spreadsheets, pictures, sounds, or other

binary data), created in other programs using the

OLE protocol, that can be linked to or

embedded in an Access table

•

(22)

Hyperlink

•

• Field that will store hyperlinks. A hyperlink can Field that will store hyperlinks. A hyperlink can be a UNC path or a URL.

be a UNC path or a URL.

•

(23)

Lookup Wizard

•

• Creates a field that allows you to choose a value Creates a field that allows you to choose a value from another table or from a list of values using

from another table or from a list of values using

a combo box. Choosing this option in the data

type list starts a wizard to define this for you.

•

(24)

Primary Key

• The power of a relational database system such as

Microsoft Access comes from its ability to quickly find and bring together information stored in separate tables using queries, forms, and reports. In order to do this, each table should include a field or set of fields that

uniquely identifies each record stored in the table. This information is called the primary key of the table. Once you designate a primary key for a table, to ensure

(25)

So should I set a Primary Key?

•

• I would just to be safe.I would just to be safe. •

• It creates an AutoNumber field automaticallyIt creates an AutoNumber field automatically •

(26)

Input Mask

•

• This controls how the data is entered into a field This controls how the data is entered into a field in a table or a form.

in a table or a form.

•

• Common Input Masks are Social Security Common Input Masks are Social Security

Number, Date/Time, Phone number, Zip code,

Extension, Password.

•