Transcript Access1 Slides

Introduction

What is a database?




Collection of related data arranged into related tables of rows & columns
Tables are typically interconnected through the data
Most powerful when data is more complicated
Different from Excel?



Suitable for large data sizes
Models a complete business
Provides stronger built-in data access functionalities




Rather than building them from scratch as in Excel
Provided thru a DBMS
Works with data of interest only
What is a database management system (DBMS)?


A collection of complicated software programs that organizes the data in a database and
allows users to insert, update, delete or retrieve subsets of the data
Other provided features include: Access Control, Recovery, Concurrency etc …
Introduction

A Table holds data for one entity of the business

Table is divided into rows called records (or rows)



Row is divided into fields (or columns)




One row describes one unit in our table
E.g. one employee, product, customer, etc …
Properties of the entity
E.g. properties of an employee: Name, address, department, salary, etc …
See Gargoyle Employee Example
Every table must have a unique key



Unique value for every record in the table to assist in maintaining data integrity (Key field)
If more than 1 key exist, one is designated as primary key
Student table in university database


Unique attributes (key fields?)
Primary key?
Designing a Table in Access

Office ButtonNew under “Blank Database” on the
right


Name your database file, choose its storage location, click on Create
button
A window appears: under the “AllTables” section on the left





Click on the down arrow
Select “Object Type”
Click on the down arrow again and observe that now you can see all
objects that can be created in Access: Forms, Tables, Queries, Reports,
Macros, etc …
Select “Tables” and double click on the only table in the menu (usually
called Table1)
In the upper menu (right above “Table1”): Click on View and select
“Design View”…save table if prompted


Field part: Name, Type, and Description of every field
Properties part: specific properties for each field
Designing a Table in Access




Field Name should be unique
Many Data Types
Description for programmers needs
Add SSN and LastName to Table
Designing a Table – Data Types






Text – Up to 255 characters
Memo – longer strings
Date/Time
Currency
AutoNumber – let system generate a
number
Yes/No – like boolean
Properties



Input Mask – pattern for input
information
Validation Rule – Can specify
requirements for a valid entry and then
specify an error message if not met
Required – Has to have a value
Properties

Validation Rule:


Determines what is a valid for this field
Department in EMPLOYEE table must be limited to certain names







In (“Sales”, “Production”, “Management”, “Design”)
< #1/1/1995#
LIKE "#####-####“
LIKE "*smith##*"
LIKE "??00####"
Validation Text: specifies the message output to the user in case in valid
input is provided (e.g. ‘Not a Valid Department’)
Set one field as the primary key


Select the field
Click on the key icon on the toolbar (notice the key appears next to the
field)
Adding and Editing Data





Switch from Design View to Dataset
View
Type a row at a time
Formatting appears automatically
Edit by clicking in a cell and changing
values
Can sort entries using top menu
Queries

A selection function

selects certain fields and records from a table according to
certain criteria & displays results in a new table for a report



Record filter
Update, delete, & insert data into a table
Select Queries are very common

The produced table is a subset of the database


Limited by selected fields and matching records
Used to summarize or do simple calculations on the data in
the database
Queries



Click on the down arrow on
the left next to where it
says “Tables”
Select “Queries” and double
click on the only table in the
menu (usually called
Table1)
On the top menu, click on
the “Create Ribbon” and
then click on the “Query
Design” button in the
“Other” group



to be displayed in the query or
that participate in the selection
criteria
(Not limited to the given fields)

Derive others
Queries

For each field we can
decide to


Show it
Specify criteria to be
met

(Numbers)




(Strings)






= 25
<= 10
Between 5 and 10
“Management”
“Sales” or “Design”
Not “Sales”
In (“Sales”, “
Design”,
“Production”)
Like “S*”
(Dates)

< #1-May-99#