DATABASE

WHAT IS DATABASE?

• A DATABASE IS A COLLECTION OF INFORMATION THAT IS

ORGANIZED SO THAT IT CAN BE EASILY ACCESSED,
MANAGED AND UPDATED.
• DATA IS ORGANIZED INTO ROWS, COLUMNS AND TABLES,
AND IT IS INDEXED TO MAKE IT EASIER TO FIND RELEVANT
INFORMATION.
 RELATIONAL DATABASE

• RELATIONAL DATABASES ARE MADE UP OF A SET OF

TABLES WITH DATA THAT FITS INTO A PREDEFINED
CATEGORY. EACH TABLE HAS AT LEAST ONE DATA
CATEGORY IN A COLUMN, AND EACH ROW HAS A
CERTAIN DATA INSTANCE FOR THE CATEGORIES WHICH
ARE DEFINED IN THE COLUMNS.
 STRUCTURED QUERY LANGUAGE

• THE STRUCTURED QUERY LANGUAGE (SQL) IS THE

STANDARD USER AND APPLICATION PROGRAM
INTERFACE FOR A RELATIONAL DATABASE.
 MICROSOFT ACCESS

• IS A DATABASE MANAGEMENT SYSTEM (DBMS)

FROM MICROSOFT
• IT IS A MEMBER OF THE MICROSOFT OFFICE SUITE
OF APPLICATIONS, INCLUDED IN THE PROFESSIONAL
AND HIGHER EDITIONS.
 MICROSOFT ACCESS

• LIKE RELATIONAL DATABASES, MICROSOFT ACCESS ALSO

ALLOWS YOU TO LINK RELATED INFORMATION EASILY.
FOR EXAMPLE, CUSTOMER AND ORDER DATA. HOWEVER,
ACCESS 2013 ALSO COMPLEMENTS OTHER DATABASE
PRODUCTS BECAUSE IT HAS SEVERAL POWERFUL
CONNECTIVITY FEATURES.
 MICROSOFT ACCESS

• AS ITS NAME IMPLIES, ACCESS CAN WORK

DIRECTLY WITH DATA FROM OTHER SOURCES,
INCLUDING MANY POPULAR PC DATABASE
PROGRAMS, WITH MANY SQL (STRUCTURED
QUERY LANGUAGE) DATABASES ON THE DESKTOP,
ON SERVERS, ON MINICOMPUTERS, OR ON
MAINFRAMES, AND WITH DATA STORED ON
 MICROSOFT ACCESS

• ACCESS CAN WORK WITH MOST POPULAR

DATABASES THAT SUPPORT THE OPEN DATABASE
CONNECTIVITY (ODBC) STANDARD, INCLUDING
SQL SERVER, ORACLE, AND DB2.
 MICROSOFT ACCESS
• DATABASE CREATION − CREATE YOUR MICROSOFT ACCESS DATABASE
AND SPECIFY WHAT KIND OF DATA YOU WILL BE STORING.
• DATA INPUT − AFTER YOUR DATABASE IS CREATED, THE DATA OF EVERY
BUSINESS DAY CAN BE ENTERED INTO THE ACCESS DATABASE.
• QUERY − THIS IS A FANCY TERM TO BASICALLY DESCRIBE THE PROCESS
OF RETRIEVING INFORMATION FROM THE DATABASE.
• REPORT (OPTIONAL) − INFORMATION FROM THE DATABASE IS
ORGANIZED IN A NICE PRESENTATION THAT CAN BE PRINTED IN AN
ACCESS REPORT.
DATABASE MANAGEMENT SYSTEM ( DBMS)

• SOFTWARE THAT CREATES AND MANAGES A

DATABASE
• MICROSOFT’S DBMS IS CALLED ACCESS
 DATABASE FILES
• THINK OF A DATABASE AS AN ELECTRONIC FILE
CABINET
• EACH CABINET ( SET OF DRAWERS) IS A COLLECTION
OF DATA
• THIS IS MANUAL DATABASE
 DATABASE RECORDS

• WITHIN EACH DRAWER IS A SET OF FOLDERS

• THE FOLDERS CONTAIN DATA THAT IS RELATED
• EACH FOLDER REPRESENTS A RECORD WITHIN THE
DATABASE
 DATABASE FIELDS

• WITHIN EACH FOLDER ARE PIECES OF INFORMATION

• EACH SMALL PIECE OF INFORMATION REPRESENTS A
FIELD
 DATABASE HIERARCHY

• FIELD (SMALLEST UNIT)

