Introduction

Frequently, individuals and organizations need to store and retrieve data of similar types.

Whether it is customer information, product information, inventory, payroll or sales results, the timely storage and efficient retrieval and analysis of this data can mean the success or failure of the venture.!

Data is Stored in Databases

A Database is an store of information of a particular type.

This could be lists, inventories, invoices, patients in a hospital, staff of a business, etc.

This data is stored, retrieved and analyzed with Database Software.

Database Software can be “Flat File” or Relational in nature.

Relational Databases are more efficient and more powerful.

One the Internet We also have something called a “DATA STORE”
A Data Store can be a collection of data of different types. This can include

Databases

Text Documents

Web Pages

Other types of textual information

Microsoft Access

We will be using Microsoft Access as our
Database program or database manager.

Access puts all the related information for one project
in a Database.

An Access Databases Consist of Objects: Tables, Reports, Views, Queries and other information related to this particular set of data

Access Can Have Many Databases

If you have several businesses you would have a Databases for each.

I could have a database for ABC Company, and Once for XYZ Company

I could use Access to Manage Each separately.

Access Databases end in mdb, so one database could be in a file called XYZCO.MDB, and the other could be ABCCO.MDB

Tables

Access Uses the Concept of a Table to organize Data

A Table Could be a List of Clients and Addresses.

One table could be your companies employees and their address

Another table could be the customers and their addresses

A Third table could be your inventory.

Since they all belonged to your company they could be in the same access database.

Records

Tables consist of Records

In a database all the information about one person or thing is a record.

Each record has the same structure for a given record type

All the Inventory items have a description, location, and cost.
In the Client Table, each record has a name, address and phone number:
Josephine Carr 448 34^th St 565-333-0998

Fields

Each separate part of the record structure is a field.

For example each record in our inventory had fields called name, location, and cost

In our client list we had fields called Name, Address, and Telephone

What would the fields be in this table?

#223 Fred Smith Co. $345 May 8, 1999

We could call them Number, Company, amount, and Date!

Keys

Records are identified from other records by a primary key.

The primary key must be unique, only one record can have it!

In a list of invoices, the invoice number would be the primary key

Types of Data

As you can see from the last example, the data, or information in the fields, can be of different types.

Common Data Types are:

Numbers: $34.45 100,000 .0034

Dates and Times: Feb 5, 2000, 10:01 AM

Text: Fred, 123-344-4566, 00345, Zebra

Logical: Yes, No; True, False;

Special data types for Access

Access allows variations of the above main types of fields:

Ole, Autonumber, Memo, Hyperlink, Lookup Wizard, Currency

Currency Fields are for money

They can be up to 15 digits to the left of the decimal

Plus also up to 4 decimal places

Number Fields

Numbers can be of several types including:

Byte, Integer, Long Integer, Single,Double, Replication ID, and Double

Autonumber

Autonumber fields are special number fields used to order records

They are sequential integers

Created by Access when new records are added

Usually only one autonumber field is used in a table

Text Data

TEXT FIELDS are fields with the letters A-Z, or numbers with no value. For instance a phone number entered as 743-9123 should be entered as a text field, not a numeric field.

Likewise a Zip code, such as 00456 must be entered as text field, otherwise the leading zeros mean nothing and they will be dropped!

Numeric Fields are of several types, but whenever we need to keep value or do math on a field it must be entered as some type of numeric field

Memo Fields

Memo fields are large text fields, they lack some of the special features of text, such as indexing, and easy fast searches, but can be up to 64,000 characters: Now is the time to come to my party, the date is Nov 23, 2001, and the...

DATE FIELDS

DATE FIELDS contain numbers in a date format of some type. Usually these dates can be used in certain operations such as determining elapsed time between two dates.

Mar 6, 1999 or 10/05/2000 are examples of dates in a data field.

How they are displayed is irrelevant to their actual value. The program can display the same date many different ways!

LOGICAL FIELDS

LOGICAL FIELDS store information as one of two values, such as True or False or Yes or No.

