management information system and app

a04h1Coffee.accdb

Home >Business & Finance homework help >Management homework help >management information system and app

CustomerID	CustomerName	Contact	EmailAddress	Address1	Address2	City	State	ZipCode	Phone	Fax	ServiceStartDate	CreditRating	SalesRepID
1	McAfee, Rand, & Karahalis	Paula Fields		5000 Jefferson Lane	Suite 2000	Flatgap	KY	41219	5553756442	5553756443	1/3/12	B	1
2	Department of CIS	Miguel Long		1238 Walsh Drive	Suite 2202	Galway	NY	12074	5553854431	5553854433	1/3/12	B	2
3	Advantage Sales	Tracy Beck	[email protected]	4215 South 81 Street		East Greenville	PA	18041	5554445555	5554445556	1/5/11	B	1
4	Kinzer & Sons	Beatrice Peterson		9020 N.W. 75 Street	Suite 302	Lawrenceville	GA	30044	5557539887	5557539888	1/6/11	A	2
5	Milgrom Associates	Leslie Evans	[email protected]	7500 Center Lane		Mountain Home	TN	37684	5557537830	5557537831	2/6/12	C	2
6	Lugo Computer Sales	Adam Sanchez	[email protected]	6000 Tigertail Avenue		Sellersburg	IN	47172	5554468900	5554468901	2/10/12	C	1
7	Bethune Appliance Sales	Kyle Glover	[email protected]	276 Brickell Avenue	Suite 1403	Tunnel Hill	IL	62991	5554443980	5554443981	3/10/12	A	1
8	Baker Auto Supply	Brittany Hill	[email protected]	4508 N.W. 7 Street		Jarrettsville	MD	21084	5556353454	5556353455	4/11/12	A	2
9	Howard Animal Hospital	Howard Webb	[email protected]	455 Bargello Avenue		Mc Lemoresville	TN	38235	5556664801	5556664802	5/13/12	A	2
10	Katie's Casual Wear	Katie Taylor	[email protected]	9522 S.W. 142 Street		Glenwood	NJ	07418	5552533908	5552533909	4/13/12	B	1
11	Little, Joiner, & Jones	Matthew Watson	[email protected]	7245 NW 8 Street		Steamboat Springs	CO	80488	5559741234	5559741235	8/14/12	B	2
12	Kline Bait & Tackle	Alma Snyder	blee@klineb&t.com	1000 Call Street		Birmingham	AL	35238	5553274124	5553274125	10/21/12	A	2
13	Computer Informations Systems	Douglas Stevens	[email protected]	1238 Walsh Drove	Suite 2202	Estcourt Station	ME	04741	5553854431	5553854433	11/23/12	B	2
14	Coulter Office Supplies	Ernest Guzman	[email protected]	1000 Main Street		Buckingham	PA	18912	5551239876		12/23/12	A	2

OrderID	ProductID	Quantity
1	1	1
1	6	2
2	2	2
2	8	1
2	10	1
3	2	2
3	8	2
3	12	1
4	3	1
4	7	1
4	9	2
4	15	1
5	1	3
6	1	2
6	6	2
6	9	2
7	2	3
7	10	2
7	13	2
8	2	1
8	3	1
9	13	2
9	14	1
9	15	1
10	1	1
10	5	1
10	9	2
11	3	1
11	6	2
11	15	1
12	2	2
13	4	2
13	7	1
13	12	1
14	1	1
14	5	1
14	10	1
14	12	1
14	18	2
15	14	1
15	16	1
15	20	2
15	21	2
16	1	2
16	2	2
16	8	2
16	14	1
17	23	1
18	1	2
18	3	2
18	24	1
19	6	2
19	10	1
19	17	1
20	1	1
20	2	1
20	3	1
21	8	1
21	10	1
21	23	1
22	4	3
23	2	2
23	6	1
23	14	1
23	23	1
24	5	1
25	1	3
25	7	2
25	20	2
25	21	2
26	12	1
26	18	4
27	13	1
27	19	2
27	23	1
28	3	1
28	9	2
28	19	1
29	5	1
29	21	1
30	3	2
30	9	1
30	21	3
31	6	2
31	19	2
31	24	1

