Transcript access 2007 project

Due Nov 25,
2014
ACCESS 2007 Assignment
FALL 2014
Use these Table and
Field names.
Must have a NUMERIC data type.
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. (Group Leader’s name only)
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 seven 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. See next slide for information not in the training video.
10. The following slides contain Tables, and examples of Queries, a Form, and a Report.
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 NUMBER. 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.
ACCESS 2007 Assignment
Use these records and field names for the Coins Table
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
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
CREATE TWO MORE QUERIES FOR A TOTAL OF SEVEN.
Two more Queries: Make your own queries and be sure that you give them
good names. The five queries in this assignment are examples of queries
with good names.
THUS, YOU WILL HAVE A TOTAL OF SEVEN QUERIES FOR THIS
PROJECT.
ACCESS 2007 Assignment
An example of the a New Orders Form
NOTE: THIS ORDER FORM MUST WORK IN
ORDER FOR THE PROJECT TO BE GRADED.
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
FALL 2014
Summary of Requirements:
1. Play the Ready2Read video at least twice before constructing your database. While
you are constructing the database, watch the videos at the same time.
2. Create a blank database and save it in a file called: YourLastName Coin
Shop.accdb.
3. 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.
4. Populate the database with “some” data so that you can check to see if it is working.
5. Construct the Customers Form and enter more data. Use the customers given in the
examples, and then add more. Hide unnecessary fields.
6. 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. THE
ORDERS FORM MUST WORK IN ORDER TO BE GRADED. (-60 POINTS)
7. Construct and test the five queries. Use the query names given in the examples.
8. Construct two reports of your choosing. They must be professional looking.
9. NOTE: PROCEDURES FOR SUBMITTING THE PROJECT AND THE GRADING
CRITERIA WILL BE ADDED VERY SOON.
ACCESS 2007 PROJECT
COIN SHOP (FALL 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
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 (5 points each)
35 = 7 queries X 5 points each.
POINTS
(0 OR 1 point)
(0 OR 14 points)
(0 OR 5 points)
(0 OR 5 points)
(0 TO 35 points)
__________
__________
__________
__________
__________
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 (-60 points)
DO NOT COPY THE PROJECT TO A CD OR DVD-R USING THE FLASH DRIVE FORMAT.
NOT SAVED IN ACCESS 2007 FORMAT (-60 points)
ORDERS FORM DOES NOT WORK (-60 points)
The ORDERS FORM must work in order for the program to be graded.
50% off for each class day late.
Comments:
TOTAL POINTS: 60 pts. possible
__________
ACCESS 2007 PROJECT
ProgramCOIN
#2 SHOP
Fall 2011
Form-B
(Fall 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_________________
LAST NAME:_____________________
FIRST NAME_________________
LAST NAME:_____________________
FIRST NAME_________________
DATE DUE:_________________
DATE SUBMITTED:__________________
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.
PLACE A CHECK NEXT TO THE FOLLOWING:
1.
____ Saved the Program on a CD or DVD-R using the following
directory and
sub-directory: Your Name\Access Project
2.
____ 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.
4.
____ Placed the CD or DVD-R in the envelope.
5.
____ Cut out this form along dotted edges and TAPE it to the
envelope.
DO NOT EMAIL THIS PROJECT. (-50 points)
MUST BE HANDED IN DURING CLASS TIME ONLY. (-20 points)
14