Data types in Microsoft Access

clutteredreverandData Management

Oct 31, 2013 (3 years and 1 month ago)

139 views

What is

Access
?
Access is a database tool for gathering and understanding all your
information

your phone numbers, inventory, guest lists, whatever you’re tracking

and providing a convenient way to enter, navigate, and report out your data.


When do I
need

Access
?



When you want to keep track of not only who’s on your guest list, but where they’re
staying, and any dietary needs.



When you need to be able to collect contact information accurately from large
groups.



When you want a database that is easy for

colleagues to explore your information
without training.



When you need to run the same report daily, weekly, or monthly.



When you want to know who your most valuable customers are so you can target
your marketing.


What is a form?

A

form

is an Access
object. It generally serves three purposes:

1.

To allow users to perform data entry. Data can be inserted, updated, or deleted from a table
using a Form object.

2.

To allow users to enter custom information, and based on that information perform a task. For
exam
ple, you may want to ask a user for parameters before running a report.

3.

To allow users a method of navigating through the system. For example, you may create a form
where a user can select a form to load, a report to run, etc.


A form is a database object
that you can use to create a user interface for a database
application. A "bound" form is one that is directly connected to a data source such as
a table or query, and can be used to enter, edit, or display data from that data
source. Alternatively, you ca
n create an "unbound" form that does not link directly to
a data source, but which still contains command buttons, labels, or other controls
that you need to operate your application.



Databases



Databases serve the function of data collection, management
and storage. MS Access
databases are used for personal and/or business often for data storage of address books,
customer information, mailing lists or inventory lists. Recall of the database content is
intuitive and can be queried, printed or published to
the Internet.

Tables



MS Access tables are the key objects in the Access file, as they contain the data that is
stored in the database. Tables are made up of rows and columns and allow for direct data
entry into their grids. The row is the record that conta
ins the individual data pieces
making up an individual record. The columns, also referred to as fields, consist of
categorized information. For example, the fields are columns consisting of categorized
information such as telephone numbers.


What are these

objects?

When you create a database, Access offers you

Tables, Queries, Forms, Reports,
Macros,

and

Modules
. Here's a quick overview of what these are and when to use them.

Tables.

All data is stored in tables. When you create a new table, Access asks yo
u
define

fields

(column headings), giving each a unique name, and telling Access the

data type
. Use
the "Text" type for most data, including numbers that don't need to be added e.g. phone numbers
or postal codes. Using Wizards, Access will walk you through

the process of creating common tables
such as lists of names and addresses. Once you have defined a table's structure, you can enter data.
Each new row that you add to the table is called a

record
. To define

relationships

between tables:



in Access 2007 or

later:


Database Tools | Relationships,



in Access 95


2003:


Tools | Relationships,



in Access 1


2:


Edit | Relationships.

Queries.

Use a query to find or operate on the data in your tables. With a query, you can
display the records that match certain

criteria

(e.g. all the members called "Barry"),

sort

the data
as you please (e.g. by Surname), and even

combine data

from different tables. You can

edit

the
data displayed in a query (in most cases), and the data in the underlying table will change. Specia
l
queries can also be defined to make

wholesale changes

to your data, e.g. delete all members
whose subscriptions are 2 years overdue, or set a "State" field to "WA" wherever postcode begins
with 6.

Forms.

These are screens for

displaying

data from and

inputting

data into your tables. The
basic form has an appearance similar to an index card: it shows only one record at a time, with a
different field on each line. If you want to control how the records are

sorted,

define a query first,
and then create a
form based on the query. If you have defined a one
-
to
-
many relationship
between two tables, use the "
Subform
" Wizard to create a form which contains another form. The
subform will then display only the records matching the one on the main form.

Reports.

I
f forms are for input, then reports are for output.

Anything you plan to
print

deserves a report, whether it is a list of names and addresses, a financial summary for a
period, or a set of mailing labels. Again the Access Wizards walk you through the proce
ss of
defining reports.

Pages

(Access 2000
-

2003). Use pages to enter or display data via Internet. Pages are
stored as HTML files, with data read from and written to the database. Michael Kaplan has
published a

free utility

to convert Access forms and reports into Data Access Pages. (Pages were
deprecated in Access 2007.)

Macros.

An Access Macro is a

script

for doing some job. For example, to create a button which
opens a
report, you could use a macro which fires off the "OpenReport" action. Macros can also be
used to

set one field
based on the value of another (the "SetValue" action), to

validate

that certain
conditions are met before a record saved (the "CancelEvent" actio
n) etc.

Modules.

This is where you

write your own functions

and programs if you want to.
Everything that can be done in a macro can also be done in a module, but you don't get the Macro
interface that prompts you what is needed for each action. Modules ar
e far more powerful, and are
essential if you plan to write code for a

multi
-
user

