Confused Please Help

Application Assignment 25 Instructions

Download the ValleProducts.accdb database. Save the file as ValleeProducts_LastNameFirstName.accdb

Create a new table named Orders using the table design below. You may create your table in design view or datasheet view. Save the Orders table as Orders Your Name #3.

Add the three records listed below to the Orders table.

Define a one-to-many relationship between the Customer table and Orders table. Right click in the window and select Show table. Select the referential integrity option and both cascade options for the relationships. Save the relationship.

Create a query based on Customer table that shows CustomerNum, CustomerName, Street, City, and State for all customers in Grand Rapids, South Bend, and Toledo. This will be a List-of-Values query.

Save as #5 Cities YourName. Query results will show 8 records.

Create a query based on the Coffee Table that displays all four fields. Show only those records that do not have the following Coffee Types: African, Blended, and Flavored.

Save as #6 Coffee Types YourName. The query results will show 13 records.

Create a query based on the Product Table that shows all Decaf products that have a WeightCode field value of A, or whose Price field value is less than 30. Display all fields from the Product table. Open the Product table and look in design view if you need information about how Decaf is listed there. Remember to indicate Decaf in both rows. Hint: Look in design view of the Product table to see how Decaf is displayed. You must be sure to use the OR operator for A or < 30 which means those two criteria must be on two different rows.

Save the query as #7 Decaf YourName. Your results should show 35 records.

Create a new query based on the Product table. Use aggregate functions to find the lowest, average and highest values in the Price field. Name the fields: Lowest Price, Average Price and Highest Price, respectively.

Save the query as #8 Prices YourName. Your results will show one row of results: $7.99 $30.36 $234.00

Create a new query based on the Product table. Use aggregate functions to find the lowest, average and highest values in the Price field by WeightCode. Name the fields: Lowest Price, Average Price and Highest Price, respectively.

Save the query as #9 Price by Weight YourName. You will have 7 rows of output in this query.

Create a parameter query based on the Customer table that prompts for customers in a specific state. Do not just make a select query. A parameter is different. Display the CustomerNum, CustomerName, City, and State fields.

Save the query as #10 States YourName. Run the query using a parameter value of OH. You will see only 4 Oh records.

Create a query based on the Order table and display all fields. Show the record that has a billing Date equal to 2/15/2012. Save the query as #11 Date Your Name.

Create a query based on the Order table and display all fields. Only show records that have not paid. Add a calculated field that will display a late fee of 2.8% based on the Invoice Amount field. Use the Standard format and 2 Decimal places. Rename the new field Late Fee.

Save the query as #12 Late Fee YourName. One of the Late Fees will be $55.61. There will be one other late fee.

Create a query based on the Coffee table (include all fields) that shows all records where the Coffee Name ends with Blend. Use a Pattern Match query.

Save the query as #13 Blend YourName. Your results will display 6 records.

Create a form in Design View using the Coffee and Product tables.

a) Place the all fields from the Coffee table under the Detail section. Move them so that they approximate the picture of the form below.

b) Add an appropriate title to the form and add your name to the end of the title.

c) Place a subform under the Coffee fields on the detail section based on the Product table. Include all fields from the Product table.

d) Click on the subform label (highlighted in yellow below) and type your name after Product. Do not rename the entire subform, just the label.

e) Switch to Layout View and resize the columns in your subform to best fit.

f) Save the form as #14 Form YourName.

offeeCode

ecaf

MAR

lavored

NUT

Flavored true

false

Flavored true

Central & South American true

Central & South American false

true

Flavored true

EOR

Flavored true

Central & South American false

Special Import true

Flavored true

Special Import false

Flavored true

African true

African false

Blended true

Central & South American false

Flavored true

Blended true

Blended false

Central & South American true

Flavored true

Blended true

Flavored true

Special Import false

African true

African false

Blended true

