MIST 201 – MS-Access Practice Exercises
Problem Overview:
You have been asked to create an application to support a bookstore. As part
of that application, you will create a Microsoft Access database that will
support keeping track of books. Currently, information on books is kept in a
web page that has been stored as an .html file. Information on Authors is kept
in an excel file. Information on publishers has been stored in a text file. As a
proof of concept, you will create some sample forms and queries that will
demonstrate how this application might function as a decision support system
for the company. Create a database using MS Access named
yourUserName.accdb (e.g., ma1003456.accdb).
Note: Make sure you have downloaded and unzipped the file “Data
Source File.zip” before you start working on the exercises.
Exercise 1: Create Tables
A. Create a table called Publishers having the following structure. Set the PubID as the primary
key.
Field Name Data Type
PubID Text
PubName Text
URL Hyperlink
B. Import the data into this table from the file Publishers.txt.
C. Create a second table by importing from the Authors sheet in the Bookstore excel file. Set
AuthorID as the primary key and name the table Author.
D. Create a third table by importing from the .html file called Book.html. ISBN is the key field. After
importing, modify the properties of the Books table fields as follows:
NOTE: Ignore any data conversion errors in the step! Just say “OK.”
Field Name Data Type Properties
PubDate Date/Time Input Mask: Short Date
Price Currency Format: Euro
AuthorCode Field Size: Double
Edition Number
Exercise 2: Create Relationships
Create (or edit) the relationship between the foreign keys and the associated
primary keys as follows. Ensure that a book cannot be entered if its publisher
is not already in the Publishers table.
A. Create a relationship between the PubID field in the Publishers table
and the PubID field in the Books table.
B. Create a relationship between the AuthorID field in the Authors table
and the AuthorCode field in the Books table.
Exercise 3: Create Forms
Because an author must exist in the database before a posting can be created
for them, you will need a form for entering new authors. Create a form called
New Author Data Entry Form that contains the following fields from the
Author table (and nothing else): LastName and FirstName. Use the datasheet
layout.
When entering new books, it will make sense to do that from a screen where
the Publisher is selected and then the Book information is entered. Therefore,
a second form, Book Entry Form, should be created. It should contain all the
fields from the Publishers table plus a subform containing all the fields from
the Books table.
Exercise 4: Create Queries
The company would like to use the data in these tables for the purpose of
querying the database in order to get answers to specific questions. Construct
the following queries and view the results.
Prentice Hall Books: List all of the books that are published by Prentice
Hall. Display only the title field of the Books table, and the LastName field
of the Authors table. Do not show the publisher. Sort the query in
ascending order according to the title field.
Second Edition Books: List all of the books whose Edition field contains a
2. Display only the title field of the Books table, the LastName field of the
Authors table, and the PubName field of the Publishers table. Sort the
query in ascending order according to the PubName field.
Books before 2010: List all of the books where the copyright is before
2010. Display only the Copyright year, title, and PubDate fields of the
Books table, the LastName field of the Authors table, and the PubName
field of the Publishers table. Sort the query in descending order according
to the PubDate field.
Exercise 5: Create Reports
All of our reps need access to information on the books that are currently
available. Create a report based on the data in the tables. Include the book’s
title, author’s last name, publisher, ISBN, and price. View the data by
publisher, group it by last name, and sort it by title. Use the stepped layout
and landscape orientation. Name the report Book List. Adjust the layout as
necessary so that all fields and headings are visible.
If you have any question, don’t hesitate to come and talk to me about
the difficulties you find in completing these exercises.
