Access

1
Access
Part I
2
Open a Access Database

• Blank Database
• Database Wizard
• Existing Database (last five databases saved on
  the computer appear in the list box. If the file
  you want does not appear select More files)

3
The database window

• This window will appear after selecting the
  database. Note it has the 6 database objects
  each on its own tab.

4
Database Tables

• Used to store data
• holds field names, field descriptions and data
  for each field of each record
• underlying structure for data stored in a database

5
Table Selection

• Datasheet View
• Design View
• Table Wizard
• Import Table
• Link Table

6
Design ViewDatabase Table
7
Columns in the Table Design

• Field Name
• Data Type - 10 types that are listed in a drop
  down box by clicking the arrow button on the
  column
• Description

8
Access Data Types

• Text - any combination of alphabetic and numeric
  characters, such as names, addresses, and
  telephone numbers, that are not use in
  calculations (default)
• memo - long entries requiring multiple lines of
  text, such as detailed descriptions and
  performance notes

9
Access Data Types

• Number - numeric values, such as the number of
  items or number of days worked that might be used
  in calculations
• Date/Time - dates, such as date hired, and times,
  such as 100
• Currency - monetary values, such as salary

10
Access Data Types

• AutoNumber - numbers assigned by Access to
  uniquely identify each record these values
  cannot be changed, deleted, or edited
• Yes/No - Single-character entry fields that are
  marked when the status of the field is true (yes)
  or left blank when the status is false (no)

11
Access Data Types

• OLE object - fields that may be linked to an
  object, such as a picture or a document
• Hyperlink - fields linked to other objects, Web
  pages, or documents that appear when the field
  when the field is clicked.
• Lookup Wizard - fields that enable you to access
  a value from a table or list of values

12
Filled in Table Design
13
Datasheet View
The Field Name(s)
There are no records for this Database (yet)
14
Records Added to Datasheet
15
Two Views

• Datasheet view - you see the field names as
  column headings and records as row. View multiple
  records on the screen.
• Form view - forms use the fields and data that
  are stored in database tables to see one record
  on-screen at a time.

16
Form View
17
Maintenance

• Change is inevitable. You will need add, delete
  modify information in your db
• find, update, insert, replace, delete, sort, and
  filter and query.

18
Find, Filter, Query

• Query language
• format
• examples

19
Query Language

• ask questions about the database
• find the records which match a certain criteria

20
Find or Filter or Query

• Which one? Depends on preciseness.
• All display or filter only the record(s) that
  match a specific criteria
• note that there is also a FIND command in the
  word processing and spreadsheet tools.

21
Find

• usually used for error correction
• used in searching for a particular value in a
  record. This command works the same way as it
  did for the spreadsheet.
• you type in the dialog box
• Find what the entry you want to find.
  Depending on whether you unselected current
  field, Find searches each field (in the table)
  to look for a match.

22
To Find Records in a DB Table

• The binoculars on the toolbar
• also can do find/replace (Edit-gtReplace)

23
Find

• Find can a search criterion with text or values
  anywhere in the table. This can be inexact. For
  example, if you used the find command to find an
  employee named Brown, the records of employees
  who live in the town Brown or on the street Brown
  could also be displayed.

24
Filters

• A Filter is an exact match. In a filter, Access
  only matches within a certain field. So in our
  last example, you would tell Access to find only
  NAMES (the field name) that are equal to Brown.
  Thus the other Brown matches are eliminated.

25
Filters

• To display (filter) only the record or records
  that satisfy a criterion.
• Filter by selection is the simplest type of
  filter.
• All you need to do is give Access an example of
  the data you want by selecting the data within
  the table, and then clicking the Filter by
  Selection button on the Form View toolbar (the
  sieve with the lighting bolt)

26
Filter

• In order to have all the records once again
  displayed, you remove the filter by clicking the
  Remove Filter button on the form toolbar. ( the
  sieve by itself icon)

27
Filter/Query

• Can be saved (it is one of the 6 objects)
• multiple tables
• displays only the fields you specified
• queries can also be used to insert new values,
  modify field values and perform calculations on
  field values

• Not saved
• only within a single table
• displays all the fields of filtered records
• no other functions

28
Database Window
Three options you can do in queries section
Queries Tab
Previously Saved queries
29
Queries

• Click on tab marked Queries on the Database
  Window
• There are three button on this window
• Open - If a previously saved query is
  highlighted, clicking this button will display
  the results of this query.
• Design - If a previously saved query is
  highlighted, clicking this button will display
  the design query window filled in with the
  settings for this query.
• New - An empty design query window will be
  displayed

30
Queries

• Note the table(s) used in the query display at
  the top.
• If you clicked the Design button, the table(s)
  used in this saved query will be displayed.
• If you clicked the New button, you will be given
  the opportunity of selecting the tables through
  the Show Table.