Special Import false

								C	CoffeeName	CoffeeType											D
																		A	Amaretto			F	true
																			B	Banana Nut
			BRUM	Butter Rum
	BRZM	Brazilian Margopipe								Central & South American					false
			CARC	Caramel Creme
			CCHR	Chocolate Cherry
	C												E	Celebes Toraja	Special Import
C					IN	Cinnamon Stick
			COCO	Coconut Creme
			COLA	Colombian Aged Crop
			COLF	Colombian French Roast
							COLS	Colombian Supremo
	COSC	Costa Rica Cartago
			DUTB	Dutch Breakfast							Blended
			ETHH	Ethiopian Harrar					African
							FVAN	French Vanilla
								G	Georgia Peach
			GERC	German Chocolate
	GUAC	Guatemala Coban
			HAWK	Hawaiian Kona
							HAZL	Hazelnut Creme
					IRSH	Irish Creme
	JAMB	Jamaica Blue Mountain
			JRUM	Jamaican Rum
			KENA	Kenya AA
	KENP	Kenya Peaberry
			KONB	Kona Blend
	MEXC	Mexican Cafe Pluma
																MI	Chocolate Mint
			MNUT	Macadamia Nut
			MOCH	Mocha Java Blend
	NEYB	New York Blend
			PAND	Peak of Andes
			RASP	Black Raspberry
			SAFB	San Francisco Blend
					SALM	Swiss Chocolate Almond
			SCHR	Sweet Cherry
			SPEC	Southern Pecan
	SUMM	Sumatra Mountain
			TANK	Tanzania Kibo Chagga
	TANP	Tanzania Peaberry
											VALD	Valle Dinner Blend
											VALG	Valle Gourmet Blend
	YEMM	Yemen Mocha

MI 49464

5/11/91

5/25/92

Country Inn

Alto MI

Grand Rapids MI

-4431

620

Brewing Company

South Bend IN

635

Grand Rapids MI

Toledo OH

Romulus MI 48174

Grand Rapids MI