environment, since macros cannot include error
handling. Most serious Access users start out with macros to get a feel for things, but end up using
modules almost exclusively
. On the other hand, if your needs are simple, you may never need to
delve into the depths of Access modules.


Data types in Microsoft Access

The following list summarizes all the field data types available in Microsoft
Access, their uses, and their
storage sizes.

Text

Use for text or combinations of text and numbers, such as addresses, or
for numbers that do not require calculations, such as phone numbers,
part numbers, or postal codes.

Stores up to 255 characters. The FieldSize property controls the

maximum number of characters that can be entered.

Memo

Use for lengthy text and numbers, such as notes or descriptions.

Stores up to 65,536 characters.

Number

Use for data to be included in mathematical calculations, except
calculations involving money (u
se Currency type).

Stores 1, 2, 4, or 8 bytes; stores 16 bytes for Replication ID (GUID). The
FieldSize property defines the specific Number type.

Date/Time

Use for dates and times.

Stores 8 bytes.

Currency

Use for currency values and to prevent rounding o
ff during calculations.

Stores 8 bytes.

AutoNumber

Use for unique sequential (incrementing by 1) or random numbers that
are automatically inserted when a record is added.

Stores 4 bytes; stores 16 bytes for Replication ID (GUID).

Yes/No

Use for data that c
an be only one of two possible values, such as Yes/No,
True/False, On/Off. Null values are not allowed.

Stores 1 bit.

OLE Object

Use for OLE objects (such as Microsoft Word documents, Microsoft Excel
spreadsheets, pictures, sounds, or other binary data)
that were created in
other programs using the OLE protocol.

Stores up to 1 gigabyte (limited by disk space).

Hyperlink

Use for hyperlinks. A hyperlink can be a UNC path or a URL.

Stores up to 64,000 characters.


What is Field ?

A field contains an item of
data; that is, a character, or group of
characters that are related. For instance, a grouping of related text
characters such as "John Smith" makes up a name in the name field.


What is Record ?

A record is composed of a group of related fields. As anothe
r way of
saying it, a record contains a collection of attributes related to an entity
such as a person or product. Looking at the list of potential gun control
supporters, the name, address, zip code and telephone number of a
single individual would consti
tute a record. A payroll record would
contain the name, address, social security number, and title of each
employee.


A database is a collection of related files that are usually integrated,
linked or cross
-
referenced to one another. The advantage of a
database is that data and records contained in different files can be
easily organized and retrieved using specialized database management
software called a database management system (DBMS) or database
manager.

After reading this lesson, you should be abl
e to:



Define the term database management system (DBMS).



Describe the basic purpose and functions of a DBMS.



Discuss the advantages and disadvantages of DBMSs.




DBMS Fundamentals

A database management system is a set of software programs that allows
users to create, edit and update data in database files, and store and
retrieve data from those database files. Data in a database can be added,
deleted, changed, sorted or searched all

using a DBMS.

DBMSs and File Management Systems

Computerized file management systems (sometimes called file managers)
are not considered true database management systems because files
cannot be easily linked to each other. However, they can serve as usef
ul
data management functions by providing a system for storing information
in files. For example, a file management system might be used to store a
mailing list or a personal address book. When files need to be linked, a
relational database should be creat
ed using database application
software such as Oracle, Microsoft Access, IBM DB2, or FileMaker Pro.

The Advantages of a DBMS

Improved availability:

One of the principle advantages of a DBMS is that
the same information can be made available to different us
ers.

Minimized redundancy:

The data in a DBMS is more concise because, as a
general rule, the information in it appears just once. This reduces data
redundancy, or in other words, the need to repeat the same data over
and over again. Minimizing redundancy
can therefore significantly reduce
the cost of storing information on hard drives and other storage devices.
In contrast, data fields are commonly repeated in multiple files when a
file management system is used.

Accuracy:

Accurate, consistent, and up
-
to
-
d
ate data is a sign of data
integrity. DBMSs foster data integrity because updates and changes to the
data only have to be made in one place. The chances of making a mistake
are higher if you are required to change the same data in several
different places
than if you only have to make the change in one place.

Program and file consistency:

Using a database management system, file
formats and system programs are standardized. This makes the data files
easier to maintain because the same rules and guidelines a
pply across all
types of data. The level of consistency across files and programs also
makes it easier to manage data when multiple programmers are involved.

User
-
friendly:

Data is easier to access and manipulate with a DBMS than
without it. In most cases,

DBMSs also reduce the reliance of individual
users on computer specialists to meet their data needs.

Improved security:

As stated earlier, DBMSs allow multiple users to
access the same data resources. This capability is generally viewed as a
benefit, but
there are potential risks for the organization. Some sources
of information should be protected or secured and only viewed by select
individuals. Through the use of passwords, database management systems
can be used to restrict data access to only those wh
o should see it.