OrderID	OrderDate	CustomerID	PaymentType	Comments
1	1/3/12	1	Cash	Will pick up order
2	1/3/12	2	Cash
3	1/5/12	3	Check	Next day delivery
4	1/6/12	4	Credit Card
5	1/6/12	5	Purchase Order	Will pick up order
6	1/6/12	1	Check
7	1/7/12	3	Purchase Order
8	1/10/12	6	Credit Card
9	1/10/12	7	Check	Will pick up order
10	1/11/12	8	Purchase Order	Next day delivery
11	1/11/12	2	Check
12	1/13/12	9	Check	Will pick up order
13	1/13/12	10	Check
14	1/14/12	7	Cash
15	1/14/12	11	Purchase Order
16	1/16/12	11	Credit Card	Will pick up order
17	1/21/12	12	Cash	Next day delivery
18	1/21/12	1	Credit Card
19	1/22/12	2	Check
20	1/23/12	13	Purchase Order
21	1/23/12	14	Purchase Order	Next day delivery
22	1/23/12	8	Cash	Will pick up order
23	1/23/12	6	Credit Card
24	1/24/12	10	Check
25	1/24/12	3	Check
26	1/25/12	4	Purchase Order	Next day delivery
27	1/25/12	5	Check
28	1/25/12	6	Check	Will pick up order
29	1/26/12	10	Cash
30	1/26/12	11	Check
31	1/26/12	14	Cash

ProductID	ProductName	Description	Cost	MarkupPercent	RefrigerationNeeded	Brand	YearIntroduced
1	Coffee - Colombian Supreme	24/Case, Pre-Ground 1.75 Oz Bags	¤ 18.40	0.5	false	Discount	2012
2	Coffee - Hazelnut	24/Case, Pre-Ground 1.75 Oz Bags	¤ 26.45	1.0	false	Premium	2012
3	Coffee - Mild Blend	24/Case, Pre-Ground 1.75 Oz Bags	¤ 23.00	0.5	false	House	2012
4	Coffee - Assorted Flavors	18/Case. Pre-Ground 1.75 Oz Bags	¤ 26.45	0.5	false	House	2012
5	Coffee - Decaf	24/Case, Pre-Ground 1.75 Oz Bags	¤ 23.00	0.5	false	Discount	2012
6	Tea Bags - Regular	75/Box, Individual Tea Bags	¤ 5.75	0.75	false	House	2012
7	Tea Bags - Decaf	75/Box, Individual Tea Bags	¤ 8.05	0.75	false	House	2012
8	Creamers - Assorted Flavors	400/Case, 8 50-count Boxes	¤ 23.00	1.0	false	Discount	2012
9	Creamers - Liquid	200/Case, Individual Creamers	¤ 17.25	1.0	true	Premium	2012
10	Sugar Packets	2000/Case	¤ 20.70	1.0	false	House	2012
11	Ceramic Mug	SD Company Logo	¤ 5.75	1.0	false	House	2012
12	Sugar Substitute	500/Case, 1-Serving Bags	¤ 21.85	0.5	false	Discount	2012
13	Coffee Filters	500/Case, Fits 10-12 Cup Coffee Maker	¤ 3.45	0.5	false	House	2012
14	Napkins	3000/Case, White	¤ 23.00	1.0	false	House	2012
15	Stirrers - Plastic	1000/Box	¤ 1.72	0.75	false	Discount	2012
16	Stirrers - Wood	1000/Box	¤ 1.44	1.0	false	Discount	2012
17	Spoons	500/Box, White Plastic	¤ 17.25	1.0	false	House	2012
18	Popcorn - Plain	36/Case, 3.75 Oz Microwave Bags	¤ 9.78	1.0	false	House	2012
19	Popcorn - Buttered	36/Case, 3.75 Oz Microwave Bags	¤ 10.92	1.0	false	House	2012
20	Soup - Chicken	50 Envelopes	¤ 11.50	1.0	false	Premium	2012
21	Soup - Variety Pak	50 Envelopes	¤ 13.80	1.0	false	Premium	2012
22	Styrofoam Cups - 10 ounce	1000/Case	¤ 19.55	0.5	false	House	2012
23	Styrofoam Cups - 12 ounce	1000/Case	¤ 21.85	0.5	false	House	2012
24	Milk - 1 quart	Delivered Daily	¤ 2.30	1.0	true	House	2012
25	Milk - 1 pint	Delivered Daily	¤ 1.15	1.0	true	House	2012

SalesRepID	LastName	FirstName	Address	City	State	ZipCode	HomePhone	CellPhone	SSN	Gender	BirthDate	HireDate	HighestDegree	CurrentSalary
1	Garcia	Rodrigo	476 Frosty Drive	Webber	KS	66970	5555551222	5555555556	111111111	M	1/1/68	1/1/07	Master's Degree	¤ 50,000.00
2	Xu	Huan	371 Rodeo Circle	Mine Hill	NJ	07803	5555551222	5555555556	111223333	F	11/23/81	3/15/14	Bachelor's Degree	¤ 45,000.00
3	Mukopadhyay	Priyanka	842 Purcell Road	Mount Vernon	NY	10557	5555551222	5555555556	111111111	F	1/1/61	1/1/09	Master's Degree	¤ 50,000.00