CustomerNum	CustomerName	Street	City	State	ZipCode	OwnerName	Phone	FirstContact
104	Meadows Restaurant	Pond Hill Road	Monroe	48161	Mr. Ray Suchecki	(313) 792-3546	2/28/91
107	Cottage Grill	82 Mix Avenue	Bootjack	49945	Ms. Doris Reaume	(616) 643-8821	4/3/91
122	Roadhouse Restaurant	8408 E. Fletcher Road	Clare	48617	Ms. Shirley Woodruff	(517) 966-8651	4/12/91
123	Bridge Inn	400 Salmon Street	Ada	4	9301	Mr. Wayne Bouwman	(616) 888-9827	4/17/91
128	Grand River Restaurant	37 Queue Highway	Lacota	49063	Mr. John Rohrs	(313) 729-5364	4/20/91
129	Sandy Lookout Restaurant	95 North Bay Boulevard	Jenison	49428	Ms. Michele Yasenak	(616) 111-9148	4/27/91
131	Bunker Hill Grill	15365 Old Bedford Trail	Eagle Point	49031	Mr. Ronald Kooienga	(906) 895-2041	5/1/90
133	Florentine Restaurante	2874 Western Avenue	Drenthe			49464	Mr. Donald Bench	(616) 111-3260	5/3/91
135	Topview Restaurant	2840 McClain Road	Zeeland	Ms. Janice Stapleton	(616) 643-4	635		5/11/91
136	Cleo’s Downtown Restaurant	4090 Division Street NW	Borculo	Ms. Joan Hoffman	(616) 888-2046
163	Bentham’s Riverfront Restaurant	1366 36th Street	Roscommon	48653	Mr. Joe Markovicz	(517) 792-8040	5/18/91
165	Sullivan’s Restaurant & Lounge	1935 Snow Street SE	Saugatuck	49453	Ms. Dawn Parker	(616) 575-6731	5/19/91
201	Wagon Train Restaurant	5480 Alpine Lane	Selkirk	48661	Mr. Carl Seaver	(517) 111-5545			5/25/92
202	Extra Helpings Restaurant	2140 Edgewood Road	Five Lakes	48446	Ms. Deborah Wolfe	(517) 889-6003
203	Mountain Lake Restaurant	701 Bagley Street				Grand Rapids	49571	Mr. Donald MacPherson	(616) 532-4499
322		Alto	114 Lexington Parkway	49302	Mr. James Cowan	(616) 888-7111	6/2/93
325	Best Bet Restaurant	56 Four Mile Road	49505	Ms. Rebecca Van Singel	(616) 415-7294	6/12/93
407	Jean’s Country Restaurant	44 Tower Lane	Mattawan	49071	Ms. Jean Brooks	(517)	620	9/18/93
423	Bay Pointe Restaurant	11456 Marsh Road	Shelbyville	4	9344	Mr. Janosfi Petofi	(616) 679-5681	10/24/93
515	Cheshire Restaurant	8200 Baldwin Boulevard	Burlington	49029	Mr. Jeffrey Hersha	(517) 717-9855	12/24/93
597	Around the Clock Restaurant	3333 Bradford Farms	Copper Harbor	49918	Ms. Jennifer Lewis	(906) 273-	9465	6/24/94
Brandywine Restaurant	150 Hall Road	Kearsarge	49942	Mr. Walter Reed	(906) 124-1824	7/3/94
624			South Bend	431 North Phillips Road	46611	Mr. Toby Stein	(219) 332-4847	7/17/94
625	Maxwell’s Restaurant	230 South Phillips Road	46623	Ms. Barbara Feldon	(219) 333-0000	7/19/94
627	Monarch Restaurant	3915 Hawthorne Avenue		Toledo				OH	43603	Mr. Gilbert Scholten	(419) 332-2681	7/29/94
Oaks Restaurant	3300 West Russell Street	Maumee	43537	Ms. Julie Pfeiffer	(419) 336-9000	9/13/94
646	Golden Gate Restaurant	2890 Canyonside Way		Romulus		48174	Ms. Nancy Mills	(313) 888-7778	11/12/94
650	The Peppermill	109 East Monroe Avenue	Elkhart	46515	Ms. Tara Jerentowski	(219) 334-3980	10/10/94
652	Apple Blossom Inn	915 South Creek Drive	49587	Ms. Pam Leonard	(616) 755-1736	12/8/94
687	The Brittany Restaurant	6 Bayberry Pointe Drive	Topaz	49925	Mr. Ken Hodge	(517) 331-7388	1/5/95
726	Four Star Steakhouse	1925 Bridge Street	Halfway Corner	48441	Mr. Gregory Olson	(906) 434-4192	1/3/95
741	Prime Cut Steakhouse	2819 East 10 Street	Mishawaka	46544	Ms. Gretchen Fletcher	(219) 336-0900	1/18/95
742	Cherry Creek Inn	4005 West Madison Road	43617	Mr. Douglas Viereck	(419) 336-2333	2/22/95
750	Grain Bin Inn	5013 North Cliff Avenue	LaPorte	46351	Ms. Bridgit Feeney	(219) 339-1264	5/19/95
779	Gateway Lounge	3408 Gateway Boulevard	Sylvania	43560	Ms. Susan Mouw	(419) 361-1137	3/5/96
783	First Edition Restaurant	3509 Garfield Avenue	Mr. Shannon Petree	(313) 461-8899	5/12/96
788	The Empire	10 Sycamore Street	49509	Ms. Curtis Haiar	(616) 762-9144	9/16/96
798	Embers Restaurant	4004 West 41st Street	Goshen	46526	Mr. Clifford Merritt	(219) 816-2456	3/2/97

CoffeeCode

Decaf

COLA A

MOCH A ¤ 7.99

A ¤ 8.99

COCO A ¤ 8.99

BRUM A ¤ 8.99

A ¤ 8.99

JRUM A ¤ 8.99

CCHR A ¤ 8.99

MNUT A