• RECORD ( COLLECTION OF RELATED FIELDS)
• FILE ( COLLECTION OF RELATED RECORDS
 WORKING WITH ACCESS DATABASE

• DATABASES ARE COLLECTIONS OF RELATIONSHIPS

BETWEEN INFORMATION
• THEY ARE INTANGIBLE
• WE CAN VIEW ACCESS DATA IN DIFFERENT WAYS
• MICROSOFT CALLS THESE TOOLS OBJECTS
 COMMON ACCESS OBJECTS

•TABLES
•FORMS
•QUERIES
•REPORTS
 ACCESS TABLES
• COLUMNS (FIELDS) AND ROWS (RECORDS)
• LOOKS LIKE EXCEL SPREADSHEETS
• DIFFER BECAUSE THEY MAINTAIN RELATIONSHIPS
 ACCESS FORMS

• RECORDS IN ACCESS CAN BE ORGANIZED AS

FORMS
• SHOWS 1 RECORDS AT A TIME
• USED TO ENTER, VIEW, OR EDIT DATA
 ACCESS QUERIES

• INFORMATION CAN BE EASILY PULLED OUT OF A

DATABASE
• ASKING ACCESS FOR INFORMATION IS CALLED
QUERY
• EASILY LOCATES DATA.
 ACCESS REPORTS

• SUMMARIZE DATA
• PRINT OUT SELECTED INFORMATION
• CUSTOMIZE REPORTS TO ORGANIZE DATA
• USE ONLY INFORMATION NEED
 DATABASE ARE ALL AROUND US
• SCHOOLS KEEP DATA ON STUDENTS
• GOVERNMENT KEEPS DATA ON EARNINGS AND TAXES
• CREDIT INFORMATION IS IN DATABASES
• GROCERY STORES TRACK PURCHASES
• WEBSITE COLLECT USER INFORMATION (COOKIES)
• DATABASES DRIVE THE INFORMATION AGE AND MODERN
ECONOMY
DATABASE CREATION AND
MANAGEMENT
MICROSOFT ACCESS
 OVERVIEW OF ACCESS DATABASE
• ONE OF DATABASE MANAGEMENT SYSTEMS
SOFTWARE.
• ACCESS, SQL SERVER, ORACLE, DB 2
• MS ACCESS IS A RELATIONAL DATABASE.
• A COLLECTION OF TABLES THAT ARE
RELATED TO ONE ANOTHER BASED ON A
COMMON FIELD.
 RELATIONAL DATABASE

A schematic diagram of a relational database and a

sample part of a relational database showing different
tables
 OPEN AN EXISTING DATABASE
• CREATE A FOLDER BY YOUR LAST NAME IN MY
DOCUMENTS OR ON DESKTOP
• DOWNLOAD “RESTAURANT1” DATABASE INTO THE
FOLDER BY YOUR LAST NAME THEN, OPEN THE
“RESTAURANT1” DATABASE
 OPEN AN EXISTING DATABASE
• TO OPEN AN EXISTING DATABASE, YOU MUST FIRST
START ACCESS
• WHEN ACCESS IS LAUNCHED YOU WILL SEE THE
ACCESS WINDOW, WITH THE TASK PANE ON THE
RIGHT SIDE OF THE WINDOW.
• FROM THE TASK PANE, YOU CAN OPEN AN EXISTING
DATABASE.
• OR SIMPLY DOUBLE-CLICK THE EXISTING DATABASE
TO OPEN.
HOW ACCESS CREATES AND SAVES A NEW
DATABASE

• CREATE A NEW DATABASE

• DATABASE NAME: YOUR LAST NAME + FIRST INITIAL
OF FIRST NAME
HOW ACCESS CREATES AND SAVES A NEW
DATABASE

• WHEN YOU PRESS THE SAVE BUTTON IN ACCESS, YOU

ARE SAVING THE DESIGN OF THE ACCESS OBJECTS
AND NOT THE DATABASE ITSELF!
• THE SAVE FUNCTION IN ACCESS DIFFERS FROM THE
SAVE FUNCTION IN OTHER WINDOWS PROGRAMS.
 CHARACTERISTICS OF RELATIONAL DB
• IN A RELATIONAL DATABASE, EACH RECORD (ROW) IN
A TABLE MUST BE UNIQUELY IDENTIFIED.
• USING PRIMARY KEY
 CHARACTERISTICS OF RELATIONAL DB
• A RELATIONAL DATABASE IS A COLLECTION OF TABLES
THAT ARE RELATED TO ONE ANOTHER BASED ON A
COMMON FIELD.
• USING FOREIGN KEY (& PRIMARY KEY)
 PRIMARY KEY (PK) & FOREIGN KEY (FK)

• PK: A FIELD THAT UNIQUELY IDENTIFIES EACH RECORD

IN A TABLE.
• SS#, STUDENT ID
• IT DOES NOT HAVE TO BE FIRST FIELD.
 PRIMARY KEY (PK) & FOREIGN KEY (FK)

• FK: A FIELD THAT CONNECTS ONE TABLE LOGICALLY

WITH ANOTHER TABLE
• RULE OF THUMB: PK = FK (VALUE) – SEE NEXT SLIDE
 RELATING TABLES USING PK AND FK

The primary key in the

Employer table (EmployerID)
is the common field that
relates this table to the
Position table.

PositionID is the primary key in

the Position table. The
EmployerID field is a foreign key
in this table.

Primary keys can only have one

occurrence in a table. Foreign keys
may have multiple occurrences.
 VALLE COFFEE’S RESTAURANT DB
• VALLE IS A SMALL DISTRIBUTOR OF INEXPENSIVE COFFEE
BEANS TO VARIOUS RESTAURANTS. BARBARA HENNESSEY,
THE DIRECTOR OF CRM, AND HER STAFF USE ACCESS TO
MAINTAIN COMPANY DATA SUCH AS CUSTOMER ORDERS
AND BILLING, COFFEE SUPPLIER ORDERS AND PAYMENTS,
AND ADVERTISING PLACEMENTS AND PAYMENTS.
 VALLE COFFEE’S RESTAURANT DB
• BARBARA HAS A RECENTLY DEVELOPED DATABASE
NAMED RESTAURANT 1 TO TRACK THE COMPANY’S
RESTAURANT CUSTOMERS, THEIR ORDERS, AND RELATED
DATA SUCH AS THE PRODUCTS THEY ORDER. SHE ASKS
FOR YOUR HELP IN COMPLETING AND MAINTAINING
THIS DATABASE.
 DESCRIPTIONS OF RESTAURANT DB

• VALLE COFFEE’S RESTAURANT 1 DATABASE WILL CONTAIN

FIVE TABLES:
• CUSTOMER TABLE, WHICH BARBARA ALREADY HAS.
• ORDER TABLE, WHICH YOU WILL CREATE SOON.
• PRODUCT AND ORDER DETAIL TABLES, WHICH YOU WILL IMPORT
FROM FINEFOOD DATABASE.
• BILLING ADDRESS TABLE THAT IS IN EXCEL FORMAT AND YOU
WILL IMPORT IT, AND THEN CONVERT TO ACCESS TABLE.
 CREATING A ORDER TABLE
• BARBARA WANTS TO TRACK INFORMATION ABOUT
EACH ORDER PLACED BY EACH RESTAURANT
CUSTOMER. THIS INFORMATION INCLUDES THE
ORDER’S BILLING DATE AND INVOICE AMOUNT.
BARBARA ASKS YOU TO CREATE A SECOND TABLE IN
THE RESTAURANT DATABASE, NAMED ORDER, IN WHICH
TO STORE THE ORDER DATA.
 CREATING A ORDER TABLE
• USE DESIGN VIEW
• CASE SENSITIVE
• NUMBER VS. NUMBER
• EXACT MATCH OF EACH NAME
• USE TAP KEY TO ENTER DATA
• ESPECIALLY, WHEN YOU ENTER DATA IN DATASHEET
VIEW.
 ACCESS DATA (FIELD) TYPE
Make certain the field type you select matches the data to be held in that field.
 ACCESS DATA (FIELD) TYPE (CON’T)
Additional Access field types.
 CREATING THE ORDER TABLE

Field Name Data Type Description Field Properties

OrderNum Text primary key Field size (3), Required (Yes)

CustomerNum Text foreign key Field size (3)

BillingDate Date/Time

PlacedBy Text person who Field size (25)

placed order

InvoiceAmt Currency
 ADDING RECORDS TO A TABLE
• ENTER DATA SEQUENTIALLY IN DATASHEET VIEW
• DO NOT JUMP FROM FIELD TO FIELD
• USE TAP KEY TO ENTER DATA

OrderNum CustomerNum BillingDate PlacedBy InvoiceAmt

323 624 02/15/2001 Mike Smith $1,986.00
201 107 01/15/2001 Matt Davis $854.00
 MODIFYING A TABLE

• FROM THE ORDER TABLE

• DELETE THE PLACEDBY FIELD
• MOVE THE BILLINGDATE FIELD TO THE END OF THE TABLE
• INSERT THE PAID AS A NEW FIELD BETWEEN CUSTOMERNUM AND
INVOICEAMT (POSITION) FIELDS
• DATA TYPE: YES/NO
• DEFAULT VALUE: NO (MEANS “UNPAID”)
• ADD FOLLOWING DATA TO EACH FILED: 211, 201, PAID (MARK
THE CHECK BOX USING SPACE BAR), $703.50, 01/15/2001
 PRACTICE: CREATING A DB
• BARBARA NEEDS A DATABASE TO TRACK THE COFFEE
PRODUCTS OFFERED BY VALLE COFFEE. SHE ASKS YOU TO
CREATE THE DATABASE BY COMPLETING THE FOLLOWING:
• IN THE INITIAL MICROSOFT ACCESS DIALOG BOX, CLICK THE
BLANK ACCESS DATABASE OPTION BUTTON, AND THEN CLICK
OK BUTTON. CLICK THE CREATE BUTTON THE NEW DATABASE.
• YOUR LAST NAME DB FILE NAME

• DISPLAY THE TABLE WINDOW IN DESIGN VIEW (IF

NECESSARY), AND THEN CREATE A TABLE USING THE TABLE
DESIGN SHOWN IN THE NEXT SLIDE.
 PRACTICE: CREATING A TABLE
Field Name Data Type Description Field Properties
ProductCode Text Primary Key Field size (4), Required: Yes
CoffeeCode Text Foreign Key Field size (4)
Price Currency Price for this product
Decaf Text D if decaf, Null if Field size (1), Default Value: D
regular
BackOrdered Yes/No back-ordered from Default Value: No
supplier?
• SPECIFY PRODUCTCODE AS THE PRIMARY KEY, AND THEN SAVE THE TABLE AS
PRODUCT.
• ADD THE PRODUCT RECORDS SHOWN IN NEXT SLIDE TABLE TO THE PRODUCT
TABLE. (HINT: YOU MUST TYPE THE DECIMAL POINT WHEN ENTERING THE PRICE
FIELD VALUES.)
 PRACTICE: MODIFYING A TABLE
ProductCode CoffeeCode Price Decaf BackOrdered
2316 JRUM 8.99 Yes
9754 HAZL 40.00 D Yes
9309 COCO 9.99 D No
• ADD A NEW FIELD BETWEEN THE COFFEECODE AND PRICE FIELDS, USING
THESE PROPERTIES;
• FIELD NAME: WEIGHTCODE
• DATA TYPE: TEXT
• DESCRIPTION: FOREIGN KEY
• FIELD SIZE: 1
• MOVE THE DECAF FIELD SO THAT IT APPEARS BETWEEN THE WEIGHTCODE
AND PRICE FIELDS.
 PRACTICE: UPDATING A TABLE
• ENTER THESE WEIGHTCODE VALUES FOR THE THREE RECORDS: A FOR
PRODUCTCODE 2316, A FOR PRODUCTCODE 9309, AND E FOR
PRODUCTCODE 9754.
• ADD A RECORD TO THE PRODUCT DATASHEET WITH THESE FIELD
VALUES:
• PRODUCTCODE: 9729
• COFFEECODE: COLS
• WEIGHTCODE: E
• DECAF: D
• PRICE: 39.75
• BACKORDERED: YES
 DATA INTEGRITY (PK RULE)
• NO “NULL” VALUE CAN BE ALLOWED.
• NO TWO RECORDS CAN HAVE THE SAME PRIMARY KEY.
• NO TWO CSUB STUDENTS CAN HAVE SAME ID NUMBER.

• A PK CAN BE “COMPOSITE KEY”

• EXAMPLE ON CLASS WEB PAGE
• “COMPOSITE PK EXAMPLE”
• MORE THAN ONE FIELD CAN BE USED AS A PK (COMPOSITE)
• CSUB: STUDENT ID + SS#
 REFERENTIAL INTEGRITY (FK RULE)

• A FIELD THAT CONNECTS ONE TABLE LOGICALLY WITH ANOTHER TABLE.

• EXCEPTION EXAMPLE ON CLASS WEB PAGE
• “PK AS FK” THERE ARE TWO TABLES…
• RELATED TABLE: SHIPPING ADDRESS TABLE
• PRIMARY TABLE: CUSTOMER TABLE
IMPORTING EXTERNAL ACCESS TABLE AND
EXCEL WORKSHEET

• BARBARA ALSO WANTS YOU TO INCLUDE THE PRODUCT

AND ORDER DETAIL TABLES FROM THE FINEFOOD
DATABASE IN THE RESTAURANT DATABASE.
• DOWNLOAD AND REVIEW DESIGN VIEW OF FINEFOOD DB FIRST
IMPORTING EXTERNAL ACCESS TABLE AND EXCEL
WORKSHEET (CON’T)
• AND SHE WANTS YOU TO INCLUDE THE BILLING
ADDRESS EXCEL WORKSHEET AS A ACCESS TABLE IN THE
RESTAURANT DATABASE.
• BEFORE TRY TO IMPORT THE EXCEL FILE, REVIEW IT FIRST
• USE EXCEL COLUMN HEADINGS FOR ACCESS TABLE
• PK: CUSTOMERNUM
• SPECIFY IN THE DESCRIPTION AREA OF DESIGN VIEW THAT
CUTOMERNUM IS NOT ONLY PRIMARY KEY OF
BILLINGADDRESS TABLE BUT ALSO A FOREIGN KEY OF
CUSTOMER TABLE.
 ACCESS IS A RELATIONAL DATABASE

• ACCESS ALLOWS YOU TO FORM RELATIONSHIPS BETWEEN THE TABLES; THAT’S

WHY IT’S CALLED A RELATIONAL DATABASE
• THE SIMPLEST WAY TO CREATE A RELATIONSHIP
• LOOK FOR IDENTICAL FIELD NAMES BETWEEN TABLES.

• TABLES CAN BE JOINED IN THREE WAYS; ONE-TO-ONE, ONE-TO-MANY, AND

MANY-TO-MANY.
 AN EXAMPLE OF A
ONE-TO-MANY RELATIONSHIP
The Employer table is related to the Position table via the common field EmployerID.

The Employer table has

one record for EmployerID
value 10126. The Position
table has two records.
 A ONE-TO-ONE RELATIONSHIP
• A ONE-TO-ONE RELATIONSHIP EXISTS WHEN ONE TABLE HAS ONE
RECORD ASSOCIATED WITH ONLY ONE RECORD OF ANOTHER
TABLE.
• AS WE SAW FROM PK AS FK DATABASE
• SHIPPING ADDRESS TABLE IS AN RELATED TABLE.
• PRIMARY TABLE: CUSTOMER TABLE
• PRIMARY KEY IS ALSO FOREIGN KEY: ONLY ACCESS
 USING REFERENTIAL INTEGRITY
• REFERENTIAL INTEGRITY ALLOWS YOU TO MAINTAIN THE
INTEGRITY AND CONSISTENCY BETWEEN RELATED TABLES.
• IF YOU CHOOSE TO ENFORCE REFERENTIAL INTEGRITY, YOU CAN
INSURE THAT YOU WILL NOT HAVE RECORDS THAT HAVE NO
MATCHING RECORD IN THE PRIMARY TABLE.

• THE RULES ASSOCIATED WITH REFERENTIAL INTEGRITY SPECIFY

THAT WHEN YOU UPDATE OR DELETE A RECORD (PK) IN THE
PRIMARY TABLE, A MATCHING RECORD (FK) IN THE RELATED
RECORD MUST BE UPDATED OR DELETED.
 USE CASCADE UPDATE
AND CASCADE DELETE
• IN REFERENTIAL INTEGRITY, THERE ARE TWO OPTIONS.
• IF YOU CHOOSE CASCADED UPDATES, MAKING A CHANGE IN A
FIELD THAT IS COMMON TO TWO RELATED TABLES WILL CAUSE THE
UPDATE TO BE MADE IN BOTH TABLES.
• IF YOU DELETE A FIELD THAT IS COMMON TO TWO TABLES, THE
DELETION WILL TAKE PLACE IN BOTH TABLES.
• ENFORCING ACCESS REFERENTIAL INTEGRITY RULES
• TRY REFERENTIAL INTEGRITY EXAMPLE DB ON THE CLASS WEB
PAGE.
 CREATING RELATIONSHIPS
• DOWNLOAD RESTAURANT2 FILE FROM THE CLASS WEB
• CREATE RELATIONSHIPS USING 5 TABLE
• IN TERMS OF CREATING A RELATIONSHIP BETWEEN CUSTOMER
AND BILLINGADDRESS, START FROM THE CUSTOMER TABLE.
• ENFORCE BOTH CASCADE OPTIONS
• PRIMARY KEY OF THE ORDER DETAIL TABLE
• COMBINATION OF ORDERNUM AND PRODUCTCODE
• OTHERWISE, A DUPLICATION OF THE QUANTITY FIELD IN BOTH
THE ORDER AND PRODUCT TABLES.
PRACTICE: CREATING RELATIONSHIP 1
• CREATE A BLANK DATABASE (USE ANY NAME YOU LIKE) AND
THEN, IMPORT THE THREE EXCEL WORKSHEETS (COURSE,
INSTRUCTOR, AND MEMBERSHIP) FROM THE CLASS WEB SITE
INTO YOUR ACCESS DATABASE.
• DEFINE EACH IMPORTED TABLE’S PRIMARY KEY USING
INFORMATION BELOW:
• COURSE TABLE: CLASS_NUMBER
• INSTRUCTOR TABLE: EMPLOYEE_NUMBER
• MEMBERSHIP TABLE: MEMBER_NUMBER
PRACTICE: CREATING RELATIONSHIPS 2
• ESTABLISH RELATIONSHIP BASED ON COMMON FIELDS.
• AND ENFORCE REFERENTIAL INTEGRITY (APPLY BOTH
OPTIONS) AMONG THREE IMPORTED TABLES
 WHAT IS AN ACCESS QUERY?

• YOU CAN CREATE A QUERY WHEN YOU NEED ONLY A PORTION OF THE DATA FORM TABLES
(OR EXISTING QUERIES).
• FOR EXAMPLE, YOU MAY ONLY NEED TO SEE CUSTOMERS WHO LIVE IN CA. THE RESPONSE WOULD
BE TO DISPLAY ONLY THE RECORDS WHOSE STATE FIELD MATCHES WITH CA.
• MULTIPLE TABLES OR QUERIES CAN BE USED.
• RESTRICTIONS CAN BE USED
• COMPARISON OPERATORS
• THE DESIGN VIEW IS USED TO SPECIFY THE FIELDS AND RECORDS YOU WANT TO SEE.
 THE QUERY DESIGN VIEW WINDOW
Fields used in the query are selected from You can run the query at any
the field list and added to the design grid. time by clicking the Run button.
 CREATING A SAMPLE QUERY

• KIM CARPENTER, THE DIRECTOR OF MARKETING AT THE VALLE, WANTS A LIST

OF ALL RESTAURANT CUSTOMERS SO THAT HER STAFF CAN CALL CUSTOMERS
TO CHECK ON THEIR SATISFACTION WITH VALLE COFFEE’S SERVICES AND
PRODUCTS. SHE WANTS TO HAVE ONLY FOLLOWINGS; CUSTOMERNAME,
CITY, STATE, OWNERNAME, AND PHONE.
• USE RESTAURANT 3 DATABASE
• SAVE THE QUERY AS FIRST CUSTOMER LIST.
 Billing Address Table

CustomerNum BillingName Street City State Zip

129 Sandy Lookout PO Box 2800 Grandville MI 49468

Restaurant
Customer Table
CustomerNum CustomerName Street City State ZipCode OwnerName Phone FirstContact

000 Choi COB 105 CSUB CA 93311 Scott Choi 5348 09/20/2001

Order Table
OrderNum CustomerNum Paid InvoiceAmt BillingDate
201 107 No 854.00 01/15/2001

Order Detail Table

OrderNum ProductCode Qty
201 2834 11

Product Table
ProductCode CoffeeName Weight/Size Price Decaf
2301 Colombian Aged Crop 1 lb pkg 7.99
 MORE ABOUT A QUERY

• WHEN YOU USE THE QUERY DESIGN WINDOW, YOU USE QUERY BY EXAMPLE (QBE).
• DO NOT INCLUDE ANY UNNECESSARY TABLES OR QUERIES IN DESIGN VIEW OF THE QUERY.
• OTHERWISE, YOU HAVE TO DEAL WITH UNEXPECTED PROBLEMS.
• IN YOUR MIDTERM, YOU WILL BE PENALIZED IF YOU INCLUDE UNNECESSARY TABLES OR QUERIES.

• ALWAYS USE A PRIMARY TABLE.

• CUSTOMERNUM: CUSTOMER VS. BILLINGADDRESS

• RESTRICTIONS CAN BE USED AS WELL.

 CREATING QUERIES USING
MULTIPLE TABLES
• SINCE THERE HAVE BEEN SOME MAJOR CHANGES IN
THE RESTAURANT DATABASE, BARBARA WANTS TO
MAKE SURE THAT THE DATABASE IS RELIABLE. IN ORDER
TO TEST THE RELIABILITY OF DATABASE QUERY
FUNCTION, SHE WANTS YOU TO GENERATE A QUERY.
THE QUERY MUST INCLUDE FOLLOWING FIELDS: ORDER
NUMBER, COFFEE NAME, QUANTITY, PRICE, AND
WEIGHT/SIZE.
• SAVE AS TEST
• SHOULD SELECT ORDERNUM FROM THE ORDER TABLE (PRIMARY TABLE)
 QUERYING AND SORTING OF
MULTIPLE TABLES
• BARBARA WANTS TO HAVE FOLLOWING INFORMATION FOR THE UPCOMING
QUARTERLY REPORT: CUSTOMERNAME, CITY, STATE, BILLINGDATE, INVOICEAMT,
PAID, AND FIRST CONTACT.
• AT SAME TIME, BARBARA WANTS TO SORT THE RECORDS BY THE INVOICEAMT FIELD
IN ASCENDING ORDER.
• BARBARA ALSO WANTS TO MOVE THE PAID FIELD BETWEEN THE STATE AND
BILLINGDATE FIELD.
• SAVE AS CUSTOMER ORDERS
 Expressions

Words begin or end with a: LIKE a* or LIKE *a

Find between values: (NOT) BETWEEN 45000 AND 78000
Find exact match value: 1/10/2005
 FIND AN EXACT MATCH

• BARBARA WOULD LIKE TO HAVE A LIST OF ALL ORDERS BILLED ON

01/15/2001.
• THE LIST MUST INCLUDE FOLLOWING FIELDS; ORDERNUM, PAID,
INVOICEAMT, BILLINGDATE, CUSTOMERNAME, STATE, OWNERNAME, AND
PHONE
• SAVE AS JAN ORDERS
 USING LIKE A*

• BARBARA WANTS TO KNOW A LIST OF ALL CUSTOMER NAMES THAT BEGINS

WITH M. THE LIST MUST INCLUDE FOLLOWING FIELDS; CUSTOMER NAME,
ORDER NUMBER, FIRST CONTACT AND BILLING DATE. IN ADDITION, THE
ORDER NUMBER MUST BE SORTED IN ASCENDING ORDER.
• SAVE AS CUSTOMER NAME BEGINS WITH M
 USING BETWEEN ** AND **

• BARBARA WANTS TO KNOW A LIST OF ALL PRODUCT CODE BETWEEN 2465

AND 2763. THE LIST MUST INCLUDE FOLLOWING FIELDS; PRODUCT CODE,
COFFEE NAME AND PRICE.
• THERE SHOULD A SPACE BETWEEN NUMBER AND WORD

• SAVE AS PRODUCT CODE BETWEEN 2465 AND 2763.

 USING GREATER THAN (>)

• BARBARA WANTS TO KNOW THOSE RECORDS WHOSE INVOICEAMT EXCEEDS

$2,000.
• USE THE CUSTOMER ORDERS QUERY.
• THE QUERY MUST INCLUDE ONLY FOLLOWING FIELDS; CUSTOMER NAME,
INVOICE AMOUNT, AND BILLING DATE.
• SAVE AS HIGH INVOICE AMOUNTS
 USING AND AND OR OPERATORS

The And
condition.

The Or
condition.
 CREATING AND AND OR CONDITIONS
IN THE DESIGN GRID

And conditions must be

specified on the same line.
Or conditions must be specified on different lines.
 USING AND

• LEONARD ASKS BARBARA FOR A LIST OF ORDERS BILLED ON 01/15/2001

THAT ARE STILL UNPAID. HE WANTS TO KNOW WHICH CUSTOMERS ARE SLOW
IN PAYING THEIR INVOICES.
• USE THE CUSTOMER ORDERS QUERY AGAIN AND MAKE SURE THAT THIS
QUERY INCLUDES FOLLOWING FIELDS; CUSTOMERNAME, PAID, BILLINGDATE,
AND INVOICEAMT.
• 01/15/2001 AND UNPAID (NO)
• SAVE AS UNPAID JAN ORDERS
 USING OR
• LEONARD WANTS TO DETERMINE WHICH RESTAURANT
CUSTOMERS ARE MOST VALUABLE TO VALLE COFFEE.
SPECIFICALLY, HE WANTS TO SEE A LIST OF THOSE
CUSTOMERS WHO HAVE BEEN PLACING ORDERS FOR
MANY YEARS (SPECIFICALLY, FIRST CONTACT DATE -
EARLIER THAN 01/01/1994: <=01/01/1994 ) OR WHO
PLACE ORDERS FOR A SUBSTANTIAL AMOUNT OF MONEY
(GREATER THAN $2,000: >2000), SO THAT HE CAN CALL
THE CUSTOMERS PERSONALLY AND THANK THEM FOR
THEIR BUSINESS.
 USING OR

• MAKE SURE THAT THIS QUERY INCLUDES FOLLOWING FIELDS;

CUSTOMERNAME, INVOICEAMT, AND FIRST CONTACT.
• HE ALSO WANTS TO HAVE A SORTED OUTPUT IN ASCENDING ORDER BASED
ON THE CUSTOMER NAME.
• IF YOU WANT, YOU CAN USE THE CUSTOMER ORDERS QUERY AGAIN
• SAVE AS TOP CUSTOMERS
 A CALCULATED FIELD IN
THE QUERY DATASHEET
Expression Builder adds your calculated field to the query design grid. You can then
assign it a name, which will display in query datasheet view when the query is run.
Use of “Build” Function
 PERFORMING CALCULATION
• LEONARD IS CONSIDERING ADDING A 2% LATE CHARGE
TO THE UNPAID INVOICES. HE ONLY WANTS TO INCLUDE
FOLLOWING FIELDS; CUSTOMER NAME, PAID, BILLING
DATE, AND INVOICE AMOUNT.
• SET UNPAID FIELD TO “NO”
 PERFORMING CALCULATION
• AND HE WANTS TO KNOW EXACTLY WHAT THESE
CHARGES WOULD BE. HE WANTS TO CREATE A NEW FIELD
“LATE CHARGE” RIGHT AFTER INVOICEAMT FIELD AND
THEN USE IT TO CALCULATE LATE CHARGE.
• LATE CHARGE FORMULA: INVOICEAMT*0.02
• SAVE AS UNPAID WITH LATE CHARGE
 USING AGGREGATE FUNCTIONS

• BARBARA PREPARES A REPORT OF VALLE COFFEE’S RESTAURANT BUSINESS FOR

LEONARD ON A REGULAR BASIS. THE INFORMATION IN THE REPORT SHOULD
INCLUDE A SUMMARY OF THE RESTAURANT INVOICE STATISTICS: THE TOTAL
INVOICE AMOUNT FOR ALL ORDERS, THE AVERAGE INVOICE AMOUNT, AND
THE NUMBER OF ORDERS (SAME AS NUMBER OF INVOICE AMOUNT). SHE
ASKS YOU TO CREATE THESE STATISTICS.
 USING AGGREGATE FUNCTIONS
Access has several Aggregate Aggregate functions are specified in the
Functions that can be used to Total row of the design grid. They can
calculate various statistical be assigned by clicking the Totals
information. button on the Query Design toolbar.
 USING AGGREGATE FUNCTIONS

• SHE ALSO WANTS YOU ASSIGN A NEW FIELD AS FOLLOWS;

• TOTAL OF INVOICES ( TOTAL INVOICE AMOUNT FOR ALL ORDERS)
• AVERAGE OF INVOICES (AVERAGE INVOICE AMOUNT)
• NUMBER OF ORDERS (NUMBER OF INVOICE AMOUNT)

• SAVE AS INVOICE STATISTICS

 USING RECORD GROUP CALCULATIONS

• BARBARA’S ANOTHER REPORT TO LEONARD ALSO INCLUDES THE SAME

INVOICE STATISTICS (TOTAL, AVERAGE, AND COUNT). BECAUSE VALLE COFFEE
SENDS INVOICES TO ITS CUSTOMERS EACH MONTH (JAN INVOICE, FEB
INVOICE, AND MARCH INVOICE), EACH INVOICE HAS THE SAME BILLING
DATE. BARBARA ASKS YOU TO DISPLAY THE INVOICE STATISTICS FOR EACH
MONTH (BILLING DATE).
• GROUPING BY EACH MONTH
 USING RECORD GROUP CALCULATIONS

• CALCULATE STATISTICS FOR GROUPS OF RECORDS

• AVERAGE SALARY BY THE POSITION
• NUMBER OF EMPLOYEES IN EACH DEPARTMENT

• GROUP BY OPERATOR
• DIVIDES THE SELECTED RECORDS INTO GROUPS BASED ON THE VALUES IN THE SPECIFIED FIELD
• THOSE RECORDS WITH THE SAME VALUE FOR THE FIELD ARE GROUPED TOGETHER.

• SAVE AS BY BILLING DATE

 QUERY PRACTICE 1

• DOWNLOAD A DATABASE: “ROADRUNNER”

• I WOULD LIKE TO HAVE A QUERY THAT INCLUDES FOLLOWING FIELDS;
EMPLOYEE NUMBER, INSTRUCTOR LAST NAME, INSTRUCTOR FIRST NAME,
COURSE NAME AND TIME.
• SAVE AS YOUR COURSE INFO.
• I WOULD LIKE TO HAVE A QUERY THAT ONLY INCLUDES THE MALE MEMBERS
WHO LIVE IN BURBANK USING FOLLOWING FIELDS; FIRST NAME, LAST NAME,
GENDER, CITY, AND STATE.
• SAVE AS MALES IN BURBANK.
 QUERY PRACTICE 2

• I WOULD LIKE TO KNOW A LIST OF ALL MEMBERS WHOSE LAST NAME BEGINS
WITH P USING FOLLOWING FIELDS; FIRST NAME, LAST NAME, CITY, AND STATE.
• SAVE AS LAST NAMES BEGIN WITH P.
 QUERY PRACTICE 3

• I WOULD LIKE TO KNOW ONLY THE EMPLOYEE NUMBER, INSTRUCTOR LAST

NAME, INSTRUCTOR FIRST NAME, AND THE SALARY AMOUNT FOR ONLY THOSE
EMPLOYEES WHO MAKE BETWEEN $39,500 AND $55,000. IN ADDITION, THE
RESULTING SALARIES MUST ALSO BE PLACED IN ORDER FROM HIGH SALARY TO
LOW SALARIES.
• SAVE AS BETWEEN SALARIES.
 QUERY PRACTICE 4

• I WOULD LIKE TO KNOW THE CLASS NUMBER, LAST NAME, AND MEMBER
NUMBER FOR EACH MEMBER WHO HAS A CLASS NUMBER GREATER THAN OR
EQUAL TO 8900. IN ADDITION, SORT LAST NAMES IN ASCENDING ORDER.
• SAVE AS GREATER THAN OR EQUAL TO 8900.
 QUERY PRACTICE 5

• I WOULD LIKE TO KNOW A LIST OF ALL MEMBERS WHOSE LAST NAME THAT
BEGINS WITH A OR A FIRST NAME THAT BEGINS WITH R INCLUDING THE
FOLLOWING FIELDS: FIRST NAME, LAST NAME, CITY, AND STATE.
• SAVE AS NAMES WITH A OR R.
 QUERY PRACTICE 6

• I WOULD LIKE TO KNOW AN INSTRUCTOR WHO TEACHES HANDBALL AND

STUDENTS WHO ARE TAKING HANDBALL COURSE. FOLLOWING FIELDS MUST
BE APPEAR ON THE QUERY: INSTRUCTOR FIRST NAME, INSTRUCTOR LAST NAME,
COURSE NAME, MEMBER LAST NAME AND MEMBER FIRST NAME. IN ADDITION,
THE MEMBER LAST NAMES SHOULD BE SORTED IN ALPHABETICAL ORDER.
• SAVE AS HANDBALL LISTING
 QUERY PRACTICE 7

• I WOULD LIKE TO KNOW A WHOLE LIST OF THE INSTRUCTOR LAST NAME,

INSTRUCTOR FIRST NAME, AND THE SALARY AMOUNT. SINCE INSTRUCTORS
HAVE WORKED HARD, I WOULD LIKE TO INCREASE THEIR SALARY BY 15%. SO,
I’D LIKE TO CALCULATE 15% SALARY INCREASE FOR EACH INSTRUCTOR.
• SAVE AS SALARIES INCREASE
 QUERY PRACTICE 8

• I WOULD LIKE TO KNOW THE SUM, AVERAGE, MAX. AND MIN. OF EMPLOYEES
SALARY (THESE FOUR ITEMS MUST ALL APPEAR IN THE SAME QUERY).
• SAVE AS SALARY CALCULATIONS

• I WOULD LIKE TO KNOW THE SUM, AVERAGE, MAX. AND MIN. OF EMPLOYEES
SALARY BASED ON EMPLOYEE TYPE.
• SAVE AS EMPLOYEE TYPE
 FORMS

• FORMS ARE USED TO CUSTOMIZE YOUR DATA ENTRY

SCREEN.
• FORMS ARE CREATED FOR THE CONVENIENCE OF THE
USER.
• FORMS ARE USED TO UPDATE DATA TABLES.
• YOU CAN CHANGE LOCATIONS OF FIELDS AND COLORS
VERY EASILY IN THE DESIGN SCREEN.
 CREATING A FORM

• AFTER KIM LEAVES FOR HER STAFF MEETING, BARBARA ASKS YOU TO
CREATE A FORM FOR THE CUSTOMER TABLE BECAUSE THE STAFF ARE
NOT ALLOWED TO ACCESS THE DATABASE DIRECTLY. THE STAFF
MEMBERS SHOULD BE ALLOWED ONLY FOR ENTERING DATA. BUT,
USING A FORM, THE STAFF CAN WORK DATA EASILY IN THE TABLE.
• DOWNLOAD “RESTAURANT 3” DATABASE AGAIN.
• OPEN CUSTOMER TABLE TO IDENTIFY # OF RECORDS (38)
• CREATE USING “FORM WIZARD”
• LAYOUT: COLUMNAR & STYLE: INTERNATIONAL
• SAVE THE FORM AS TEST FORM
 ADDING A RECORD INTO THE FORM
• ADD FOLLOWING INFORMATION AS A RECORD NO.39
CUSTOMER;
• CUSTOMERNUM: 000
• CUSTOMERNAME: YOUR LAST NAME
• STREET, CITY, STATE, AND ZIPCODE: YOUR ADDRESS
• OWNERNAME: YOUR FULL NAME
• PHONE: YOUR PHONE NUMBER
• FIRSTCONTACT: 09/20/2001
• A RECORD NO. 39 ADDED TO THE CUSTOMER TABLE (IT’S
HAS BEEN ALTERED).
 MODIFYING A FORM
• MODIFY THE TEST FORM BASED ON BELOW
REQUIREMENTS USING THE DESIGN VIEW OF FORM
• MAKE SURE THAT CITY, STATE, AND ZIP CODE ARE ON THE SAME
LINE
• TITLE OF FIELD: LABEL BOX
• ACTUAL ENTRY SPACE: TEXT BOX
• IN ORDER TO MOVE BOTH BOXES TOGETHER, PLACE THE CURSOR
BETWEEN LABEL AND TEXT BOXES. THEN, THE SHAPE OF CURSOR
WILL BE CHANGED TO A BLACK COLORED HAND.
 MODIFYING A FORM (CON’T)

• PUT A SPACE BETWEEN WORDS FOR FOLLOWINGS:

• CUSTOMERNAME, OWNERNAME, AND FIRSTCONTACT

• CHANGE CUSTOMERNUM TO CUSTOMER NO

• CHANGE PHONE TO PHONE NUMBER
• ADD A LABEL IN THE FORM HEADER SECTION IN ORDER TO CREATE A TITLE OF THIS FORM. THE
TITLE (USE LABEL BUTTON FROM THE TOOLBOX) FOR THE FORM SHOULD BE CUSTOMER DATA
ENTRY FORM.
• THE FONT SIZE OF THE TITLE SHOULD BE A MINIMUM OF 20 AND THE TITLE MUST BE CENTERED.
 FORMS PRACTICE
• CREATE A FORM FOR THE MEMBERSHIP TABLE FROM THE
ROADRUNNER DATABASE. BEGIN YOUR FORM WITH THE
WIZARD AND THEN MODIFY IT SO THAT:
• MEMBER NUMBER IS ON THE TOP LINE
• LAST NAME AND FIRST NAME IS ON THE SAME LINE
• ADDRESS IS ON A LINE BY ITSELF
• CITY AND STATE ARE ON THE SAME LINE
• CLASS NUMBER AND GENDER ARE ON THE SAME LINE
• THE LABELS FOR MEMBER NUMBER, LAST NAME, ADDRESS, CITY,
AND CLASS NUMBER SHOULD ALL BE LINED UP VERTICALLY WITH
THE SAME LEFT BORDER/MARGIN
 FORMS PRACTICE (CON’T)

• THE LABELS FOR FIRST NAME, STATE, AND GENDER SHOULD ALL LINE UP VERTICALLY
WITH THE SAME LEFT BORDER/MARGIN.
• YOU MUST ALSO SPACE OUT YOUR FIELDS AND ROWS SO THAT THERE IS PLENTY OF
SPACE AROUND EACH FIELD.
• THEY SHOULD NOT BE CROWDED TOGETHER.
• YOU SHOULD ALSO ADD A LABEL TO YOUR FORM IN THE FORM HEADER SECTION.
THE LABEL (USE LABEL BUTTON)FOR YOUR FORM SHOULD BE MEMBERSHIP DATA
ENTRY FORM.
• THE FONT SIZE SHOULD BE A MINIMUM OF 16 POINT FONT.
• YOUR TITLE MUST ALSO BE CENTERED.
 FORMS PRACTICE (CON’T)

• YOU WILL NEED TO CHANGE THE FONT COLOR OF YOUR LABEL TEXT AND
THE FILL COLOR FOR YOUR LABEL TEXT SO THAT YOUR FORM LABEL IS EASILY
VIEWABLE ON YOUR COMPUTER SCREEN, GIVEN THE STYLE COLOR/PATTERN
THAT YOU SELECTED FOR YOUR BACKGROUND.
• SAVE AS MEMBERSHIP DATA ENTRY FORM.
 REPORTS
• REPORTS ARE USED TO “DRESS UP” OR SUMMARIZE
YOUR DATA.
• REPORTS CAN BE MADE FROM A TABLE OR QUERY.
• YOU CAN USE A WIZARD TO CREATE YOUR REPORTS
OR YOU CAN CUSTOMIZE THEM TO FIT YOUR NEEDS.
• YOU CAN SPECIFY SORTING ORDERS AND YOU CAN
ALSO GROUP DATA.
 CREATING A REPORT
• KIM RETURNS FROM HER STAFF MEETING WITH ANOTHER
REQUEST.
• SHE WANTS TO HAVE A LIST OF ORDERNUM,
CUSTOMERNUM, PAID AND INVOICEAMT. FROM THE ORDER
TABLE. SHE’D LIKE THE INFORMATION PRESENTED IN A MORE
READABLE FORMAT FOR SENIOR MANAGERS.
• CREATE USING “REPORT WIZARD”
• LAYOUT: TABULAR AND OTHERS
• GROUP BY: CUSTOMERNUM
• SORT THE INVOICEAMT IN DESCENDING ORDER
• SAVE THE REPORT AS ORDER LIST
 MODIFYING A REPORT
• INSERT CSUB LOGO UPPER-RIGHT CORNER OF THE
REPORT
• THE TITLE IS ORDER LIST REPORT. TITLE REQUIREMENTS;
• THE TITLE MUST BE CENTERED. CHANGE BACKGROUND AND
FONT COLOR. CHANGE FONT SIZE TO 24.
• PUT A SPACE BETWEEN WORDS FOR FOLLOWINGS:
• ORDERNUM
• CUSTOMERNUM
• INVOICEAMT
 MODIFYING A REPORT (CON’T)
• CHANGE ORDER NUM TO ORDER NO
• CHANGE CUSTOMER NUM TO CUSTOMER NO
• CHANGE INVOICEAMT TO INVOICE AMOUNT
• WIDEN OUT THE HEADING NAMES AND FIELD ENTRIES
SO THAT NOTHING IS CUT OFF. SPACE OUT YOUR
DATA FIELDS AS WELL. ALSO, ADJUST VERTICAL
ALIGNMENT OF EACH FIELD. ALL WORDS IN THE
REPORT MUST BE CLEARLY VISIBLE.
 REPORT PRACTICE

• WE WILL MAKE A REPORT THAT COMBINES ELEMENTS OF THE INSTRUCTOR

TABLE WITH ELEMENTS FROM THE COURSE TABLE.
• FROM THE INSTRUCTOR TABLE, YOUR REPORT WILL INCLUDE EMPLOYEE
NUMBER AND INSTRUCTOR LAST NAME. FROM THE COURSE TABLE, YOUR
REPORT WILL INCLUDE CLASS NUMBER, COURSE NAME, AND TIME.
• YOU NEED TO GROUP BY EMPLOYEE NUMBER. CLASS NUMBER SHOULD BE
SORTED IN ASCENDING ORDER. THE REPORT IS CALLED TEACHING
ASSIGNMENTS.
 REPORT PRACTICE (CON’T)
• AFTER YOU CREATE THE BASIC FORM OF THE REPORT USING
THE WIZARD, WE WILL NEXT NEED TO GO INTO THE DESIGN
SCREEN AND CUSTOMIZE THE REPORT. CENTER THE TITLE IN
THE MIDDLE OF THE PAGE.
• CHANGE EMPLOYEE_NUMBER TO EMPLOYEE NO.
• CHANGE INSTRUCTOR_LAST TO INSTRUCTOR.
• CHANGE CLASS_NUMBER TO CLASS.
• CHANGE COURSE_NAME TO COURSE

• WIDEN OUT THE HEADING NAMES AND FIELD ENTRIES SO THAT

NOTHING IS CUT OFF. SPACE OUT YOUR DATA FIELDS. ALL
WORDS IN THE REPORT MUST BE CLEARLY VISIBLE.
PREVIEW YOUR REPORT BEFORE PRINTING IT

Use the Print Preview

option to see if the report
is as you want it to be. If
not, make your changes
and use Print Preview
again.
 CREATE A FORM WITH A
MAIN FORM AND A SUBFORM
• TRY MICROSOFT FORMS II TUTORIAL
• YOU CAN CREATE A FORM WITH A SUBFORM ON TWO TABLES THAT HAVE AN
ESTABLISHED RELATIONSHIP.
• 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.
• BY SELECTING TWO RELATED TABLES IN THE FORM WIZARD, YOU CAN PRODUCE A FORM
WITH A SUBFORM.
• THE FORM WITH SUBFORM IS A GREAT WAY TO DISPLAY DATA FOR TABLES THAT HAVE A
ONE-TO-MANY RELATIONSHIP.
 FORM WIZARD FORM/SUBFORM
DIALOG BOX
Tables or queries used Main form fields Subform fields
for the form and are shown here. are shown here.
subform display here.
 FORM AND SUBFORM DATA

• NOTICE IN THE FOLLOWING FIGURE THAT THE MAIN FORM CONTAINS

INFORMATION ABOUT THE EMPLOYER WHOSE ID IS 10122.
• THE DATA IN THE SUBFORM ARE POSITIONS THAT THIS PARTICULAR EMPLOYER
HAS AVAILABLE.
• ALSO NOTICE THAT YOU HAVE TWO SETS OF NAVIGATION BUTTONS. YOU
CAN NAVIGATE THE DATA FOR EITHER FORM.
• THE OUTER NAVIGATION BUTTONS APPLY TO THE MAIN FORM
• THE INNER NAVIGATION BUTTONS APPLY TO THE SUBFORM
AN EXAMPLE OF A FORM WITH SUBFORM
The main form The subform.
name appears in
the title bar.

The main form.

The subform
navigation bar.

The main form

navigation bar.
 RELATIONSHIP OF MAIN/SUB FORM

• TO CREATE A FORM BASED ON TWO TABLES, A RELATIONSHIP BETWEEN THE

TWO TABLES MUST BE DEFINED FIRST.
• ACCESS FORM WIZARD AUTOMATICALLY DIVIDE INTO MAIN/SUBFORM
FORMAT.
• ONE (CUSTOMER) – TO – MANY (ORDER)
• PRIMARY TABLE: CUSTOMER (MAIN FORM)
• RELATED TABLE: ORDER (SUB FORM)
 CREATING A MAIN/SUB FORM

• BARBARA WANTS YOU TO CREATE A FORM SO THAT SHE CAN VIEW THE DATA
FOR EACH CUSTOMER AND ALL THE ORDERS FOR THE CUSTOMER AT THE SAME
TIME. THE FORM MUST HAVE FOLLOWING INFORMATION: CUSTOMERNUM,
CUSTOMERNAME, OWNERNAME, ORDERNUM, PAID, INVOICEAMT, AND
BILLINGDATE.
• USE THE FORM WIZARD.
• SUBFORM LAYOUT: DATASHEET & STYLE: INTERNATIONAL
• SAVE AS CUSTOMER (MAIN FORM) AND ORDER (SUBFORM)
CREATING AN ADVANCED REPORT
• KIM WOULD LIKE TO HAVE A MORE FRIENDLY AND EASY
TO READ REPORT FOR SENIOR MANAGERS. SHE NEEDS
FOLLOWING FIELDS FROM BOTH CUSTOMER AND
ORDER TABLES:
• CUSTOMERNUM, CUSTOMERNAME, PHONE, FIRST CONTACT,
ORDERNUM AND INVOICEAMT
• VIEW BY: CUSTOMER TABLE, GROUP BY: CUSTOMERNUM, AND
SORT BY: ORDERNUM FIELD IN ASCENDING ORDER, AND
LAYOUT: STEPPED
• MODIFY YOUR REPORT…..SO, LOOK NICE!!

• SAVE AS CUSTOMER ORDERS

 CREATING WEB-ENABLED INFORMATION

• BARBARA FEELS THAT OTHER EMPLOYEES IN THE COMPANY WOULD BENEFIT FROM
GAINING ACCESS TO THE RESTAURANT DATABASE. LEONARD ASKS WHETHER THE
DATABASE CAN BE MADE AVAILABLE TO EMPLOYEES OVER THE COMPANY NETWORK
(I.E., INTRANET). THAT WAY, EMPLOYEES COULD OBTAIN COMPANY INFORMATION
USING THEIR DESKTOP COMPUTERS RATHER THAN USING PAPER FORMS. MOST
EMPLOYEES, SUCH AS THE CUSTOMER REPRESENTATIVES IN THE MARKETING
DEPARTMENT, DO NOT NEED TO ACCESS TO THE ENTIRE DATABASE, NOR SHOULD
THEY BE ABLE TO MAKE CHANGES TO THE DATABASE OBJECTS.
 CREATING A STATIC WEB PAGE

• LEONARD HAS ASKED YOU TO CREATE AN HTML DOCUMENT FOR THE

CUSTOMER TABLE. HE WANTS THIS DATA TO BE AVAILABLE TO CUSTOMER
REPRESENTATIVES WORKING OUTSIDE THE OFFICE. LEONARD WANTS YOU TO
CREATE STATIC WEB PAGES BECAUSE THE CUSTOMER REPRESENTATIVES NEED
TO VIEW THEM ONLY ONCE A MONTH TO COMPLETE THEIR MONTHLY STATUS
REPORTS.
• SELECT THE CUSTOMER TABLE FROM RESTAURANT DATABASE
• FILE NAME: CUSTOMER
• OPEN THE TABLE USING ANY INTERNET BROWSER
 CREATING AN STATIC WEB PAGE

• EASY TO CREATE AND MAINTAIN

• STORED OUTSIDE THE DATABASE

• SIMPLE TO PUBLISH
• ONLY REFLECT THE STATE OF THE CUSTOMER TABLE AT THE TIME IT WAS CREATED.
• DOES NOT ALLOW CONTINUOUS UPDATE BECAUSE IT’S NOT LINKED TO THE CUSTOMER TABLE
ON WHICH IT IS BASED.
 CREATING A DYNAMIC WEB PAGE (DATA
ACCESS PAGE)
• BARBARA ASKS IF IT’S POSSIBLE TO CREATE A DYNAMIC WEB PAGE USING THE CUSTOMER
TABLE THAT HER STAFF MEMBERS CAN UPDATE USING THEIR BROWSERS.
• USE THE PAGES OBJECTS
• SAVE AS CUSTOMER
• A DATA ACCESS PAGE IS A DYNAMIC HTML DOCUMENT.
 CREATING A DYNAMIC WEB PAGE (DATA
ACCESS PAGE)
• WHEN YOU OPEN A DATA ACCESS PAGE, YOU ARE VIEWING CURRENT DATA FROM THE DATA
ACCESS PAGE, THEN YOU CAN USE THE DATA ACCESS PAGE TO VIEW OR UPDATE THE DATA IN THE
DATABASE USING A WEB BROWSER (AT LEAST MS EXPLORER VERSION 5).
• A DATA ACCESS PAGE CAN BE STORED OUTSIDE THE DATABASE AS SEPARATE HTML DOCUMENTS.
• IF A SINGLE TABLE OR QUERY IS THE BASIS OF THE DATA ACCESS PAGE, THEN YOU CAN USE THE
DATA ACCESS PAGE TO UPDATE THE DATA IN THE DATABASE.
• HOWEVER, IF TWO OR MORE RELATED TABLES ARE THE BASIS FOR THE DATA ACCESS PAGE, THEN
YOU CAN ONLY VIEW THE DATA (NO UPDATE).
 IMPORTING AN HTML DOCUMENT AS AN
ACCESS TABLE
• DOWNLOAD AND THEN OPEN NEWREST HTML DOCUMENT.
• CLOSE NEWREST HTML DOCUMENT.
• IMPORT NEWREST HTML DOCUMENT INTO RESTAURANT DATABASE.
• LET THE ACCESS CHOOSE THE PRIMARY KEY.

• SAVE AS POTENTIAL CUSTOMERS

 IMPORTING AND EXPORTING AN XML
DOCUMENT
• DOWNLOAD LOANS XML DOCUMENT.
• UNIVERSAL WAY TO TRANSMIT DATA BECAUSE OF THE INTERNET

• IMPORT LOANS XML DOCUMENT INTO RESTAURANT DATABASE.

• SAVE AS LOANS XML
• EXPORT CUSTOMER TABLE AS AN XML DOCUMENT