ACCESS Assignment
Access questions.. please solve the "accdb" file using "docx" instruction. Thank you.
ACCESS Assignment
Due on April 30, 2014 (at 5:00 PM)
Victoria owns an Antique shop in Never Never Land Mall. In addition to selling antiques, Victoria provides restoration classes offered to customers and Mall shoppers. Victoria has already developed a database with a number of entities and now she is trying to query the database to answer some questions and extract the information she needs.
Open the database NeverNeverLand.accdb from the Access Assignment Folder on Canvas. Familiarize yourself with the database and answer the following questions:
a. (1 pt) Victoria needs to identify those students who have signed up for classes with high unpaid fees. Create a query that helps her identify students with unpaid fees greater than $ 40. Save the query as qryUnpaid_GT_40.
b. (1) Victoria wants to identify dealers who carry military memorabilia. Create this query. Name it as qryNoMeomorabilia.
c. (1 pts) Victoria expects the cost of classes to increase by 20 percent for next year’s enrollment. Create a query which reflects the 20 percent increase in cost. Name it qryEnrollment+20%
d. ( 1 pts) Create a relationship diagram among the different tables save it as Relationship.
e. (1 pt) Create a list that includes the ID numbers, last names, first names, and phone numbers for customers in Cleveland and Knoxville. Save it as qryCleveland_and_Knoxville.
f. (1 pt) Create a list that includes all green booths which are rented for more than $250. Save it as qryGrrenBooth>250.
g. (1) Create a list of all the February classes sorted by date and by time. Be sure to include information the reader is interested in such as name of the class, the date and time, and the class fee. Save it as qryFebClasses.
h. (1) When Victoria reviews the list for February classes, she thinks that the dates would be more meaningful if they spelled out the day of the week, using a format such as “Saturday, February 5, 2013.” Victoria asks you to modify the class list to display this format. Save this as qryFeb_Long_Date
i. (2 pts) Victoria has requested an easy way to browse customer data along with the classes in which customers have enrolled. She wants to also use this form to quickly mark the record when the customer has paid for a particular class. Create the requested form for Victoria. As you build the form, consider the design, arrangement of fields, and the general attractiveness of the form. Select the layout and style that provides the best “look” for this application. Save this as frmEnroll.
Instructor ID First Name Last Name Address City State ZIP Phone Cell Fee
1 Gary Crenshaw 1267 Holston Ave Greeneville TN 37899 (423) 989-1234 (423) 365-8788 $50.00
2 Candace Carpenter 453 Grandview Ave Chattanooga TN 37889 (423) 898-8978 (423) 354-0590 $35.00
3 Gretchen Cox 345 Cutshaw Rd Cleveland TN 37364 (423) 396-1477 (423) 365-1644 $65.00
4 Rebecca Thomas 3998 Willow Creek Cleveland TN 37364 (423) 434-0326 (423) 366-5435 $25.00
Dealer ID First Name Last Name Address City State ZIP Phone Cell Tax ID Comments
1111 Richard Michaels 208 Sonja Avenue Ringgold GA 30738 (706) 890-8788 (423) 900-8788 78-9667890 Cannot be reached by phone during the day. Leave a message and he'll return the call.
1242 Marcia Tyler 9800 Harbor Lane Cleveland TN 37364 (423) 890-8788 (423) 645-8900 34-5690654 Call cell phone first. Up to 15% discount is approved.
1575 Gary Williams 13849 Warren Park Rd Cleveland TN 37364 (423) 889-8888 (423) 238-0909 83-8492834 Call cell phone first.
1576 Kristy Granger 1233 Garrison Park Rd Cleveland TN 37364 (423) 309-7878 (423) 344-8897 10-2938945 Call home phone during the day and cell phone at night. No discounts without permission.
1865 Sherry Carney 2110 First Ave Cleveland TN 37364 (423) 890-8877 (423) 556-8900 34-5212378 Call home phone first.
2323 Gretchen Greenley 8755 Weeping Willow Lane Greeneville TN 78988 (423) 326-8988 (423) 590-8888 12-3890987
2344 Franklin Grindstaff 3589 Sharpbend Road Ooltewah TN 37363 (423) 396-8890 (423) 423-7899 10-2030405 Call home phone first, then cell phone. 10-20% discount is approved.
2390 Steven Barnitz 890 Fifth Ave Cleveland TN 37364 (423) 590-8900 (423) 432-7866 78-9875678 Use cell phone number all the time. No discounts.
2455 Faye Emerett 4172 Graysville Rd Cleveland TN 37311 (423) 587-8766 (423) 544-8988 80-1834638 Discounts approved up to 15%.
3340 Shannon Franklin 114 Fifth St. Alcoa TN 37701 (865) 982-7441 (865) 877-7676 76-5398764 No discounts.
3355 Sharon Dilbert 14839 Sherry Lane Cleveland TN 37364 (423) 398-8766 (423) 899-8799 78-9876567 Use home phone for first contact. No discounts. Has additional Dr Pepper memorabilia and stained glass not on display. Books in booth are all $5 each.
3434 Joyce Hodges 301 Brickly Lane Cleveland TN 37311 (423) 396-8988 (423) 586-8977 09-3738029 Call cell phone first. Leave a message. Up to 15% discount is approved. Bookshelf in booth is not for sale.
3488 Mike Fisher 2174 Homewood Circle Ooltewah TN 37363 (423) 396-8988 (423) 432-2877 82-9403292 Call cell phone first. Discount up to 20% is approved.
4243 Judy Lynn Cox 11984 McKinley Rd Knoxville TN 78987 (863) 691-8988 (865) 777-8788 87-8794302 No discounts.
4322 Bill Bailey 2282 Shiney Penney Lane Ooltewah TN 37363 (423) 390-8766 (423) 399-7898 78-3982943
4444 Whitman Scott 7543 Grandview Ooltewah TN 37363 (423) 396-8899 (423) 433-8900 78-9204856 Has large stock of military memorabilia.
4545 Sharon Crenshaw 7536 Phillips Lane Cleveland TN 37364 (423) 677-7655 (423) 465-7888 18-3948274 Call home phone first. No discounts.
4555 Curt Duval 7878 Cartison Lane Collegedale TN 37363 (423) 397-8799 (423) 455-8799 82-0394810 Discounts up to 20% approved on furniture items only.
4577 Karen Kline 7150 Oakwood Dr Cleveland TN 37364 (423) 675-8790 (423) 677-8777 78-3940184 Has large stock of Dr Pepper and Pepsi memorabilia.
5050 Gretchen Grandolier 223 Eighth Ave Chattanooga TN 37817 (423) 678-8777 (423) 345-7899 83-9402934 Only reachable by phone on weekends. Up to 15% discounts approved on all items in the booth.
5437 Kelly Kirtchen 7890 Grover Lane Ringgold GA 30738 (706) 890-8787 (423) 234-8790 82-9304988
5466 Sue Stevens 558 Groverland Pass Ooltewah TN 37363 (423) 899-8787 (423) 788-8777 83-9402983 Cell phone number is husband's (Rick). Rick can authorize discounts.
5533 Taylor Thomas 10057 Churchill Place Cleveland TN 37364 (423) 892-0384 (423) 230-9098 29-3490293 Call cell phone first. No discounts without permission.
5555 Philip Steinfel 1289 Stevens Mill Road Cleveland TN 37889 (423) 398-7789 (423) 238-9877 83-7482938
5766 Felicia Eichendahl 5432 Garnier Road Ringgold GA 30738 (706) 789-7898 (423) 877-8987 10-2934857 Call cell phone first. No discounts without permission.
6754 Michael Leroux 432 Cherry Lane Cleveland TN 38478 (423) 398-7878 (423) 234-8789 02-6548755
6767 Christine Turner 11485 Winding River Road Cleveland TN 37364 (423) 897-8789 (423) 235-8987 89-7438283 Up to 20% discount is approved. Military memorabilia can be discounted 30%. Do not break up glassware sets.
7676 Gary Thomas 16340 5th Street Chattanooga TN 37421 (423) 898-7898 (423) 238-9897 82-8394872 Can only be reached by phone at night or on weekends. Up 15% discount approved.
7878 Laurence Victor 3002 Shadowlands Dr Cleveland TN 37364 (423) 398-7898 (423) 234-8799 87-9827348
7879 Carla Thomas 11321 Whispering Pines Rd Chattanooga TN 37421 (423) 309-7898 (423) 348-7889 83-8943827
8154 Michael Richards 11874 Winchester St Ooltewah TN 37363 (423) 396-1813 (423) 655-8900 78-9089768 No discounts.
9090 David Donaldson 10583 Victory Lane Cleveland TN 37364 (423) 309-8877 (423) 353-3898 82-9347982
9855 Kathy Reid 401 First Avenue Ooltewah TN 37363 (423) 878-7988 (423) 238-9877 02-9348757 Call cell phone first. Up to 20% discount except items marked ND.
Customer ID First Name Last Name Address City State ZIP Phone Comments Newsletter?
1 Antoine Stephaine 123 Eighth Ave W Cleveland TN 37364 (423) 396-8789 Yes
2 Angela Sutherland 7890 Grandview Dr Cleveland TN 37364 (423) 396-1214 No
3 Patricia Sutherland 7890 Grandview Dr Cleveland TN 37364 (423) 396-1214 Yes
4 Brandie Phillips 8907 Westview Road Cleveland TN 37364 (615) 472-4545 No
5 Chloe Chastaine 7856 Chestnut Dr Cleveland TN 37364 (615) 477-8989 Yes
6 David Chastaine 7856 Chestnut Dr Cleveland TN 37364 (615) 477-8989 Yes
7 Desiree Taylor 7865 Oak Street Cleveland TN 37364 (423) 559-5547 Yes
8 Dianne Taylor 7865 Oak Street Cleveland TN 37364 (423) 559-5547 No
9 Gerald Taylor 7865 Oak Street Cleveland TN 37364 (423) 559-5547 Yes
10 Jeff Watry 675 E Walnut Ln Athens TN 37371 (423) 745-5547 No
11 Jeff Greenley 6543 Fescue Dr Cleveland TN 37364 (615) 478-1889 Yes
12 David Searles 8315 Grant St. Chattanooga TN 37421 (423) 541-3588 Yes
13 Daniel Hagensack 832 W Fourth Ave Cleveland TN 37364 (615) 478-1889 Yes
14 Michael Crockett # 5 Greenwood Ct Cleveland TN 37364 (615) 472-1233 Yes
15 Holly George 6544 Homewood Circle Ooltewah TN 37363 (423) 514-3212 Yes
16 Phillip D'Angelo 654 Rolling Green Dr Collegedale TN 37363 (423) 892-4156 Yes
17 Timothy D'Angelo 654 Rolling Green Dr Collegedale TN 37363 (423) 892-4156 Yes
18 Walter Richards 6754 Homewood Circle Ooltewah TN 37363 (615) 499-6541 Yes
19 William Flaherty 8900 Woodland Dr Knoxville TN 37929 (615) 539-1212 Yes
20 Florence Wisecarver 6755 Turner Lane Sweetwater TN 37874 (615) 477-8989 No
21 Floyd Greeley 6654 Ranger Lane Ringgold GA 30738 (706) 631-2222 No
22 Judith Holefill 6788 Forestview Dr Knoxville TN 37929 (615) 498-6478 Yes
23 Jim Ossewalde 7865 Shadowpark Rd Collegedale TN 37363 (423) 888-3545 No
24 Jimmy Cox 958 Icey Lane Ooltewah TN 37363 (423) 647-2477 Yes
25 Kristi Culver 7865 Misty Valley Rd Ooltewah TN 37363 (423) 436-6398 Yes
26 Ray Stevens 6754 Sherry Lane Collegedale TN 37363 (423) 396-1674 Yes
27 Roseanne Adams 8990 Granite Road Collegedale TN 37363 (423) 238-3474 No
28 Dan Adams 8990 Granite Road Collegedale TN 37363 (423) 238-3474 Yes
29 Scott Franklin 78 Friendly Lane Knoxville TN 37929 (615) 675-4444 No
30 Shannon Leverence 1189 Grindstone Rd Ringgold GA 30738 (706) 312-6211 No
31 Mark Ikendahl 9008 Willow St Ringgold GA 30738 (706) 312-6211 Yes
32 Ande Ikendahl 9008 Willow St Ringgold GA 30738 (706) 312-6211 Yes
33 Suzie Soffeil #5 Madison Way Athens TN 37371 (615) 988-6547 Yes
34 Jeremy Soffeil #5 Madison Way Athens TN 37371 (615) 988-6547 Yes
35 Sandy Jackson 3456 Homewood Circle Ooltewah TN 37363 (615) 490-3244 Yes
36 Sherry Choi 788 Courtly Lane Knoxville TN 37929 (615) 966-3244 Yes
37 Cheri Cranshaw 927 Grindstaff Rd Athens TN 37371 (615) 745-5211 No
38 Scott Kent 254 Hyacinth Way Cleveland TN 37364 (423) 695-4884 Yes
Class ID Class Name Cost Classroom Date Time Instructor ID Continuing Education?
1 Tattered Treasures $20.00 1 15/01/2013 08:30:00 3 Yes
2 Applying Stains $30.00 1 18/01/2013 19:00:00 1 No
3 Tattered Treasures $20.00 2 15/01/2013 19:00:00 3 Yes
4 Furniture Repair $35.00 1 05/02/2013 18:00:00 1 No
5 Furniture Repair $35.00 1 25/01/2013 08:30:00 1 No
6 Depression Glass $20.00 2 15/02/2013 19:00:00 4 No
7 Learning to be an Auctioneer $75.00 1 28/02/2013 18:00:00 1 Yes
8 Learning to be an Auctioneer $75.00 1 28/02/2013 08:30:00 1 Yes
9 True Antiques $35.00 1 15/02/2013 08:30:00 1 No
10 Tattered Treasures $20.00 2 05/02/2013 18:00:00 3 Yes
11 Depression Glass $20.00 2 06/02/2013 08:30:00 4 No
Customer ID Class ID Fees Paid?
2 5 No
3 5 No
4 7 Yes
5 3 Yes
5 8 No
5 10 No
7 7 Yes
8 8 No
9 4 Yes
9 5 Yes
9 8 Yes
10 4 Yes
10 5 Yes
10 8 Yes
10 9 Yes
10 10 Yes
12 10 Yes
15 2 No
15 4 Yes
15 8 Yes
15 10 Yes
16 4 Yes
18 4 Yes
18 5 No
18 10 Yes
21 5 No
22 10 Yes
25 4 Yes
25 5 Yes
25 7 Yes
25 8 No
25 10 Yes
29 4 No
31 2 No
31 4 No
33 2 No
33 4 Yes
37 8 Yes
37 10 Yes
38 10 Yes
Employee ID First Name Last Name SSN Date of Birth Start Date End Date Address City State Zip Job ID Memo Phone Cell Salary Hourly Rate Review
1 Linda Sutherland 703-87-8987 15/08/1968 15/02/2007 1102 Jackson Street Cleveland TN 37311 1 (423) 396-1777 (423) 644-1222 $67,000.00
3 Mary Sanchez 925-78-7655 15/10/1968 15/06/2010 633 Empire Street Chattanooga TN 37415 5 (423) 398-9898 (423) 665-1211 $35,500.00 31/05/2008
4 William Sullivan 952-16-4286 14/02/1975 23/05/2009 12933 Road G Cleveland TN 37311 4 (423) 233-6321 (423) 651-2342 $9.25 01/03/2008
8 Frances Miller 701-53-2642 15/10/1968 22/03/2009 755 Cherry Street Cleveland TN 37312 3 (423) 344-7212 (423) 985-3410 $12.50 01/04/2008
9 Gretchen Archibald 308-12-5070 22/12/1969 05/01/2005 16/05/2009 906 East Second Street Athens TN 37303 4 (423) 435-2648 (423) 751-2042 $9.25
11 Scott Cox 728-65-4988 22/04/1969 19/04/2008 22/04/2010 1309 Mesa Avenue Ooltewah TN 37315 3 (423) 435-3147 (423) 952-0010 $15.50
12 Cheri Greene 928-55-6322 12/04/1980 05/08/2010 403 North Madison Street Collegedale TN 37311 2 (423) 396-6412 (423) 825-1643 $35,500.00 15/01/2009
13 Angela Scott 905-16-4257 04/05/1981 22/09/2010 133 South Washington Ooltewah TN 37315 3 (423) 435-2616 (423) 844-1200 $15.00 01/02/2008
14 Francois Feliciano 722-65-3716 15/03/1980 16/07/2009 307 Memorial Drive Cleveland TN 37311 3 (423) 233-1697 (423) 165-2974 $16.00 01/05/2008
16 Robert Toriano 495-00-1678 10/04/1974 16/09/2009 9411 Clarkston Avenue Chattanooga TN 37401 6 (423) 344-4289 (423) 592-1352 $15.50 01/06/2008
17 Adam Jones 711-15-4632 21/06/1977 12/10/2010 623 South Hampton Way Knoxville TN 37901 3 (865) 465-2122 (423) 241-5230 $15.00 01/06/2008
18 Luke Hoffman 900-51-6420 15/09/1968 16/11/2009 22/02/2012 1322 East Fairmont Street Cleveland TN 37312 3 (423) 398-1647 (423) 789-9552 $15.50
19 Frances Gomez 658-66-0000 30/05/1981 31/05/2011 912 North Hampton Avenue Athens TN 37303 6 Speaks fluent Spanish (423) 398-5454 (423) 502-1200 $12.00 01/11/2008
20 Hae Yeon Choi 653-00-1876 12/12/1979 15/12/2013 322 South Cripple Creek Avenue Chattanooga TN 37402 6 (423) 233-9745 (423) 583-2420 $13.50 01/03/2009
22 Dawn Young 723-32-0100 20/06/1977 16/01/2009 56 East Pioneer Road Cleveland TN 37323 3 (423) 344-8458 (423) 264-3213 $15.50 15/12/2008
Booth ID Size Location Type Color Rafters? Carpet? Dealer ID Rent
A-01 12 x 18 Outside Perimeter Green Yes Yes 1242 $310.00
A-02 12 x 18 Outside Perimeter Tan Yes Yes 9855 $310.00
A-03 12 x 12 Outside Perimeter Green No No 7878 $290.00
A-04 12 x 12 Outside Perimeter Yellow Yes Yes 4243 $290.00
A-05 12 x 12 Outside Perimeter White Yes Yes 1575 $290.00
A-06 12 x 18 Outside Perimeter Yellow No Yes $310.00
A-07 12 x 12 Outside Perimeter White Yes Yes $290.00
A-08 12 x 10 Outside Perimeter White Yes No 4555 $230.00
A-09 12 x 10 Outside Perimeter Yellow No No 1576 $230.00
A-10 12 x 10 Outside Perimeter Green Yes Yes 4322 $230.00
A-11 12 x 18 Outside Perimeter Green Yes Yes 9855 $310.00
A-12 12 x 18 Outside Perimeter Green Yes No 2390 $310.00
A-13 12 x 10 Outside Perimeter Yellow Yes Yes 5533 $230.00
A-14 12 x 10 Outside Perimeter Yellow Yes Yes 5533 $230.00
A-15 12 x 8 Outside Perimeter Green Yes Yes 3434 $175.00
A-16 12 x 8 Outside Perimeter Green Yes Yes 4243 $175.00
A-17 12 x 8 Outside Perimeter White No No $175.00
A-18 12 x 12 Outside Perimeter White Yes Yes 5050 $290.00
A-19 12 x 18 Outside Perimeter Green Yes Yes $310.00
A-20 12 x 18 Outside Perimeter Green Yes Yes $310.00
B-01 8 x 10 Inside Perimeter Green Yes Yes 2344 $120.00
B-02 8 x 10 Inside Perimeter White Yes Yes 3355 $120.00
B-03 8 x 10 Inside Perimeter Green Yes Yes 4555 $120.00
B-04 8 x 8 Inside Perimeter Green Yes No 4322 $105.00
B-05 8 x 8 Inside Perimeter Yellow Yes Yes 5437 $105.00
B-06 8 x 8 Inside Perimeter White Yes Yes 2323 $105.00
B-07 8 x 8 Inside Perimeter Yellow Yes Yes $105.00
B-08 8 x 10 Inside Perimeter White Yes No 1111 $120.00
B-09 8 x 10 Inside Perimeter Yellow Yes No 2390 $120.00
B-10 8 x 10 Inside Perimeter Yellow Yes Yes 3340 $120.00
B-11 8 x 12 Inside Perimeter White Yes Yes 8154 $175.00
B-12 8 x 12 Inside Perimeter Yellow Yes Yes 6767 $175.00
B-13 8 x 12 Inside Perimeter White Yes Yes 7878 $175.00
B-14 8 x 10 Inside Perimeter Yellow Yes No $120.00
B-15 8 x 10 Inside Perimeter Green Yes No 5555 $120.00
C-01 8 x 8 Aisle Yellow Yes No 7878 $90.00
C-02 8 x 8 Aisle Yellow Yes Yes 9090 $90.00
C-03 8 x 8 Aisle Yellow Yes Yes 6767 $90.00
C-04 8 x 8 Aisle Green No No 1865 $90.00
C-05 8 x 8 Aisle White Yes Yes 6754 $90.00
C-06 8 x 8 Aisle Green Yes No 7878 $90.00
C-07 8 x 8 Aisle White Yes Yes 7676 $90.00
C-08 8 x 8 Aisle White Yes Yes 6767 $90.00
C-09 8 x 8 Aisle Green Yes Yes 6754 $90.00
C-10 8 x 8 Aisle White Yes Yes 4444 $90.00
C-11 8 x 8 Aisle Green Yes No 5437 $90.00
C-12 8 x 16 Aisle White Yes No 3488 $150.00
C-13 8 x 16 Aisle White Yes No 5050 $150.00
C-14 8 x 20 Aisle Green Yes Yes 7676 $200.00
C-15 8 x 20 Aisle White No No $200.00
Job ID Title
1 Manager
2 Assistant Manager
3 Sales
4 Maintenance
5 Accounting
6 Administrative Assistant