KENP A ¤ 8.99

RASP A ¤ 8.99

COLS A ¤ 7.99

TANP A ¤ 8.99

COSC A ¤ 7.99

VALD A ¤ 7.99

DUTB A ¤ 7.99

SALM A ¤ 8.99

GUAC A ¤ 7.99

ETHH A ¤ 8.99

CELT A

BRZM A ¤ 7.99

COLF A ¤ 7.99

GERC A ¤ 8.99

KONB A ¤ 7.99

HAZL A ¤ 8.99

IRSH A ¤ 8.99

TANK A ¤ 8.99

JAMB A

A ¤ 8.99

VALG A ¤ 7.99

KENA A ¤ 8.99

HAWK A

CARC A ¤ 8.99

NEYB A ¤ 7.99

MEXC A ¤ 7.99

SCHR A ¤ 8.99

YEMM A

SPEC A ¤ 8.99

PAND A

SUMM A

A ¤ 8.99

FVAN A ¤ 8.99

SAFB A ¤ 7.99

COLA B

MOCH B

CINN B

AMAR B ¤ 42.00

COCO B ¤ 42.00

BRUM B ¤ 42.00

GEOR B ¤ 42.00

JRUM B ¤ 42.00

CCHR B ¤ 42.00

MNUT B

KENP B ¤ 42.00

RASP B ¤ 42.00

COLS B ¤ 36.00

TANP B ¤ 42.00

COSC B ¤ 38.00

VALD B

DUTB B ¤ 36.00

SALM B ¤ 42.00

GUAC B ¤ 38.00

ETHH B ¤ 42.00

CELT B

BRZM B ¤ 38.00

COLF B ¤ 38.00

GERC B ¤ 42.00

KONB B ¤ 36.00

HAZL B ¤ 42.00

IRSH B ¤ 42.00

TANK B ¤ 42.00

JAMB B

BNUT B

VALG B ¤ 34.00

KENA B ¤ 42.00

HAWK B

CARC B ¤ 42.00

NEYB B ¤ 36.00

MEXC B ¤ 38.00

SCHR B ¤ 42.00

YEMM B

SPEC B ¤ 42.00

PAND B

SUMM B

MINT B ¤ 42.00

FVAN B ¤ 42.00

SAFB B ¤ 36.00

AMAR C

SALM C ¤ 21.00

HAZL C ¤ 21.00

IRSH C ¤ 21.00

FVAN C ¤ 21.00

VALD D

VALG D ¤ 33.50

AMAR E ¤ 38.00

COLS E ¤ 36.00

VALD E

SALM E ¤ 38.00

HAZL E ¤ 38.00

IRSH E ¤ 38.00

VALG E ¤ 35.00

FVAN E ¤ 38.00

VALD F

VALG F ¤ 50.00

COLS G

VALD G

VALG G ¤ 58.50

9301 COLA A ¤ 8.99 D

MOCH A ¤ 8.99 D

CINN A ¤ 9.99 D

AMAR A ¤ 9.99 D

COCO A ¤ 9.99 D

BRUM A ¤ 9.99 D

GEOR A ¤ 9.99 D

JRUM A ¤ 9.99 D

CCHR A ¤ 9.99 D

MNUT A ¤ 10.99 D

RASP A ¤ 9.99 D

COLS A ¤ 8.99 D

VALD A ¤ 8.99 D

DUTB A ¤ 8.99 D

SALM A ¤ 9.99 D

9344 ETHH A ¤ 9.99 D

COLF A ¤ 8.99 D

GERC A ¤ 9.99 D

KONB A ¤ 8.99 D

HAZL A ¤ 9.99 D

IRSH A ¤ 9.99 D

TANK A ¤ 9.99 D

BNUT A ¤ 9.99 D

VALG A ¤ 8.99 D

KENA A ¤ 9.99 D

HAWK A

CARC A ¤ 9.99 D

SCHR A ¤ 9.99 D

SPEC A ¤ 9.99 D