SELECT [Sales Reps].LastName, Customers.City, Orders.OrderDate, Products.ProductName, [Order Details].Quantity, Products.Cost, Products.MarkupPercent, [Cost]+[Cost]*[MarkupPercent] AS Price, [Price]*[Quantity] AS Revenue FROM [Sales Reps] INNER JOIN (Customers INNER JOIN (Orders INNER JOIN (Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID = [Order Details].OrderID) ON Customers.CustomerID = Orders.CustomerID) ON [Sales Reps].SalesRepID = Customers.SalesRepID;

OrderID	ProductID	Quantity
1	1	1
1	6	2
2	2	2
2	8	1
2	10	1
3	2	2
3	8	2
3	12	1
4	3	1
4	7	1
4	9	2
4	15	1
5	1	3
6	1	2
6	6	2
6	9	2
7	2	3
7	10	2
7	13	2
8	2	1
8	3	1
9	13	2
9	14	1
9	15	1
10	1	1
10	5	1
10	9	2
11	3	1
11	6	2
11	15	1
12	2	2
13	4	2
13	7	1
13	12	1
14	1	1
14	5	1
14	10	1
14	12	1
14	18	2
15	14	1
15	16	1
15	20	2
15	21	2
16	1	2
16	2	2
16	8	2
16	14	1
17	23	1
18	1	2
18	3	2
18	24	1
19	6	2
19	10	1
19	17	1
20	1	1
20	2	1
20	3	1
21	8	1
21	10	1
21	23	1
22	4	3
23	2	2
23	6	1
23	14	1
23	23	1
24	5	1
25	1	3
25	7	2
25	20	2
25	21	2
26	12	1
26	18	4
27	13	1
27	19	2
27	23	1
28	3	1
28	9	2
28	19	1
29	5	1
29	21	1
30	3	2
30	9	1
30	21	3
31	6	2
31	19	2
31	24	1

OrderID	ProductID	Quantity
1	1	1
1	6	2
2	2	2
2	8	1
2	10	1
3	2	2
3	8	2
3	12	1
4	3	1
4	7	1
4	9	2
4	15	1
5	1	3
6	1	2
6	6	2
6	9	2
7	2	3
7	10	2
7	13	2
8	2	1
8	3	1
9	13	2
9	14	1
9	15	1
10	1	1
10	5	1
10	9	2
11	3	1
11	6	2
11	15	1
12	2	2
13	4	2
13	7	1
13	12	1
14	1	1
14	5	1
14	10	1
14	12	1
14	18	2
15	14	1
15	16	1
15	20	2
15	21	2
16	1	2
16	2	2
16	8	2
16	14	1
17	23	1
18	1	2
18	3	2
18	24	1
19	6	2
19	10	1
19	17	1
20	1	1
20	2	1
20	3	1
21	8	1
21	10	1
21	23	1
22	4	3
23	2	2
23	6	1
23	14	1
23	23	1
24	5	1
25	1	3
25	7	2
25	20	2
25	21	2
26	12	1
26	18	4
27	13	1
27	19	2
27	23	1
28	3	1
28	9	2
28	19	1
29	5	1
29	21	1
30	3	2
30	9	1
30	21	3
31	6	2
31	19	2
31	24	1

OrderID	ProductID	Quantity
1	1	1
1	6	2
2	2	2
2	8	1
2	10	1
3	2	2
3	8	2
3	12	1
4	3	1
4	7	1
4	9	2
4	15	1
5	1	3
6	1	2
6	6	2
6	9	2
7	2	3
7	10	2
7	13	2
8	2	1
8	3	1
9	13	2
9	14	1
9	15	1
10	1	1
10	5	1
10	9	2
11	3	1
11	6	2
11	15	1
12	2	2
13	4	2
13	7	1
13	12	1
14	1	1
14	5	1
14	10	1
14	12	1
14	18	2
15	14	1
15	16	1
15	20	2
15	21	2
16	1	2
16	2	2
16	8	2
16	14	1
17	23	1
18	1	2
18	3	2
18	24	1
19	6	2
19	10	1
19	17	1
20	1	1
20	2	1
20	3	1
21	8	1
21	10	1
21	23	1
22	4	3
23	2	2
23	6	1
23	14	1
23	23	1
24	5	1
25	1	3
25	7	2
25	20	2
25	21	2
26	12	1
26	18	4
27	13	1
27	19	2
27	23	1
28	3	1
28	9	2
28	19	1
29	5	1
29	21	1
30	3	2
30	9	1
30	21	3
31	6	2
31	19	2
31	24	1