The Disadvantages of a DBMS

There are basically two major downsides to using DBMSs. One of these is
cost, and the other the threat to data security.

Cost:

Implementing a DBMS system can be expensive and time
-
consuming,
especially in large
organizations. Training requirements alone can be
quite costly.

Security:

Even with safeguards in place, it may be possible for some
unauthorized users to access the database. In general, database access is
an all or nothing proposition. Once an unauthoriz
ed user gets into the
database, they have access to all the files, not just a few. Depending on
the nature of the data involved, these breaches in security can also pose
a threat to individual privacy. Steps should also be taken to regularly
make backup co
pies of the database files and store them because of the
possibility of fires and earthquakes that might destroy the system.

Types of Database Management Systems

DBMSs come in many shapes and sizes. For a few hundred dollars, you can
purchase a DBMS for yo
ur desktop computer. For larger computer
systems, much more expensive DBMSs are required. Many mainframe
-
based DBMSs are leased by organizations. DBMSs of this scale are highly
sophisticated and would be extremely expensive to develop from scratch.
Therefo
re, it is cheaper for an organization to lease such a DBMS program
than to develop it. Since there are a variety of DBMSs available, you
should know some of the basic features, as well as strengths and
weaknesses, of the major types.

After reading this les
son, you should be able to:



Compare and contrast the structure of different database
management systems.



Define hierarchical databases.



Define network databases.



Define relational databases.



Define object
-
oriented databases.

Types of DBMS: Hierarchical
Databases

There are four structural types of database management systems:
hierarchical, network, relational, and object
-
oriented.

Hierarchical Databases (DBMS)
, commonly used on mainframe
computers, have been around for a long time. It is one of the oldest

methods of organizing and storing data, and it is still used by some
organizations for making travel reservations. A hierarchical database is
organized in pyramid fashion, like the branches of a tree extending
downwards. Related fields or records are grou
ped together so that there
are higher
-
level records and lower
-
level records, just like the parents in a
family tree sit above the subordinated children.

Based on this analogy, the parent record at the top of the pyramid is
called the

root record
. A child r
ecord always has only one parent record
to which it is linked, just like in a normal family tree. In contrast, a
parent record may have more than one child record linked to it.
Hierarchical databases work by moving from the top down. A record
search is con
ducted by starting at the top of the pyramid and working
down through the tree from parent to child until the appropriate child
record is found. Furthermore, each child can also be a parent with
children underneath it.

The advantage of hierarchical databas
es is that they can be accessed and
updated rapidly because the tree
-
like structure and the relationships
between records are defined in advance. However, this feature is a two
-
edged sword. The disadvantage of this type of database structure is that
each c
hild in the tree may have only one parent, and relationships or
linkages between children are not permitted, even if they make sense
from a logical standpoint. Hierarchical databases are so rigid in their
design that adding a new field or record requires t
hat the entire database
be redefined.

Types of DBMS: Network Databases

Network databases

are similar to hierarchical databases by also having a
hierarchical structure. There are a few key differences, however. Instead
of looking like an upside
-
down tree, a

network database looks more like a
cobweb or interconnected network of records. In network databases,
children are called

members
and parents are called

owners
. The most
important difference is that each child or member can have more than
one parent (or ow
ner).

Like hierarchical databases, network databases are principally used on
mainframe computers. Since more connections can be made between
different types of data, network databases are considered more flexible.
However, two limitations must be considere
d when using this kind of
database. Similar to hierarchical databases, network databases must be
defined in advance. There is also a limit to the number of connections
that can be made between records.

Types of DBMS: Relational Databases


In

relational
databases
, the relationship between data files is relational,
not hierarchical. Hierarchical and network databases require the user to
pass down through a hierarchy in order to access needed data. Relational
databases connect data in different files by usi
ng common data elements
or a key field. Data in relational databases is stored in different tables,
each having a key field that uniquely identifies each row. Relational
databases are more flexible than either the hierarchical or network
database structure
s. In relational databases, tables or files filled with
data are called

relations
,

tuples

designates a row or record, and columns
are referred to as

attributes

or fields.

Relational databases work on the principle that each table has a key field
that uniqu
ely identifies each row, and that these key fields can be used
to connect one table of data to another. Thus, one table might have a
row consisting of a customer account number as the key field along with
address and telephone number. The customer account
number in this
table could be linked to another table of data that also includes customer
account number (a key field), but in this case, contains information about
product returns, including an item number (another key field). This key
field can be linked

to another table that contains item numbers and other
product information such as production location, color, quality control
person, and other data. Therefore, using this database, customer
information can be linked to specific product information.

The r
elational database has become quite popular for two major reasons.
First, relational databases can be used with little or no training. Second,
database entries can be modified without redefining the entire structure.
The downside of using a relational data
base is that searching for data can
take more time than if other methods are used.

