Agenda Hybrid Review Create Tables Add Edit Data Create Relationships in MS Access 2010 Queries Create Tables Create Tables For the tables for the sample in the Wk10TableInstanceChartxlsx ID: 628978
Download Presentation The PPT/PDF document "Week 11 Database Design" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Week 11
Database Design Slide2
Agenda
Hybrid Review
Create Tables
Add, Edit Data
Create Relationships in MS Access 2010
QueriesSlide3
Create TablesSlide4
Create Tables
For the tables for the sample in the Wk10_TableInstanceChart.xlsx Slide5
Table Design View
Exercise:
Create table for genderSlide6
Datasheet View
Exercise:
Add data to the gender tableSlide7
Table Design View
Exercise:
Create Ribbon > Table Design in the Tables grouping
Identify your own PK
Moving the order of the rowsSlide8
Create Table with Lookups
Exercise:
Add data to the city entity using a look up wizard as the data type for the provinceSlide9
Add Data to an Entity
Based on another entity
Exercise:
Create the students table (
studentID
,
fname
,
lname
and gender fields only)Slide10
Edit an Entity
Exercise:
Complete the Students table
Go back and look at your default value (note it was not added to previous records entered)Slide11
Edit Data in Datasheet View
Exercise:
Go back and add the age group and city for each of the students previously entered
Datasheet view
Record count
Add a new record
Search for a record
Sort the recordsSlide12
Create relationshipsSlide13
Create Relationships
Database Tools ribbon > relationships grouping
Exercise:
How to Edit a relationship
How to Create a relationshipSlide14
Want More Information
Visit the following site.
http://
www.functionx.com/access/index.htmSlide15
QueriesSlide16
Northwind DatabaseSlide17
Single Table Queries
Select fields to display
Sort by?
Simple Conditions
When you enter text into the criteria cell your text should be enclosed in quotes ("") to distinguish it from other expressions and operators that you may need to add.Slide18
Working with TextSlide19
Matching TextSlide20
OR StatementSlide21
IN StatementSlide22
NOT StatementSlide23
NOT IN StatementSlide24
Using Wildcards
Wildcard is a special character that can stand for either a single character or a string of textSlide25
LIKE “Text*” StatementSlide26
LIKE “*Text” StatementSlide27
LIKE “[letter-letter]*” StatementSlide28
Working with NumbersSlide29
Working with Numbers
For example, where X represents a number:
<X
finds values less than X.
>X
finds vales greater than X
>=X
finds values greater than or equal to X
<>X
finds vales not equal to X Slide30
Working with datesSlide31
Working with Dates
Ensure the
criteria cell
is type Date/Time
Each
date must be enclosed by hash marks (#).
For example:
<#1/1/98#
f
inds
dates earlier than 1 January 1998 Slide32
Working with Dates
For
example:
=#
27-Sep-50#
finds
dates equal to 27 September 1950 Slide33
Working with Dates
For
example:
Between
#5/7/98# And #10/7/98#
finds
dates no earlier than 5 July 1998 and no later than 10 July 1998 Slide34
Finding empty fieldsSlide35
Finding Empty Fields
Use
the expression
Is Null
.
Conversely
,
Is
Not Null
.
The
expression
Null
simply means "nothing".Slide36
Hybrid / Homework
Read
Calculating in Access Queries
Read
Calculating Totals in Access
Read
Working out Someone’s Age
Watch Multi-Table Queries Part
1
&
2
Database Assignment Part 2Slide37
Quiz Time