Transcript access 2007 assignment spring 2011

ACCESS 2007 ASSIGNMENT
SPRING 2014
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 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:
1. Set up a database for a gold, silver, and platinum coin business. Use any name you want for the
coin business.
2. Use your last name combined with Coin Shop.accdb for the name of the database file; for example,
Smith Coin Shop.accdb.
3. You will have three Tables (1) Customers, (2) Orders, and (3) Coins (See above).
4. You must set up two Forms:
1.
One Form for entering new Customer’s data into the Customers Table, and another Form for
recording orders into the Orders Table.
2.
Use a Combo Box on the Orders Form:
1.
for entering the Customer ID into Orders table, and
2.
for entering the Coin ID into Orders table.
3.
Use a Combo Box on the Customers Forms:
1. for entering the state into the Customers table. Assume you only do business in CA, WA,
OR, IL, OH and AZ.
ACCESS 2007 Assignment
5. You must create any two Reports of your choosing.
6. You must set up five queries as described below:
1.
How many times has each coin been ordered?
2.
Who are the customers from OH who ordered silver coins?
3.
Who are the customers ordering American Gold Eagles?
4. Who are the customers who have ordered platinum or silver?
5.
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.
9. Follow the tutorial videos closely while constructing this database. If you do this, things should go
well.
10. The following slides contain Tables, and examples of Queries, a Form, and a Report.
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.
ACCESS 2007 Assignment
Use these field names for the Orders Table
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.
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.
ACCESS 2007 Assignment
An example of the a New Orders Form
This form is not very appealing but it follows the Ready2Read tutorial format.
ACCESS 2007 Assignment
An example of a Report
This Report is not very appealing but it follows the Ready2Read tutorial format.
ACCESS 2007 ASSIGNMENT
SPRING 2014
Summary of Requirements:
1. 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.
2. Construct an entity relationship diagram for the Coin Shop business. Use
PowerPoint or some other tool.
3. Create a blank database and save it in a file called: YourLastName Coin
Shop.accdb.
4. Construct three tables: (1) Customers table, (2) Orders table, and (3) Coins table.
1. Use the Table names and Field headings in the examples given within these
slides.
2. Be VERY sure to use appropriate data validation and error messages.
5. Populate the database with “some” data so that you can check to see if it is working.
6. Construct the Customers Form and enter more data. Use the customers given in the
examples, and then add more. Hide unnecessary fields.
7. 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.
8. Construct and test the five queries. Use the query names given in the examples.
9. Construct two reports of your choosing. They must be professional looking.
10. NOTE: PROCEDURES FOR SUBMITTING THE PROJECT AND THE GRADING
CRITERIA WILL BE ADDED VERY SOON.
ACCESS 2007 PROJECT
COIN SHOP
(Spring 2014)
Form-A
(Print) Last _________________________________
First_______________________
(Print) Last _________________________________
First_______________________
Form-A
(Print) Last _________________________________
First_______________________
FOLD HERE-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MIS 101 section:
Circle one:
3:00 PM or 4:30 PM
GRADING STANDARDS
POINTS
POINTS
(0 OR 1 point)
(0 OR 14 points)
(0 OR 5 points)
(0 OR 5 points)
(0 OR 20 points)
Used last name with “Coin Shop” for the file name
Customer Form and Order Form work correctly with Combo Boxes:
Data Validation and appropriate error messages for all fields work correctly:
The two reports look professional and have correct values:
Correctly working queries (4 points each)
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:
__________
ACCESS 2007 PROJECT
ProgramCOIN
#2 SHOP
Fall 2011
Form-B
(Spring 2014)
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:
1.
2.
3.
4.
5.
____ 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.
____ 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.
____ 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)
12