Transcript Access 2

Access 2 - Continued
Navya Thum
[email protected]
Outline
• Access 2 Continues
– Creating a Three-Table Database
– Data Types
• AutoNumber
• Yes/No
• Lookup Wizard
– Forms & SubForms
• Much of what we do today will likely be useful
for your Homework 4 project
7/20/2015
Access Chapter 2_2
2
The Rock & Roll Hall of Fame Database
Example
• Premise: We want to create a database to keep
track of Bands & Members
– One Band has Multiple Members
• For the members, we also want to track their
country of origin.
– There could be one country for many members.
• 3 Tables, 2 relationships…
7/20/2015
Access Chapter 2_2
3
Table Relational Structure
∞
1
Members
Bands
∞
1
Countries
7/20/2015
Access Chapter 2_2
4
AutoNumber Primary Key Example
Create a New Blank Database called
lastname_firstname_music
Switch to Design View and create a Bands table
with a field called BandNum of type AutoNumber
(Primary Key)
Create a field called BandName of type Text
Save the table and enter these records in
Datasheet view:
1, The Who
2, Van Halen
…
5
7/20/2015
Access Chapter 2_2
AutoNumber Example Continued
 Create another table: Create ribbon > Table
 Call it Members and enter these fields:
Member AutoNumber
(Primary Key)
BandNum Number (will relate to “Band” Table later)
* NOTE: Autonumber from Band Table is compatible with Number type
in this table. DO NOT USE AUTONUMBER on both sides of the
common field !
FirstName Text
LastName Text
…
7/20/2015
Access Chapter 2_2
6
Second Table from Example
Flip to Datasheet view and Save the table
Enter these records:
1, 1, John,
Entwhistle
2, 1, Pete,
Townsend
3, 1, Roger,
Daltry
4, 1, Keith,
Moon
…
7/20/2015
Access Chapter 2_2
7
Second Table Completion
Enter these too…
5, 2, David Lee,
6, 2, Edward,
7, 2, Alex,
8, 2, Michael,
Roth
Van Halen
Van Halen
Anthony
* Close both open tables and we will come back to this
later…
7/20/2015
Access Chapter 2_2
8
Using “Lookup” Type Fields
•
•
They allow for clicking a drop down arrow when
entering field values while in datasheet view to
select predefined items as opposed to having to
type them.
Two types of lookup fields:
1. Lookup references uses field values in another table
2. Lookup references items you manually enter in
advance in the same table
We will now do both types…
7/20/2015
Access Chapter 2_2
9
Lookup from Other Table Example
Create the third table for our database called
Countries
Make these fields:
ID
AutoNumber
(Primary Key)
Country
Text
Flip to Datasheet view and save the table
…
7/20/2015
Access Chapter 2_2
10
Lookup from Other Table Example
Create the following Records:
1,
England
2,
America
Close the table
…
7/20/2015
Access Chapter 2_2
11
Lookup from Other Table Example
Return to the “Members” table in Design View
Add a 5th field called Origin of type Lookup
Wizard
Use “I want the lookup column to look up values
in a table or query”
Select “Table: Countries”
…
7/20/2015
Access Chapter 2_2
12
Lookup from Other Table Example
Add the Country field to Selected fields
Next… Next… Finish
Save if prompted
Return to Datasheet View in “Members”
Click in the Origin file to assign these:
Assign “England” to The Who members
Assign “America” to Van Halen members.
7/20/2015
Access Chapter 2_2
13
Same Table Lookup Example
(Like Step 5h)
In the Members table of our example, return to
Design View
Create a field called Instrument of type Look-up
Wizard…
Select I will type the values I want.
Enter the following:
Vocals, Drums, Guitar, Bass
Save, Return to Datasheet view to enter them!
7/20/2015
Access Chapter 2_2
14
“Yes/No” Field Type Example
Add a field called “Living”
Make it of Type Yes/No
Save the table and mark all records except Keith
Moon and John Entwhistle as Living members
* We will come back to this
7/20/2015
Access Chapter 2_2
15
Create a Relationship Example
Close the tables
Open Relationships
Note that lookup wizard created one already!
Right click, Show Table, add Bands.
Drag [Bands/BandNum] to [Members/BandNum]
Enforce referential, Create, and save.
…
7/20/2015
Access Chapter 2_2
16
Query Example
• We want to use the relationships we created
between the tables to show the names of the
artists, their band names, and their countries of
origin despite only having a band number in the
Member table and a lookup for country.
• We can use the relationship to seamlessly query
between fields in the three tables and give the
desired results…
7/20/2015
Access Chapter 2_2
17
Query Example
Create query in design view
Create Ribbon > Query Design button
Add all tables (note the relationships showing)
From Bands table double click to add: BandName
From Members table add:
FirstName, LastName, Instrument, Living
From Countries table add: Country
Run the Query !
Close and Save Query as RockTrivia
7/20/2015
Access Chapter 2_2
18
Sorting Results by Data Type…
7/20/2015
Access Chapter 2_2
19
Sort Exercise
Open the RockTrivia Query in Design View
In the Sort row for the BandName field, have it
sort the records in Ascending Order
Run the Query
…
7/20/2015
Access Chapter 2_2
20
Two-Level Sort – “LastName first”
Leave the existing sort in place and move the
LastName field so that it is before the
FirstName field in the Design view…
Click once to select it… let go and then click and
drag to move it over
Add a secondary sort by having it do Last Name
in Ascending order
Run it. Last names are now show alphabetically
inside the band names
7/20/2015
Access Chapter 2_2
21
We can also use “Wildcards”
7/20/2015
Access Chapter 2_2
22
Wildcard Example
We want to see all members whose last names
begin with “E”…
Remove all existing sort options
In the LastName field, type the letter E in
Criteria and run the query.
(No results show as no one has just the letter E
for a last name)
Make it read E* and rerun to get any other
characters…
7/20/2015
Access Chapter 2_2
23
Wildcards continued
Change the criteria to show people whose last
names end in “N”
*N
Run it and we should see 6 records.
Remove all criteria.
7/20/2015
Access Chapter 2_2
24
Using Fields without Showing
• It is possible to use a field in searching but not
show that field in the query results
• Let’s say we want to see all band members
whose bands begin and end in “d”, but not see
the band name
Remove the “Show” row checkmark for BandName
Create the criteria and run !
7/20/2015
Access Chapter 2_2
25
Forms & Subforms – IMPORTANT!
• You can create a form with a subform by
including fields from each of two tables that
have an established relationship formed
• When the relationship between the tables is a
one-to-many relationship, the main form will
consist of data from the primary table and the
subform will consist of data from the related
table
7/20/2015
Access Chapter 2_2
26
Form with Subform Sample
Main
Form
SubForm
Main Form Controls
7/20/2015
Subform Controls
Access Chapter 2_2
27
Form with Subform Example
Create form by using wizard
Create ribbon > More Forms button > Form Wizard
Click the “Tables/Queries” drop down and select
the Bands table, add BandName
…
7/20/2015
Access Chapter 2_2
28
Form/Subform Critical Step!
• Switch to another table to add more fields!
Click the “Tables/Queries” drop down and
switch to the Members Table
Add: FN, LN, Origin, Instrument, and Living
Click Next, and verify “Form with subform(s)” is
shown at the bottom
7/20/2015
Access Chapter 2_2
29
Form with Subform Example
Next > Select Tabular layout
Next > Select Flow style
Click Finish
Use the bottom navigation controls to show the
band up top and the members in the bottom!
7/20/2015
Access Chapter 2_2
30
Just for fun…
• Use the form to add records to 2 tables at once!
Click the New Record button at the bottom
Type Def Leppard for the band name
Enter Joe Elliot, England, Vocals, Living
Close the form and look in the Bands and
Members tables !
7/20/2015
Access Chapter 2_2
31
Importing Data
• XML Data
– Very similar to Excel
• Excel Data Import
7/20/2015
Access Chapter 2_2
32
Next Class
Try to go over this lecture slides step by step
Read over Access 2 in text
7/20/2015
Access Chapter 2_2
33