Types of DBMS: Object
-
oriented Databases (OODBMS)

Able to handle many new data types, including graphics, photographs,
audio, and video,

object
-
oriented databases

represent a significant
advance over their other database cousins. Hierarchical and network
databases are all designed to handle structured data; that is, data that
fits nicely into fields, rows, and columns. They are useful for handling
small snippets of

information such as names, addresses, zip codes,
product numbers, and any kind of statistic or number you can think of. On
the other hand, an object
-
oriented database can be used to store data
from a variety of media sources, such as photographs and text,

and
produce work, as output, in a multimedia format.

Object
-
oriented databases use small, reusable chunks of software called
objects. The objects themselves are stored in the object
-
oriented
database. Each object consists of two elements: 1) a piece of da
ta (e.g.,
sound, video, text, or graphics), and 2) the instructions, or software
programs called methods, for what to do with the data. Part two of this
definition requires a little more explanation. The instructions contained
within the object are used to

do something with the data in the object.
For example, test scores would be within the object as would the
instructions for calculating average test score.

Object
-
oriented databases have two disadvantages. First, they are more
costly to develop. Second, m
ost organizations are reluctant to abandon or
convert from those databases that they have already invested money in
developing and implementing. However, the benefits to object
-
oriented
databases are compelling. The ability to mix and match reusable object
s
provides incredible multimedia capability. Healthcare organizations, for
example, can store, track, and recall CAT scans, X
-
rays,
electrocardiograms and many other forms of crucial data.

Query Language

Query language allows the user to interact directly
with the database
software in order to perform information
-
processing tasks using data in a
database. It is usually an easy
-
to
-
use computer language that relies on
basic words such as SELECT, DELETE, or MODIFY. Using query language
and a computer keyboard,

the user enters commands that instruct the
DBMS to retrieve data from a database or update data in a database.

Structured Query Language (SQL)

is one type of query language that is
widely used to perform operations using relational databases. Remember
tha
t relational databases are composed of tables with rows and columns.
SQL can be used to retrieve information from related tables in a database
or to select and retrieve information from specific rows and columns in
one or more tables. One of the keys to un
derstanding how SQL works in a
relational database is to realize that each table and column has a specific
name associated with it. In order to query a table, the user specifies the
name of the table (indicating the rows to be displayed) and the names of
t
he columns to be displayed. A typical SQL query contains three key
elements:

SELECT (the column names to be displayed)

FROM (indicates the table name from which column names will be
derived)

WHERE (describes the condition for the query)

An Example of SQL

T
o illustrate the application of this type of query, let's assume a
particular user wishes to query a relational database containing
information about donors to a charitable organization. If the user wants
to know the name and address of all individuals don
ating $100 or more,
the following query might be used:

SELECT Name, Address

FROM Donor List

WHERE DonationAmt > 100

Once this command has been executed, the computer will display a list of
donors that meets the predefined criteria. In this case, all of the

data are
extracted from a single table. Similar queries can be made to extract
data from multiple tables. Such a strategy might be used to analyze
customer information involving billing data and order data, using two
separate tables. In this case, the FRO
M command would list the names of
the two tables involved.

Other Capabilities and Query Languages

SQL has many other capabilities, one of which is to be able to update and
revise a relational database. Users may discover the need to add, delete,
and/or cha
nge columns and rows in a database.

Other types of query languages are also available for manipulating data in
relationship databases. Another popular example is called query
-
by
-
example (QBE). This language uses a graphical approach and grid patterns
to al
low the user to specify the data to be displayed. SQL and QBE cannot
be used with hierarchical and network databases. Unique query languages
have been designed specifically for these databases.


Data Mining (DM)

Data mining, also known as "knowledge discov
ery," refers to computer
-
assisted tools and techniques for sifting through and analyzing these vast
data stores in order to find trends, patterns, and correlations that can
guide decision making and increase understanding. Data mining covers a
wide variety

of uses, from analyzing customer purchases to discovering
galaxies. In essence, data mining is the equivalent of finding gold nuggets
in a mountain of data. The monumental task of finding hidden gold
depends heavily upon the power of computers.

Applications of Data Mining

Data mining includes a variety of interesting applications. A few examples
are listed below:



By recording the activity of shoppers in an online store, such as
Amazon.com, over time, retailers can use knowledge of these
patterns
to improve the placement of items in the layout of a mail
-
order catalog page or Web page.



Telephone companies mine customer billing data to identify
customers who spend considerably more than average on their
monthly phone bill. The company can then target

these customers
to sell additional services.



Marketers can effectively target the wants and needs of specific
consumer groups by analyzing data about customer preferences and
buying patterns.



Hospitals use data mining to identify groups of people whose
he
althcare costs are likely to increase in the near future so that
preventative steps can be taken.