T or True or Y or Yes would all be different displays of the same logical field.

MEMO Fields

Some programs lump large amounts of character data in field types called MEMO Fields.

This allows the user to store documents, notes, and other large amounts of data with the other fields which are usually shorter.
This is a memo field it can be 64K long, which means thousands of characters in one long field!

Memo Fields do not usually have all the lookups and other options of the other types of fields.

Ole Objects

Ole Objects are pointers to files created in other programs:

pictures, sound clips, documents, etc are examples

They can be edited by the other program when clicked on.

Hyperlink

Hyperlink Fields are just links to other fields

Descriptive text

Actual Hyperlink

Lookup Wizard

Invokes a wizard to link the current table to another table to lookup values for a field in this table

Field Sizes and Attributes

Set the length of text fields except memo, date, and certain numbers when you first create your table, but then it is fixed unless the structure of the table is modified.

Set up fields long enough for your longest entry.

For example, many zips are 5 digit, but some require 10 digits, thus the field zip in a fixed length database must be at least 10 characters long

Shortening fields after data is entered may result in loss of data.

Typical Problem: try to enter 00785-0045 as zip, but 5 Character ZIP field only allows first 5 characters!

Deciding What Fields to Use

Data can be searched by entries in a field

For example you can look up the name Jones in the Last Name field of your customers table and show all the records with Jones in the Last Name Field if it is entered that way in your table. Last Name First Name
Jones Howard

But not if you have first and last name together in one field called name, unless you put in Jones, Howard when entered!

For searches to work effectively the data you want to search by should be in its own field.

Sorting

Data can be arranged or sorted by any of the fields.

In order to do this the field must be indexed or sorted

We can frequently sort by one field first, and then by another.

For example we could sort by state, and then by city or zip:

Alaska, Fairbanks, 55555; Ohio, Dayton, 22244; Ohio, Dayton, 22335; …

If we want to find or look up something frequently it should be in a unique field. Otherwise searches are slow and/or impossible.

For example if we store first and last name in one field a search for a last name would be difficult. If we store them in two separate fields called FirstName and LastName we could easily search the first name field for John, and the last name field for Smith.

Return to Home

Return to Classes

