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