PAND A

MINT A ¤ 9.99 D

FVAN A ¤ 9.99 D

SAFB A ¤ 8.99 D

COLA B

MOCH B ¤ 41.00 D

CINN B

AMAR B ¤ 46.50 D

COCO B ¤ 46.50 D

BRUM B ¤ 46.50 D

GEOR B ¤ 46.50 D

JRUM B ¤ 46.50 D

CCHR B ¤ 46.50 D

MNUT B

RASP B ¤ 46.50 D

COLS B ¤ 41.00 D

VALD B

DUTB B ¤ 41.00 D

SALM B ¤ 46.50 D

ETHH B ¤ 46.50 D

COLF B ¤ 44.00 D

GERC B ¤ 46.50 D

KONB B ¤ 41.00 D

HAZL B ¤ 46.50 D

IRSH B ¤ 46.50 D

TANK B ¤ 46.50 D

BNUT B

VALG B ¤ 39.50 D

9465 KENA B ¤ 46.50 D

HAWK B

CARC B ¤ 46.50 D

SCHR B ¤ 46.50 D

SPEC B ¤ 46.50 D

PAND B ¤ 45.00 D

MINT B ¤ 46.50 D

FVAN B ¤ 46.50 D

SAFB B ¤ 41.00 D

HAZL C

FVAN C ¤ 22.00 D

VALD D

VALG D ¤ 35.50 D

COLS E

VALD E

HAZL E

VALG E ¤ 36.50 D

FVAN E ¤ 40.00 D

VALD F

VALG F ¤ 53.00 D

COLS G

VALD G ¤ 61.00 D

VALG G ¤ 61.00 D

ProductCode		WeightCode	Price
2301									¤ 7.99
2303
2304				CINN								¤ 8.99
2306						AMAR
2309
2310
2313		GEOR
2316
2318
2320											¤ 9.99
2322
2324
2329
2330
2331
2334
2336
2338
2342
2344
2345	¤ 11.99
2346
2349
2350
2352
2354
2357
2359
2360	¤ 42.99
2362			BNUT
2363
2365
2367	¤ 16.99
2372
2374
2376
2377
2380	¤ 12.99
2381
2386	¤ 8.49
2388		¤ 10.99
2395				MINT
2396
2398
2401								¤ 38.00
2403							¤ 36.00
2404											¤ 42.00
2406
2409
2410
2413
2416
2418
2420		¤ 45.00
2422
2424
2429
2430
2431
2434		¤ 34.00
2436
2438
2442
2444
2445			¤ 61.00
2446
2449
2450
2452
2454
2457
2459
2460	¤ 234.00
2462	¤ 44.50
2463
2465
2467	¤ 86.00
2472
2474
2476
2477
2480	¤ 66.00
2481
2486						¤ 41.00
2488	¤ 55.00
2495
2496
2498
2506					¤ 21.00
2538
2554
2557
2596
2634		¤ 33.50
2663
2706
2729
2734		¤ 35.00
2738
2754
2757
2763
2796
2834		¤ 50.00
2863
2929	¤ 60.00
2934		¤ 58.50
2963
9303
9304
9306
9309
9310
9313
9316
9318
9320
9324
9329
9334
9336
9338
9349
9350
9352
9354
9357
9359
9362
9363
9365
9367	¤ 17.99
9372
9377
9381
9386	¤ 9.49
9395
9396
9398
9401		¤ 44.00
9403
9404										¤ 46.50
9406
9409
9410
9413
9416
9418
9420	¤ 49.50
9424
9429
9434		¤ 39.50
9436
9438
9444
9449
9450
9452
9454
9457
9459
9462	¤ 48.00
9463
9467	¤ 91.00
9472
9477
9481
9486
9495
9496
9498
9554		¤ 22.00
9596
9634		¤ 35.50
9663
9729	¤ 37.50
9734		¤ 36.50
9754		¤ 40.00
9763
9796
9834		¤ 53.00
9863
9929	¤ 63.00
9934
9963

