ACCESS ASSIGNMENT 1761

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