Return to Access Classes


	Frequently, individuals and organizations need to store and retrieve data of similar types.
	Whether it is customer information, product information, inventory, payroll or sales results, the timely storage and efficient retrieval and analysis of this data can mean the success or failure of the venture.!


	A Database is an store of information of a particular type.
	This could be lists, inventories, invoices, patients in a hospital, staff of a business, etc.
	This data is stored, retrieved and analyzed with Database Software.
	Database Software can be “Flat File” or Relational in nature.
	Relational Databases are more efficient and more powerful.


	Databases
	Text Documents
	Web Pages
	Other types of textual information


	We will be using Microsoft Access as our Database program or database manager.
	Access puts all the related information for one project in a Database.
	An Access Databases Consist of Objects: Tables, Reports, Views, Queries and other information related to this particular set of data


	If you have several businesses you would have a Databases for each.
	I could have a database for ABC Company, and Once for XYZ Company
	I could use Access to Manage Each separately.
	Access Databases end in mdb, so one database could be in a file called XYZCO.MDB, and the other could be ABCCO.MDB


	Access Uses the Concept of a Table to organize Data
	A Table Could be a List of Clients and Addresses.
	One table could be your companies employees and their address
	Another table could be the customers and their addresses
	A Third table could be your inventory.
	Since they all belonged to your company they could be in the same access database.


	Tables consist of Records
	In a database all the information about one person or thing is a record.
	Each record has the same structure for a given record type
	All the Inventory items have a description, location, and cost. In the Client Table, each record has a name, address and phone number: Josephine Carr 448 34^th St 565-333-0998


	Each separate part of the record structure is a field.
	For example each record in our inventory had fields called name, location, and cost
	In our client list we had fields called Name, Address, and Telephone
	What would the fields be in this table?
	#223 Fred Smith Co. $345 May 8, 1999
	We could call them Number, Company, amount, and Date!


	Records are identified from other records by a primary key.
	The primary key must be unique, only one record can have it!
	In a list of invoices, the invoice number would be the primary key


	As you can see from the last example, the data, or information in the fields, can be of different types.
	Common Data Types are:
		Numbers: $34.45 100,000 .0034
		Dates and Times: Feb 5, 2000, 10:01 AM
		Text: Fred, 123-344-4566, 00345, Zebra
		Logical: Yes, No; True, False;


		Access allows variations of the above main types of fields:
		Ole, Autonumber, Memo, Hyperlink, Lookup Wizard, Currency


	They can be up to 15 digits to the left of the decimal
	Plus also up to 4 decimal places


	Numbers can be of several types including:
	Byte, Integer, Long Integer, Single,Double, Replication ID, and Double


	Autonumber fields are special number fields used to order records
	They are sequential integers
	Created by Access when new records are added
	Usually only one autonumber field is used in a table


	TEXT FIELDS are fields with the letters A-Z, or numbers with no value. For instance a phone number entered as 743-9123 should be entered as a text field, not a numeric field.
	Likewise a Zip code, such as 00456 must be entered as text field, otherwise the leading zeros mean nothing and they will be dropped!
	Numeric Fields are of several types, but whenever we need to keep value or do math on a field it must be entered as some type of numeric field


	Memo fields are large text fields, they lack some of the special features of text, such as indexing, and easy fast searches, but can be up to 64,000 characters: Now is the time to come to my party, the date is Nov 23, 2001, and the...


	DATE FIELDS contain numbers in a date format of some type. Usually these dates can be used in certain operations such as determining elapsed time between two dates.
	Mar 6, 1999 or 10/05/2000 are examples of dates in a data field.
	How they are displayed is irrelevant to their actual value. The program can display the same date many different ways!


	LOGICAL FIELDS store information as one of two values, such as True or False or Yes or No.
	T or True or Y or Yes would all be different displays of the same logical field.


	Some programs lump large amounts of character data in field types called MEMO Fields.
	This allows the user to store documents, notes, and other large amounts of data with the other fields which are usually shorter. This is a memo field it can be 64K long, which means thousands of characters in one long field!
	Memo Fields do not usually have all the lookups and other options of the other types of fields.


	Ole Objects are pointers to files created in other programs:
	pictures, sound clips, documents, etc are examples
	They can be edited by the other program when clicked on.


	Hyperlink Fields are just links to other fields
	Descriptive text
	Actual Hyperlink


	Invokes a wizard to link the current table to another table to lookup values for a field in this table


	Set the length of text fields except memo, date, and certain numbers when you first create your table, but then it is fixed unless the structure of the table is modified.
	Set up fields long enough for your longest entry.
	For example, many zips are 5 digit, but some require 10 digits, thus the field zip in a fixed length database must be at least 10 characters long
	Shortening fields after data is entered may result in loss of data.
	Typical Problem: try to enter 00785-0045 as zip, but 5 Character ZIP field only allows first 5 characters!


	Data can be searched by entries in a field
	For example you can look up the name Jones in the Last Name field of your customers table and show all the records with Jones in the Last Name Field if it is entered that way in your table. Last Name First Name Jones Howard
	But not if you have first and last name together in one field called name, unless you put in Jones, Howard when entered!
	For searches to work effectively the data you want to search by should be in its own field.


	Data can be arranged or sorted by any of the fields.
	In order to do this the field must be indexed or sorted
	We can frequently sort by one field first, and then by another.
	For example we could sort by state, and then by city or zip:
		Alaska, Fairbanks, 55555; Ohio, Dayton, 22244; Ohio, Dayton, 22335; …
	If we want to find or look up something frequently it should be in a unique field. Otherwise searches are slow and/or impossible.
	For example if we store first and last name in one field a search for a last name would be difficult. If we store them in two separate fields called FirstName and LastName we could easily search the first name field for John, and the last name field for Smith.