WeightCode

Weight/Size
1 lb pkg
6 lb case
24 ct 1.5 oz pkg
44 ct 1.25 oz pkg
44 ct 1.5 oz pkg
88 ct 1.25 oz pkg
88 ct 1.5 oz pkg

BUSI 201

Application Assignment 25 Instructions

1. Download the ValleProducts.accdb database. Save the file as ValleeProducts_LastNameFirstName.accdb

2. Create a new table named Orders using the table design below. You may create your table in design view or datasheet view. Save the Orders table as Orders Your Name #3.

3. Add the three records listed below to the Orders table.

4. Define a one-to-many relationship between the Customer table and Orders table. Right click in the window and select Show table. Select the referential integrity option and both cascade options for the relationships. Save the relationship.

5. Create a query based on Customer table that shows CustomerNum, CustomerName, Street, City, and State for all customers in Grand Rapids, South Bend, and Toledo. This will be a List-of-Values query.

Save as #5 Cities YourName. Query results will show 8 records.

6. Create a query based on the Coffee Table that displays all four fields. Show only those records that do not have the following Coffee Types: African, Blended, and Flavored.

Save as #6 Coffee Types YourName. The query results will show 13 records.

7. Create a query based on the Product Table that shows all Decaf products that have a WeightCode field value of A, or whose Price field value is less than 30. Display all fields from the Product table. Open the Product table and look in design view if you need information about how Decaf is listed there. Remember to indicate Decaf in both rows. Hint: Look in design view of the Product table to see how Decaf is displayed. You must be sure to use the OR operator for A or < 30 which means those two criteria must be on two different rows.

Save the query as #7 Decaf YourName. Your results should show 35 records.

8. Create a new query based on the Product table. Use aggregate functions to find the lowest, average and highest values in the Price field. Name the fields: Lowest Price, Average Price and Highest Price, respectively.

Save the query as #8 Prices YourName. Your results will show one row of results: $7.99 $30.36 $234.00

9. Create a new query based on the Product table. Use aggregate functions to find the lowest, average and highest values in the Price field by WeightCode. Name the fields: Lowest Price, Average Price and Highest Price, respectively.

Save the query as #9 Price by Weight YourName. You will have 7 rows of output in this query.

10. Create a parameter query based on the Customer table that prompts for customers in a specific state. Do not just make a select query. A parameter is different. Display the CustomerNum, CustomerName, City, and State fields.

Save the query as #10 States YourName. Run the query using a parameter value of OH. You will see only 4 Oh records.

11. Create a query based on the Order table and display all fields. Show the record that has a billing Date equal to 2/15/2012. Save the query as #11 Date Your Name.

12. Create a query based on the Order table and display all fields. Only show records that have not paid. Add a calculated field that will display a late fee of 2.8% based on the Invoice Amount field. Use the Standard format and 2 Decimal places. Rename the new field Late Fee.

Save the query as #12 Late Fee YourName. One of the Late Fees will be $55.61. There will be one other late fee.

13. Create a query based on the Coffee table (include all fields) that shows all records where the Coffee Name ends with Blend. Use a Pattern Match query.

Save the query as #13 Blend YourName. Your results will display 6 records.

14. Create a form in Design View using the Coffee and Product tables.

a) Place the all fields from the Coffee table under the Detail section. Move them so that they approximate the picture of the form below.

b) Add an appropriate title to the form and add your name to the end of the title.

c) Place a subform under the Coffee fields on the detail section based on the Product table. Include all fields from the Product table.

d) Click on the subform label (highlighted in yellow below) and type your name after Product. Do not rename the entire subform, just the label.

e) Switch to Layout View and resize the columns in your subform to best fit.

f) Save the form as #14 Form YourName.

Page 1 of 2

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Confused Please Help ”

Get high-quality paper

Guarantee! All work is written by expert writers!

Still stressed from student homework?

Get quality assistance from academic writers!

Order now