ACCESS 2007 ASSIGNMENT SPRING 2014

1. Purpose: To give students a basic understanding of ACCESS 2007 by constructing a database using the Ready2Read bookstore tutorial as a model for a database involving a Coin Store. It will also give students an opportunity to construct an entity relationship diagram. See the class Web site for a link to the Ready2Read Access 2007 tutorial. Students will work in groups of two or three. You will select the members yourself. Points: 50 Due date: Thursday, April 17, 2014 (Amount of time to do assignment : ~5 weeks.) ACCESS 2007 ASSIGNMENTSPRING 2014

2. ACCESS 2007 Assignment Use these Table and Field names. • Using the ACCESS 2007 Ready2Read example as a model for your database assignment you are to do the following: • Set up a database for a gold, silver, and platinum coin business. Use any name you want for the coin business. • Use your last name combined with Coin Shop.accdb for the name of the database file; for example, Smith Coin Shop.accdb. • You will have three Tables (1) Customers, (2) Orders, and (3) Coins (See above). • You must set up two Forms: • One Form for entering new Customer’s data into the Customers Table, and another Form for recording orders into the Orders Table. • Use a Combo Box on the Orders Form: • for entering the Customer ID into Orders table, and • for entering the Coin ID into Orders table. • Use a Combo Box on the Customers Forms: • for entering the state into the Customers table. Assume you only do business in CA, WA, OR, IL, OH and AZ.

3. ACCESS 2007 Assignment • 5. You must create any two Reports of your choosing. • 6. You must set up five queries as described below: • How many times has each coin been ordered? • Who are the customers from OH who ordered silver coins? • Who are the customers ordering American Gold Eagles? • Who are the customers who have ordered platinum or silver? • What is the total dollar amount for each coin ordered? • 7. Be sure to validate all fields in the Customers and Coins tables; that is, these fields must have entries of the appropriate data type and must contain a data. If there is an error, an appropriate message must be displayed. • 8. You must populate the Orders Table sufficiently so that the above five queries have appropriate data to show that the queries are working. For example, (1) several customers must have ordered American Gold Eagles, (2) several customers must be from OH and have ordered silver coins, and (3) several customers must have ordered both platinum or silver. • Follow the tutorial videos closely while constructing this database. If you do this, things should go well. • The following slides contain Tables, and examples of Queries, a Form, and a Report.

4. ACCESS 2007 Assignment Use these records and field names for the Coins Table 8 9 Use these records and field names for the Customers Table. You will add more records later. You do not need email addresses.

5. ACCESS 2007 Assignment Use these field names for the Orders Table

6. ACCESS 2007 Assignment Below are samples of how each required query may look. Your queries may look different because of the data you enter into the database.

7. ACCESS 2007 Assignment Below are samples of how each required query may look. Your queries may look different because of the data you enter into the database.

8. ACCESS 2007 Assignment An example of the a New Orders Form This form is not very appealing but it follows the Ready2Read tutorial format.

9. ACCESS 2007 Assignment An example of a Report This Report is not very appealing but it follows the Ready2Read tutorial format.

10. Summary of Requirements: • Play the Ready2Read video at least once or twice before constructing your database. While you are constructing the database, watch the videos at the same time. • Construct an entity relationship diagram for the Coin Shop business. Use PowerPoint or some other tool. • Create a blank database and save it in a file called: YourLastName Coin Shop.accdb. • Construct three tables: (1) Customers table, (2) Orders table, and (3) Coins table. • Use the Table names and Field headings in the examples given within these slides. • Be VERY sure to use appropriate data validation and error messages. • Populate the database with “some” data so that you can check to see if it is working. • Construct the Customers Form and enter more data. Use the customers given in the examples, and then add more. Hide unnecessary fields. • Construct the Orders Form and enter data. Make enough orders so that the queries produce results with at least 3 or more “hits”. Hide unnecessary fields. • Construct and test the five queries. Use the query names given in the examples. • Construct two reports of your choosing. They must be professional looking. • NOTE: PROCEDURES FOR SUBMITTING THE PROJECT AND THE GRADING CRITERIA WILL BE ADDED VERY SOON. ACCESS 2007 ASSIGNMENTSPRING 2014

11. IMPORTANT IMPORTANT: In a training video, a step is missing: In the Setting Up Tables and Fields lesson, the Customer ID and Book ID data types MUST BE SET TO NUMERIC. These TWO fields are in the ORDERS TABLE. These two fields are called FOREIGN KEYS. If you do not set these data types to numeric, you will get an error when trying to set up relationships.

12. ACCESS 2007 PROJECT COIN SHOP (Spring 2014) Form-A Form-A (Print) Last _________________________________ First_______________________ (Print) Last _________________________________ First_______________________ (Print) Last _________________________________ First_______________________ FOLD HERE------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- MIS 101 section: Circle one: 3:00 PM or 4:30 PM GRADING STANDARDS POINTS POINTS Used last name with “Coin Shop” for the file name (0 OR 1 point) __________ Customer Form and Order Form work correctly with Combo Boxes: (0 OR 14 points) __________ Data Validation and appropriate error messages for all fields work correctly: (0 OR 5 points) __________ The two reports look professional and have correct values: (0 OR 5 points) __________ Correctly working queries (4 points each) (0 OR 20 points) __________ Professional looking and correctly drawn entity-relationship diagram: (0 OR 5 points) __________ (The entity-relationship diagram will have three tables and two relationships.) Failure to populate orders table so that queries will display meaningful results. (-20 points) FAILURE TO SUBMIT PROJECT ACCORDING TO GUIDELINES ON THE NEXT SLIDE: (-20 points) DATABASE NOT ON THE CD or DVD-R or WILL NOT RUN (-50 points) NOT SAVED IN ACCESS 2007 FORMAT (-50 points) 50% off for each class day late. TOTAL POINTS: 50 pts. possible __________ Comments:

13. ACCESS 2007 PROJECT COIN SHOP (Spring 2014) Form-B Form-B CUT OUT THIS FORM ALONG THE DOTTED EDGES AND TAPE IT TO THE ENVELOPE. STUDENT FILLS IN THESE BLANKS: LAST NAME:_____________________ FIRST NAME_________________ LAST NAME:_____________________ FIRST NAME_________________ DATE DUE:_________________ DATE SUBMITTED:__________________ PLACE A CHECK NEXT TO THE FOLLOWING: • ____ Saved the Program on a CD or DVD-R using the following directory and sub-directory: Your Name\Access Project • ____ Printed your full name/s on the CD or DVD-R using a permanent black marker. 3. ____ Submitted the CD or DVD-R in required envelope style not much larger than the CD or DVD-R. • ____ Placed the CD or DVD-R in the envelope. 5. ____ Cut out this form along dotted edges and TAPE it to the envelope. YOU MUST USE THIS STYLE THAT IS ABOUT THE SIZE OF YOUR CD or DVD-R. Do not lick to seal. Use metal clip or self-sealing envelope. DO NOT EMAIL THIS PROJECT. (-50 points) MUST BE HANDED IN DURING CLASS TIME ONLY. (-20 points)