Project 1 Instructions

Step 1: Read the background information

Step 2: Skip Assignment 1

Step 3: Based on the background information determine the Access tables you need for your database, and the fields you need in each table; Assignment 2A. In this step will make up your own data (come up with customer names, coupon deals, and sign up customers to deals)

Step 4: Assignment 2B

Final Output: Here is what your final access file should include:

1. Tables Created in Access (Customers, Deals, Sign-ups)
2. Form and subform: Deals (with sign-ups subform)
3. Query 1: Maximum Price
4. Query 2: Percentage Bargain
5. Query 3: Sign-ups equal to or over 30
6. Query 4: Most popular deal
7. Query 5: For report
8. Report: Notification

CASE COLLECTIVE COUPONS TRACKING DATABASE Designing a Relational Database to Create Tables, Forms, Queries, and Reports PREVIEW in this case, you will design a relational database for a company.' that markets and sells coupons to groups on the Internet. After your database design is completed and correct, you will create database tables and populate them with data. You will produce one form with a subform that allows you to book coupon deals. You will also create queries to help the company answer some important questions: Which deals are available for a specied maximum price? How good a bargain is a deal compared with the regular price? Which deals have at leaSt 30 people signed up so the deal can run? What is the most popular deal? Finally, you will create a query and then a report that lists all customers who hare signed up for current deals. PREPARATION I Before attempting this case, you should have some experience in database design and in using Microsoft Access. O lomplcte any part of Tutorial A that your instructor assigns. - Complete any part of Tutorial 1% that your instructor assigns, or refer to the tutorial as necessary. - Refer to Tutorial 1" as necessary. BACKGROUND The internet is a great avenue for marketing and sales, and c-commcree has exploded since the mid-1990s. Recently, new companies have begun to offer discounted coupons to registered members. These coupon deals originate in local businesses, which offer products and services for about half the normal price if the coupon company -an getgor more people to siilil up for a deal. Il'or example, suppose that a local restaurant wants to increase its patronage during the middle of the week, when the dining room usually is not full. The restaurant will offer the coupon company half-price deals on mid-week dinners, such as a Hill\" gift certificate for $50, if the coupon company can not at least gopcuplt} to sign up for the deal. Hy usint': e-Inail and Facehoolt, coupon companies can often find 30 people who want to take advantage of a particular bargain. You have been hired as a summer intern to or We a database tracking system for an lntcruct coupon company 'alied Collective Coupons. The owner, (Iliarlcne Clayton, saw your resume on your university Web site. She thinks that your experience with database design, implementation, and Microsoft Access makes you a perfect fit for the job. Charlene wants you to design the database first and then implement a number offorms, queries, and a report. On your first day at work, you interview ('iharlcne to find out what she wants from the database: YOU: What sort of information do you need to keep about your business? CHARLENE: We have a spreadsheet of all our customers, including their names and cn'nlil addresses. Each record is designated with .1 customer number so we can heth them straight. So far we have over 3,000 customers registered with us, so the business is really taking off well! We don't need additional information about the customers because payment for the deals occurs only after ? people sign up and the deal is accepted. Those payments are handled by another system we ready have in place. But we would like an easy way to record who signs up for each deal. Collective Coupons Tracking Database YOU: A form with a subform would be a good way to do that. What information do you need to keep about the deals you solicit from local businesses? CHARLENE: We are starting to have so many deals that we need to keep them straight. Some deals have the same name because the business might run the same deal later in the year. We keep a description of the deal, the city where the deal is offered and run, the available dates, and the price of the deal versus the regular price so customers can see what a bargain it is. YOU: How do you track who signs up for the deals? CHARLENE: Again, using a spreadsheet, we keep a list of customers who sign up for the deals. But there isn't an easy way to count all the deals, and we need to be able to do that. YOU: You can easily count all the customers who sign up for the deals by using a query. What other information do you want to get out of this database? CHARLENE: I'd like to know what deals are available for a certain price. We like to advertise that price; people e-mail us with that question all the time. Also, we want to be able to calculate the savings that customers are getting with a deal. We would advertise these savings heavily since our customers love good bargains. Of course, we need to know which deals are running, so figuring out which deals have at least sign-ups is essential. We'd also like to know which deals are the most popular so we can ask those businesses for more future deals. YOU: Queries can handle all those questions easily. Do you need any reports? CHARLENE: We would like to see a report of everyone who has signed up for the current deals. ASSIGNMENT 1: CREATING THE DATABASE DESIGN In this assignment, you will design your database tables using a word processing program. Pay close attention to the logic and structure of the tables. Do not start developing your Access database in Assignment 2 before getting feedback from your instructor on Assignment 1. Keep in mind that you will need to examine the requirements in Assignment 2 to design your fields and tables properly. It is good programming practice to look at the required outputs before beginning your design. When designing the database, observe the following guidelines: . First, determine the tables you will need by listing the name of each table and the fields it should contain. Avoid data redundancy. Do not create a field if it can be created by a "calculated field" in a query. You will need to ret table. Alvord duplicating data. bettment los using the table feats PIOUSSOFF or tables should the form in Figure 3-4. You not market into kov field(e) by entering an e the field name. Print the database design if your instructor requires it. Table Name Field Name Type (text, numeric, currency, etc.) . .. FIGURE 3-1 Table design NOTE Have your design approved before beginning Assignment 2; otherwise, you may need to redo Assignment 2. ASSIGNMENT 2: CREATING THE DATABASE, QUERIES, AND REPORT In this assignment, you will first create database tables in Access and populate them with data. Next, you will create a form, queries, and a report.Assignment 2A: Creating Tables in Access In this part of the assignment, you will create your tables in Access. Use the following guidelines: Create at least five deals offered by Collective Coupons. Create more than 30 customers. Make sure that at least 30 customers sign up for some of the deals. Appropriately limit the size of the text fields; for example, a customer number does not need the default length of 255 characters. Print all tables if your instructor requires it. Assignment 2B: Creating Forms, Queries, and a Report You will generate one form with a subform, five queries, and one report, as outlined in the Background section of this case. Form Create a form and subform based on your Deals table and. Sign-ups table (or whatever you named these tables). Save the main form as Deals. Your form should resemble that in Figure 3-2. Deals Deals Deal Number 101 Description Fall Foliage Helicopter Tour Location Chicago, IL Deal Price $189.00 Original Price $450.00 Available Date 10/5/2012 Ending Date 11/6/2012 Sign Ups subform Sign Up Number Customer ID Deal Number 2 C2000 101 3 C2001 101 4 C2002 101 5 C2003 101 6 C2004 101 7 C2005 101 8 C2006 101 9 C2007 101 10 C2008 101 11 C2009 101 12 C2010 101 13 C2011 101 14 C2012 101 15 C2013 101 16 C2014 101 17 C2015 101 18 C2016 101 19 C2017 101 20 C2018 101 Record: 14 1 51 of 117 , H No Filter Search Record: 14 4 1 of 5 . H . No Filter Search FIGURE 3-2 Deals form and Sign Ups subformQuery 1 Create a query called Maximum Price. This query should prompt the user for a maximum price and then display fields for the Description, Location, Deal Price, Available Date, and Ending Date of all deals under the specified price. For example, if you enter $50 at the prompt, your output should resemble that in Figure 3-3, although your data will be different. Maximum Price Description Location Deal Price - Available Date . Ending Date . 18 Hole Golf Heritage Golf Center Chicago, IL $28.00 9/2/2012 9/15/2012 Hamburgers, Etc Restaurant Chicago, IL $12.00 9/20/2012 9/22/2012 Segway City Tour - 3 hours Chicago, IL $35.00 9/18/2012 9/19/2012 Spas.Unlimited Chicago, IL $25.00 10/1/2012 10/10/2012 FIGURE 3-3 Maximum Price query Query 2 Create a query called Percentage Bargain. List all the available deals, including their Deal Number, Description, Location, Deal Price, and Original Price, and then calculate a percentage in the Bargain column. The bargain is the percentage difference between the Deal Price and Original Price. Your output should look like that in Figure 3-4, although your data will be different. Percentage Bargain Deal Numbe - Description Location - Deal Price . Original Pric . Bargain 101 Fall Foliage Helicopter Tour Chicago, IL $189.00 $450.00 58.00% 102 18 Hole Golf Heritage Golf Center Chicago, IL $28.00 $64.00 56.25% 103 Hamburgers, Etc Restaurant Chicago, IL $12.00 $25.00 52.00% 104 Segway City Tour - 3 hours Chicago, IL $35.00 $70.00 50.00% 105 Spas Unlimited Chicago, IL $25.00 $50.00 50.00% FIGURE 3-4 Percentage Bargain query Query 3 30 Create a query called Sign-ups Equal to or Over In this query, you need to determine which deals have at least 2@members signed up. Display columns only for the Deal Number and Description in your output. Your output should resemble the format shown in Figure 3-5, but the data will be different. Sign-ups Equal to or Over 100 Deal Numbe . Description 101 Fall Foliage Helicopter Tour 103 Hamburgers, Etc Restaurant 104 Segway City Tour - 3 hours 105 Spas Unlimited FIGURE 3-5 Sign-ups Equal to or Over 100 query Query 4 Create a query called Most Popular Deal. List a description of the deal and how many people have signed up for it. Sort the output to list the most popular deals first. Note the column heading change from the default setting provided by the query generator. Your output should resemble the format shown in Figure 3-6, but the data will be different.Case 3 Most Popular Deal Description . Number Signed Up . Spas Unlimited 129 Fall Foliage Helicopter Tour 117 Hamburgers, Etc Restaurant 114 Segway City Tour - 3 hours 100 18 Hole Golf Heritage Golf Center 68 FIGURE 3-6 Most Popular Deal query Report 30 Create a report named Notification Report that is based on tables and the Sign-ups Equal to or Over J query you already created. Use the query to output the description of each deal and the e-mail addresses and names of customers signed up for the deal. List only the deals that have 30 or more people signed up. Save the query as For Report. Bring the query into the report generator and group records based on the Description field. Depending on your data, your output should resemble that shown in Figure 3-7; only a portion of the report is shown for space purposes. Notification Report Wednesday, November 02, 2011 9:53:09 AM Description Email Name Fall Foliage Helicopter Tour TUMSJTTR1@gmail.com J Salo ONSCKMAP2@verizon.com S Jones TLZMGOOT2@verizon.com J Legace EIEWFHW3@comcast.com P Hasek EEXRNLHM2@verizon.com E Kiprusoff IYZAUBON2@verizon.com W Labarbera BIRRRWLW3@comcast.com O O'Neal KQLSJFKI1@gmail.com A Jagr ZGYRRXIC2@verizon.com L Pierzynski XDCMRUYU3@comcast.com Z Fernandez PJTVJWXZ2@verizon.com U Alfredsson BPBDNXWP2@verizon.com A Joseph HOXWVOFD1@gmail.com U Mccabe XCAAFLSU3@comcast.com P Wright TOHDCKAY1@gmail.com T Visnovsky CHUTSNZB3@comcast.com E Eaton BFLYXFLT1@gmail.com M Gagne QSXVEVVW1@gmail.com W Luongo HOWXSAQT3@comcast.com R Lundqvist JYGAQQLH2@verizon.com N Bosh MEEHGGLY?@comcast com R Duncan FIGURE 3-7 Notification report