31
Show Table
To add tables to the query, highlight the table
name and click Add. When finished click Close
Tables saved for this database
32
Query Design Window
Back to Queries slide
33
The Query Design Window

• A Window that isolates the important parts of a
  query. (Design view)
• Two Important parts to the Window
• Table(s) used in the query (top half)
• The Query Grid

...
34
Parts of the Query Design Windowthe table(s)
used in the query
Example of a selected table to use in a query,
appears in the top half of the Query Design Window
Customers Customer Id First Name Last
Name Address
Table name
Field Names
35
Parts of the Query Design Windowthe query grid
Drop down box of the Field Names from the
selected tables
36
Field

• Drop down box, depends on the tables you selected
  to include either when the show table dialog box
  appeared (New) or from the original query
  (Design) . Once selected, the fields from the
  selected tables will be used in the query.

37
Table

• You can select several tables for a query
  operation.
• Once you have selected the Field , this entry is
  automatically entered by Access. Access will
  enter the table name of the field you selected.

38
Sort

• You have the option of sorting the matching
  records
• Just left click on the box and a drop down menu
  will appear,
• Ascending, Descending and none
• Can sort on more than one field, the first field
  listed (the leftmost) is the major key.
• Remember only the query is sorted not the
  original table(s)

39
Show

• If you want this field data to appear in the
  results
• An empty Show box indicates that the field data
  will not appear in query results.
• A checked Show box indicates that the field data
  will appear in query results.
• Objective remove unnecessary or duplicate fields

40
Criteria

• The final part, the criteria, (the comparison),
  determines how the query will do its comparisons.
• Using expressions (just like Excel), combinations
  of field names, constant values, arithmetic and
  logic operators, you can restrict the number of
  records returned by a query

41
Our Question (Query)?
Who are our customers with the first name of
John? (list them alphabetically)
Customers Customer Id First Name Last
Name Address
Select the Customers database
This can be answered by a completing a Query
Design Window.
42
Filled in Query Design Window to Answer our
Question
Customers Customer Id First Name Last
Name Address
Display in alphabetical order the last names of
customers with the first name of John
Field
First Name
Last Name
Table
Customers
Customers
Sort
Ascending
Show
Criteria
John
Or
43
The Results

• A list will appear when the run command is
  clicked, the ! Icon.

Only Last Name field data displays Also note that
the Johnaton(s) are not listed Must use wild
cards John
Last Name Black Braithwaite Dewey Dickenson Dobbin
s Duran

44
Access Automatic FILL

• When you entered in the string John, Access will
  automatically place the quotes
• John John
• If you entered the string John, Access will
  automatically change the field to
• John Like John
• Or you can put these in yourself.

45
String Criteria

• We can match strings for text type fields
• We can use the wild cards and ? for
  substitution of characters
• - 0 or more character substitution
• Like John
• matches John, Johnaton, Johns, Johnstone
• but not Jon, Jjohn
• ? only one match and not anymore
• Like 285?5???
• matches 285 5555, 285- 5555
• but not 765-285-5555, 1-285-5555, 285-51111

46
Our Next Question?
Who are our customers with the first name of John
or Susan? (list them alphabetically)
Customers Customer Id First Name Last
Name Address
Our database
This can be answered by a completing a Query
Design Window.
47
Design Query
Customers Customer Id First Name Last
Name Address
Display in alphabetical order the last names of
customers with the first name of John or Susan
Field
First Name
Last Name
Table
Customers
Customers
Sort
Ascending
Show
Criteria
John
Or
Susan
48
The Results

• A list will appear when the run command is
  clicked, the ! Icon.

Last Name Black Braithwaite Dewey Dickenson Dobbin
s Duran Doolittle
Only Last Name field data displays Also you note
that the Johnaton(s) and Susanne(s) are not
listed. Must use wild cards in strings John
Susan

49
OR

• connectors
• allows you to link comparisons to make it
  possible for you to analyze data in various ways
• logical operators, because they act in
  comparisons the way mathematical operators work
  in the Spreadsheet

50
Or Conjunction

• when one or more of the conditions needs to be
  true we use the or conjunction
• color equal to green OR color equal to yellow
• color equal to green OR part number equal to 948
• one listed in the criteria row, the other in the
  or row

51
Numeric Criteria - Logic Operators

