Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book-1
EXTENDED LEARNING MODULE J (Office 2010 Version)
IMPLEMENTING A DATABASE WITH MICROSOFT ACCESS
JUMP TO THE SUPPORT YOU WANT
STUDENT LEARNING OUTCOMES
1. Identify the steps necessary to implement the structure of a relational database using
MODULE SUMMARY
This Extended Learning Module teaches your students how to implement a database design in
Microsoft Access, version 2010.
It is assumed that your students have completed Extended Learning Module C and have also
read Chapter 3.
The primary sections of this module include:
2. Defining Relationships within the Solomon Enterprises Database
4. Creating a Simple Query Using One Relation
6. Generating a Simple Report
8. Creating a Data Input Form
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book-2
LECTURE OUTLINE
SOLOMON ENTERPRISES DATABASE (p. 473)
1. Implementing the structure of the Solomon Enterprises Database
3. Implementing the Concrete Type Relation
DEFINING RELATIONSHIPS WITHIN THE SOLOMON ENTERPRISES DATABASE (p. 482)
ENTERING INFORMATION INTO THE SOLOMON DATABASE (p. 484)
1. Simple Query with a Condition (Conditional Query)
CREATING AN ADVANCED QUERY USING MORE THAN ONE RELATION (p. 489)
CREATING A DATA INPUT FORM (p. 499)
END OF MODULE (p. 500)
1. Summary: Student Learning Outcomes Revisited
3. Short-Answer Questions
Back to Jump List
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book-3
MODULES, PROJECTS, AND DATA FILES
• Building Value Chains: Helping Customers Define Value
• Using Relational Technology to Track Projects: Foothills Construction
Back to Jump List
• These are the Student Learning Outcomes for the module.
• Use them as a road map to inform your students of what you will be
• This slide presents the organization for the module.
• This slide presents the organization for the module.
• This slide shows the steps involved in creating a database.
o Step 1: Define the entity classes and primary keys (covered in
(covered in this Module)
• The next 4 slides provide the structure of the Solomon Database from
Extended Learning Module C.
• This module shows your students how to implement the last step in
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book-5
• This slide and the next two show the structure of the Solomon
• This is the second slide showing the structure of the Solomon
• This is the third and last slide showing the structure of the Solomon
• This slide defines a data dictionary.
• This slide and the next three present the steps, followed by illustrative
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book-6
• This slide shows the next step in creating a database.
• This last slide in the set shows the screen for creating the structure of
• This is the first of two slides focusing on creating the Raw Material
• This slide shows the data dictionary screen you would get when
creating the Concrete Type relation (Figure J.6 on page 479).
Mod J Book-7
• This is the first of three slides showing how to create the Bill of
• This slide deals with the primary key of this relation.
• This figure illustrates the creation of a composite primary key (Figure
• This slide is the first of five showing how to define relationships
between the tables.
• This slide illustrates the relationships between all the tables in our
Mod J Book-8
• This slide defines the steps involved in creating the relationships.
• This second slide illustrates the first step in defining relationships
(Figure J.10 on page 483).
• The third slide and last slide in the series is the figure showing how the
relationships between the tables look in Access (Figure J.11 on page
484).
• This slide starts the process of entering information into the database
we created in the previous slides. (Student Learning Outcome #2)
• This slide shows the steps in entering information into the relation
(Figure J.13 on page 486).
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book-9
• This slide introduces the concept of referential integrity.
with the foreign key table (Raw Material relation).
• This slide show what happens in Access if you try to violate referential
• This slide demonstrates how you would begin if you wanted to change
the structure of a relation (Figure J.15 on page 487).
• This slide begins the tutorial on creating queries.
• This figure shows the first step in creating a simple query (the top two
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book–10
• This slide is the continuation of the previous one showing the rest of
Figure J.16 on page 488).
• This figure shows how to restrict the fields that appear based on some
criterion (Figure J.17 on page 489).
• This slide continues the conditional query theme with a slightly more
complex condition.
• This is the first of several slides that show how to create an advanced
query. This first slide in the series presents the first two steps.
respective tables.
• This slide shows the third and fourth step in creating an advanced
query.
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
• This slide shows the first screen in Figure J.18 on page 491.
• This is how the query window should look after you’ve chosen the
• This slide shows the second screen in Figure J.18 on page 491.
• This is what you’ll see after having dragged and dropped all the
• This slide shows the last screen in Figure J.18 on page 491 that shows
the output of our query.
• This slide is the first of seven that show how to generate a simple
report. (Student Learning Outcome #3)
• This second slide shows the 4th, 5th and 6th steps in generating a report
using the wizard.
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book–12
SLIDE 42
• This third slide showing the steps of creating a report has the final two
SLIDE 43
• This forth slide shows the screens for the first two steps of the process
(Figure J.19 on page 492).
• This slide shows the screens for selecting the fields in the report and
SLIDE 45
• This slide shows the choice of to add a sort order and layout (Figure
J.20 on page 493).
• This slide shows the choice of default values and the window you get
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book–13
• This slide shows the how to get to the Design View (in Figure J.21 on
• This slide is the first of eleven that show that steps necessary to
• This slide shows the first three steps in the report creation process.
• You’ll notice that the first three steps are pretty much identical to
• This slide shows the screens we see which take the first four steps of
• Again this slide is pretty much the same as what we saw with the
simple report.
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book–14
• This slide shows the 4th, 5th and 6th steps in the process.
• Access defaults to show grouping on the first field in the table/query.
• This slide shows two grouping windows (Figure J.22 on page 495).
• This slide shows the screen for sorting information (Figure J.22 on
page 495).
• This slide shows the 7th step in the report generation process.
• This slide shows the summary option screen (first screen in Figure J.23
on page 496).
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book–15
• This slide shows the last three steps in the report generation process.
• This slide shows the options for formatting the report.
• The background screen lets you specify the layout and orientation.
• This slide shows the final report as generated by the previous steps
(Figure J.24 on page 497).
• This slide gives general directions for adjusting the report to your
liking.
• This slide shows the screens in Figure J.25 on page 497.
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book–16
• This slide shows what we adjusted and how the new version looks
• Again, this slide shows what we adjusted for the grouping header and
how the new version looks (Figure J.27 on page 498).
• One more time, this slide shows what we adjusted for the detail
• And again, this slide shows what we adjusted for the detail section
• The last 3 slides show how to create an input form.
• You create an input form so that people who don’t have a knowledge
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book–17
• This slide shows the initial step in creating an input form (the screen in
Figure J.30 on page 500).
• This slide shows the input form we created (Figure J.31 on page 500).
• Here we can see the result of our handiwork.
Back to Jump List
Extended Learning Module J (Office 2010 Version) – Implementing a Database with Microsoft Access
Mod J Book–18
ASSIGNMENTS & EXERCISES (p. 509)
1. Enter new employee information. It’s likely that Solomon Enterprises would need to enter
the information for a new employee. Create a new input form to enter Employee ID,
Employee Last Name, Employee First Name, and Date of Hire. Design the form so that the
information appears in tabular form, and has Sandstone background.
DISCUSSION
2. What are the ingredients for premier marble concrete? Write a query to show how many
units of each raw material are in concrete type 4. Print out the name of the concrete type,
its ID, the name of the raw material (not its ID), and the number of units of each of the raw
materials.
DISCUSSION:
• This is a conditional query that will use the Bill of Material and the Concrete Type
3. Inventory report. Create a report that shows how many units Solomon Enterprises has of
each of the raw materials. Don’t include water (hint: you want all Raw Material fields that
do not equal water). Choose your own layout and page orientation.
DISCUSSION
Mod J Book–19
4. Sort query information. The datasheet view of a query allows you to sort the information
in that query. Try this out with the Order relation. Download the Solomon Enterprises
database from the Web site that supports this text www.mhhe.com/haag. (Select XLM/J.
The name of the file is XLMJ_Solomon_Enterprises.mdb.) Click on the Queries tab and
open the Order relation in datasheet view. Sort the table alphabetically on Employee Last
Name. Place your cursor anywhere in the column you want to sort by (in this case Employee
Last Name), and click on the sort-ascending button. It has an “A” above a “Z” with an arrow
pointing downwards.
DISCUSSION
5. Filter query information. You can request Access to show you any other occurrences of a
data item that is in the same column. Use the same file you needed for question #6 above.
You can download it from the Web site for this text www.mhhe.com/haag. (Select XLM/J.
The name of the file is XLMJ_Solomon_Enterprises.mdb.) Filter the information so that only
those records in which the truck is a Ford. To do this click in the Truck Type column on any
one of the occurrences of Ford. Then, click on the Filter by Selection button (that’s the
button in the button bar with the funnel and a lightning strike). You will instantly see only
the three records where the truck is a Ford. To return the data to its previous state, click on
Remove Filter button (that’s the button that has the funnel without any other symbol). If
you click on this button again, it will reapply the filter.
DISCUSSION