Unformatted Attachment Preview
Access…It’s a Grind Coffee Shop Project Instructions
Check your syllabus for Due Date
This is an MS Access Project, where you will perform a number of functions using MS Access. You
may form a group of no more than TWO, or you can work alone. The Text Appendix C, Designing
Databases, Access Tutorial Plug-Ins 5-8, and exercises prepare you for this project. When you have
completed the assignment below, you will upload your Access file to the Moodle assignment link. If
you work with one other, you only need to upload one MS Access file; make sure that you identify
both names who worked on the Access project.
THE MS ACCESS PROJECT
It’s a Grind Coffee Shop, is an Oakland, California, neighborhood coffee shop. Kate Fitzgerald, the
proprietor, has decided that she needs assistance with her database. Kate wants you to do a number
of things for her in her MS Access database named, ItsAGrindCoffee_Data_StudentVersion.accdb,
that will help her with decisions about purchasing additional books for her coffee shop.
1. Download (and save onto the desktop) ItsAGrindCoffee_Data_StudentVersion.accdb file located in
Moodle.
2. Open the MSAccess database. Open each Table to see what attributes each table contains.
Review the relationships. (Get to know your data.)
A. First, create a Form that will allow you to add all the information about any new books to the
BOOK table. Select a layout you prefer. Find a logo to insert/add to the form. You can use your
favorite search engine to find a picture or image. Position the logo in the top-left corner. Save the
form as “BOOK Form.”
a. Kate is excited about the book that she just bought... The new book is Jules Verne’s
“Twenty Thousand Leagues Under the Sea”. You will use the BOOK Form you just created
to add Kate’s new book to the database – specifically to the Book Table. Open the Book
Form to “ADD a New Record” to the Book table…
b. The Publisher is “Rand McNally and Company”. You will find that this publisher is not in
your Publisher Table, so add this Publisher to the Publisher Table. The Pub ID is an
autonumber, so you don’t have to add that data.
c. Now using your BOOK Form…add the following data for Kate’s new book:
i. Book ID…21 (as this is 21st Book in the Table)
ii. Author ID… the author is Jules Verne, so you must look up Jules’ AuthorID from the
Author table and enter that
iii. Book Title…“Twenty Thousand Leagues Under the Sea”
iv. Notes…Rare find, Very very clean
v. Year…1922
vi. Pub ID…the Publisher is Rand McNally and Company, so you must look up its Pub ID
from the Publisher table and enter that
vii. Binding ID…The Binding is Leather (ID = L)
viii. Cond…The Condition is “Very Good” (Cond = 1)
ix. BPrice…the Book Price is $345.00.
d. You should now have 21 books in your book table…
Page 1 of 2
Access…It’s a Grind Coffee Shop Project Instructions
Check your syllabus for Due Date
B. Now you will create a number of queries for Kate, which will assist her in running her business…
Remember…when creating queries, Kate will want to see the actual condition, i.e. Very Good, not
the identifier code...1; and the name of the publisher, i.e. Vintage Books, not their code 1468.
1. A query that shows the Author, Title, Year, Condition, Book Price and Publisher (in this order),
sorted by author (A-Z), and name query “ALL BOOKS Query”.
2. A query that shows all the information about any books she has that are in Excellent or Very
Good condition; name query “BEST CONDITION BOOKS Query”.
3. A query that shows the Author, Title, Publisher, Book Price, and Condition of all books that are
greater than $40.00 and better than Poor condition. Sort by Book Price (highest to lowest).
Name query “Books greater than $40 better than poor condition Query”. Make sure your Book
Prices are in Currency.
4. A query that shows the title, type of binding and publisher of only those books that are in
“good” condition (good) (in this order; left to right); name your query “only good books
Query”.
5. A query that shows the Author, Title, Publisher, Book Price, and Retail Price of all books. In
this query, create a calculated field, name it “Retail Price”; make the Retail Price 10% higher
than the Book Price. Make sure you name the Field Header (in the Query) “Retail Price” and
the Retail Prices are formatted in Currency. Sort by Retail Price (high to low) and name query
“Retail Prices Query”.
C. Now you will create a number of reports for Kate, which will also assist her in running her
business… Remember…when creating reports, Kate will want to see the actual condition, i.e. Very
Good, not the identifier code...1; or the name of the publisher, i.e. Vintage Books, not their code
1468. Also, please ensure all information on the report is readable.
a. A report that shows all books identifying Author, Title, Retail Price of all books. Sorted by
Author; name report “Books by Author Report”.
b. Using your “All Books” query, create a report that displays author, title, year, condition, and
book price. Group by Publisher, Sort by Author, and obtain details and summary options for
average, minimum, and maximum for the book prices. The format of the report is up to you,
BUT ALL information on the report must be clearly visible…in other words, “Melville, He” is not
acceptable; it must read Melville, Herman. Name report “Publisher with Pricing Report”
c. A report that shows all books identifying Title, Author, Year and Publisher; group by Publisher
and then sort by year; name report “Publishers Report”.
D. One final item...Create one additional report or query that you believe Kate needs to help her with
some of the other decisions for the “It’s a Grind Coffee Shop. Make sure you use at least three (3)
of the Tables.
And when you upload your completed MS Access file in Moodle, explain your reasoning for
the particular report or query in the Comments area, e.g., what information does this
report or query give to help Kate make decisions about her business?
Enjoy and Good Data Manipulation!!
Page 2 of 2