• Greater than (gt)
• Less than (lt)
• equals ()
• greater than or equal to (gt)
• less than or equal to (lt
• not equal to (ltgt
• (same as Excel If expressions)

52
Design Query
Customers Customer Id First Name Last
Name Address Total Purchase Age
Table name
Display in descending order the total purchase
of customers who are under 30 years of age
Field
Age
Total Purchase
Last Name
Table
Customers
Customers
Customers
Sort
Descending
Show
Criteria
lt30
Or
53
The Results

• A list will appear when the run command is
  clicked, the ! Icon.

Total Purchase 1900.50 1410.26 1167.88 900.88
469.34 367.99
Last Name Smith Buy Jones Smith Feather Johnson
Age 24 21 26 20 14 29
The query is sorted by the amount of the total
purchase

54
Design Query
Orders Customer Id Order Product ID
Quantity Unit Price Date of Purchase Date of
Payment
Table name
Display all orders which have not been paid by
oldest date
Field
Date of Purchase
Date of Payment
Order
Table
Orders
Orders
Orders
Sort
Ascending
Show
Criteria
Is Null
Or
55
Criteria

• Null - the absence of data - an empty field.
• If a field has no entry, it is called a null
  value. This is not the same as a space or zero!
• The computer treats dates as if they were
  sequential numbers that increase with time

56
The Results

• A list will appear when the run command is
  clicked, the ! Icon.

Order 146678 145778 145807 145900 145910 145950
Date of Purchase 3/10/2000 3/12/2000 3/15/2000 3/1
9/2000 3/21/2000 3/24/2000
Date of Payment
The query is sorted by the date of purchase Note
that the Date of Payment could have been marked
to not display.

57
Design Query
Orders Customer Id Order Product ID
Quantity Unit Price Date of Purchase Date of
Payment
Table name
Display all orders which were paid this month
Field
Date of Purchase
Date of Payment
Order
Table
Orders
Orders
Orders
Sort
Ascending
Show
Criteria
gt4/1/2000
Or
58
The Results

• A list will appear when the run command is
  clicked, the ! Icon.

Order 146578 145578 145607 145600 145710 145750
Date of Purchase 3/02/2000 3/02/2000 3/05/2000 3/0
5/2000 3/06/2000 3/06/2000
Date of Payment 4/2/2000 4/2/2000 4/4/2000 4/5/200
0 4/6/2000 4/6/2000
The query is sorted by the date of purchase Note
that the Date of Payment could have been marked
to not display.

59
Access Automatic FILL

• When you entered in the string gt4/1/2000, Access
  will automatically place the s
• gt4/1/2000 gt4/1/2000
• Or you can put these in yourself.

60
Simple Selection Query
Customers Customer Id First Name Last
Name Address
Display all of the customers with the name John
Smith
Field
First Name
Last Name
Table
Customers
Customers
Sort
Show
Criteria
John
Smith
Or
61
And Conjunction

• The AND conjunction says that both conditions
  must be present for the record (row) to be
  queried
• to combine criteria with AND, place the criteria
  on the same line

62
Combining Multiple Conjunctions

• when combining with all Ors - no problem
• when combining with all Ands - no problem
• when combining with both Ands and Ors - order is
  important

63
Design Query
Customers Customer Id First Name Last Name Age
Display in alphabetical order the last names of
customers with the first name of John or Susan
that are older than 50 years of age.
Field
First Name
Last Name
Age
Table
Customers
Customers
Customers
Sort
Ascending
Show
Criteria
John
gt50
Or
Susan
64
The Records in the Database
First Name Last Name
Age David Done
33 Fred
Flinstone 55 Susan
Johnson 43 Susan
Peters 34 John
Peters
39 John Smith
55 Susan Smith
52
65
Result of Query
First Name Last Name
Age Susan Johnson
43 Susan Peters
34 John
Smith 55 Susan
Smith 52
Note that the records matched any record with the
first name of Susan and records that had the
first name of John and were older than 50
(FirstName John AND Age gt 50) OR FirstName
Susan
66
But what if wanted only those records with first
name of Susan who were age 50 or older or records
with the first name of John who were age 50 or
older or
First Name Last Name
Age John Smith
55 Susan Smith
52
Note that the records matched any record with the
first name of Susan and records that had the
first name of John and were older than 50
(FirstName John OR FirstName Susan) AND
age gt 50
67
Design Query
Customers Customer Id First Name Last Name Age
Display in alphabetical order the last names of
customers with the first name of John or Susan
that are older than 50 years of age.
Field
First Name
Last Name
Age
Table
Customers
Customers
Customers
Sort
Ascending
Show
Criteria
John
gt50 gt50
Or
Susan
68
Result
First Name Last Name
Age John Smith
55 Susan Smith
52
Note that the records matched any record with the
first name of Susan who was older than 50 years
of age or records that had the first name of John
who were older than 50 years of age. (FirstName
John and age gt 50) OR ( FirstName Susan
and age gt 50)
69
Three fields using AND and OR
70
Meaning

• Be careful of the wording when designing queries.
• For example if I asked you to give me a list of
  classes that are held at 900 A.M and 1000 A.M,
  I am using the word AND to mean both (not the
  connective AND)
• BOTH-gt OR

71
Questions