NEED HELP IN MICOROSOFT ACCESS DATABASE

access_tutorials_9-12.zip

Home >Computer Science homework help >NEED HELP IN MICOROSOFT ACCESS DATABASE

Access3/Case1/Portland.accdb

ContractID	StudentID	TeacherID	ContractStartDate	ContractEndDate	LessonType	LessonLength	MonthlyLessonCost	MonthlyRentalCost
3100	CAR7534	13-1100	3/31/12	3/31/13	Piano	30	¤ 130.00	¤ 0.00
3102	MEH7551	63-1554	5/18/13	5/18/14	Guitar	30	¤ 100.00	¤ 30.00
3103	MAK7556	34-4506	6/15/13	12/15/13	Violin	30	¤ 100.00	¤ 0.00
3105	BUR7559	22-0102	4/8/12	4/8/13	Cello	60	¤ 280.00	¤ 50.00
3107	MIL7512	22-0102	6/15/13	6/15/14	Piano	60	¤ 260.00	¤ 0.00
3108	SHA7522	63-1554	6/17/12	6/17/13	Guitar	30	¤ 100.00	¤ 30.00
3110	HIR7521	17-1798	9/9/12	9/9/13	Percussion	30	¤ 120.00	¤ 0.00
3113	YAM7535	91-0178	9/10/12	9/10/13	Violin	60	¤ 280.00	¤ 0.00
3114	MEN7541	70-4490	9/10/12	9/10/13	Guitar	60	¤ 200.00	¤ 30.00
3117	BRA7545	55-5310	3/11/13	3/11/14	Voice	30	¤ 120.00	¤ 0.00
3119	BAR7544	55-5310	9/15/12	3/15/13	Voice	30	¤ 100.00	¤ 0.00
3121	ROC7545	96-2151	9/20/13	9/20/14	Guitar	30	¤ 100.00	¤ 0.00
3122	SMI7538	17-1798	8/24/12	2/24/13	Percussion	30	¤ 120.00	¤ 75.00
3125	TEA7562	22-0102	10/1/12	10/1/13	Cello	60	¤ 280.00	¤ 50.00
3126	MCE7551	70-4490	12/1/12	6/1/13	Guitar	30	¤ 100.00	¤ 30.00
3128	FOR7548	72-0005	4/7/13	10/7/13	Violin	30	¤ 100.00	¤ 35.00
3130	TAN7549	72-0005	4/26/13	4/26/14	Violin	60	¤ 200.00	¤ 35.00
3133	LAM7537	63-1554	9/2/13	9/2/14	Saxophone	60	¤ 200.00	¤ 25.00
3137	BOL7533	63-1554	1/9/13	1/9/14	Guitar	60	¤ 200.00	¤ 30.00
3139	ECK7542	96-2151	7/15/13	7/15/14	Guitar	60	¤ 200.00	¤ 30.00
3141	KAL7530	13-1100	3/23/13	3/23/14	Piano	30	¤ 130.00	¤ 0.00
3144	LIM7553	70-4490	10/15/12	10/15/13	Guitar	30	¤ 100.00	¤ 0.00
3145	BRA7545	98-9123	9/14/13	9/14/14	Flute	30	¤ 130.00	¤ 25.00
3146	MIL7512	63-1554	12/1/13	6/1/14	Guitar	30	¤ 100.00	¤ 0.00
3147	GRE7502	96-2151	9/13/13	9/13/14	Saxophone	30	¤ 100.00	¤ 25.00
3148	APP7509	17-1798	2/1/12	2/1/13	Percussion	30	¤ 120.00	¤ 75.00
3150	THO7505	77-1201	2/2/13	2/2/14	Voice	30	¤ 100.00	¤ 0.00
3152	RIV7511	17-1798	2/2/13	8/2/13	Percussion	60	¤ 260.00	¤ 75.00
3153	PEN7515	98-9123	10/5/13	10/5/14	Flute	30	¤ 130.00	¤ 0.00
3156	HAM7518	96-2151	8/4/13	8/4/14	Saxophone	60	¤ 200.00	¤ 0.00
3157	YAM7535	13-1100	5/20/13	11/20/13	Piano	30	¤ 130.00	¤ 0.00
3158	GIL7529	91-0178	3/15/13	3/15/14	Violin	60	¤ 280.00	¤ 35.00
3162	CAR7534	13-1100	3/31/13	3/31/14	Piano	30	¤ 130.00	¤ 0.00
3164	MEH7551	63-1554	9/21/13	3/21/14	Saxophone	30	¤ 100.00	¤ 0.00
3165	BUR7559	22-0102	4/8/13	4/8/14	Cello	30	¤ 140.00	¤ 50.00
3166	COR7531	22-0102	6/16/12	12/16/12	Piano	30	¤ 125.00	¤ 0.00
3167	TAN7549	13-1100	11/3/13	11/3/14	Piano	30	¤ 130.00	¤ 0.00
3168	GAR7553	34-4506	6/16/12	6/16/13	Violin	30	¤ 100.00	¤ 0.00
3169	SHA7522	63-1554	6/17/13	6/17/14	Guitar	30	¤ 100.00	¤ 0.00
3170	KAL7530	34-4506	10/20/13	4/20/14	Violin	30	¤ 100.00	¤ 35.00
3171	HIR7521	17-1798	9/9/13	9/9/14	Percussion	60	¤ 260.00	¤ 0.00
3172	MEN7541	70-4490	9/10/13	9/10/14	Guitar	30	¤ 100.00	¤ 0.00
3174	BAR7544	77-1201	3/15/13	9/15/13	Voice	30	¤ 100.00	¤ 0.00
3175	SMI7538	17-1798	2/24/13	2/24/14	Percussion	60	¤ 260.00	¤ 0.00
3176	VAR7527	91-0178	3/21/13	3/21/14	Violin	30	¤ 140.00	¤ 35.00
3179	MCE7551	70-4490	6/1/13	6/1/14	Guitar	60	¤ 200.00	¤ 0.00

StudentID	FirstName	LastName	Address	City	State	Zip	CreditRelationship	CreditCardNum
BUR7559	Rebecca	Burmeister	279 Summer St	Cornelius	OR	97113	Mother	1357-2468-9753-8642
COR7531	Allison	Corbett	205 Cedar St	Fairview	OR	97030	Mother	8908-9999-9999-9999
ECK7542	Adam	Eckstein	12830 SW Trigger Dr	Beaverton	OR	97008	Father	7890-8888-8888-8888
FOR7548	Samantha	Forrest	19210 SE Division St	Portland	OR	97266	Mother	4989-7777-7777-7777
LAM7537	Blake	Lamont	14305 SE Powell Blvd	Portland	OR	97236	Student	1908-6666-6666-6666
MEN7541	Weldon	Mendoza	1347 Lancewood St	Beaverton	OR	97008	Father	2196-5555-5555-5555
THO7505	Ben	Thorsten	60 Yearling Ct	Beaverton	OR	97008	Father	6736-4444-4444-4444
VAR7527	Leslie	Vargas	11084 SE 63rd St	Portland	OR	97222	Mother	2217-3333-3333-3333

InstrumentNum	Instrument	MonthlyRentalCost
19	Cello	¤ 50.00
20	Cello	¤ 50.00
21	FLute	¤ 25.00
22	Flute	¤ 30.00
23	Flute	¤ 25.00
24	Guitar	¤ 30.00
25	Guitar	¤ 30.00
26	Guitar	¤ 30.00
27	Guitar	¤ 45.00
28	Guitar	¤ 30.00
29	Guitar	¤ 50.00
30	Percussion	¤ 75.00
31	Percussion	¤ 90.00
32	Percussion	¤ 75.00
33	Saxophone	¤ 35.00
34	Saxophone	¤ 35.00
35	Saxophone	¤ 35.00
36	Violin	¤ 35.00
37	Violin	¤ 35.00
39	Violin	¤ 35.00
40	Violin	¤ 35.00
41	Violin	¤ 35.00

InstrumentNum	ContractID
19	3105
19	3165
20	3125
21	3145
24	3102
25	3108
25	3139
26	3114
28	3126
29	3137
30	3122
32	3148
32	3152
33	3133
34	3147
36	3128
36	3170
37	3130
39	3158
40	3176

StudentID	FirstName	LastName	Address	City	State	Zip	Phone	BirthDate	Gender
APP7509	Student	Name	15675 SW Greens Way	Portland	OR	97224	5039682245	10/10/96	M
BAR7544	Andrea	Barreau	7660 SW 135th Ave	Beaverton	OR	97008	5035792277	11/28/99	F
BOL7533	Eddie	Bolton	320 Main St	Gresham	OR	97236	5037605326	6/12/97	M
BRA7545	Lindsay	Brandenburg	78 Calaveras Ct	Beaverton	OR	97007	5036724409	8/20/00	F
BUR7500	Joshua	Burgess	11250 SW Walnut St	Portland	OR	97223	5415631135	1/12/99	M
BUR7559	Sara	Burmeister	279 Summer St	Cornelius	OR	97113	5033578410	9/24/01	F
CAR7534	Nicki	Carroll	1401 Lorraine Dr	Beaverton	OR	97229	5036173045	10/5/99	F
COR7531	Mary Beth	Corbett	205 Cedar St	Fairview	OR	97030	5036740838	7/26/02	F
ECK7542	Mark	Eckstein	12830 SW Trigger Dr	Beaverton	OR	97008	5035249610	3/27/01	M
FOR7548	Nicole	Forrest	19210 SE Division St	Portland	OR	97266	5032562368	10/24/03	F
GAR7553	Brett	Garver	17015 NW Holcomb Dr	Portland	OR	97229	5036908879	2/4/02	M
GIL7529	Aimee	Gilbertson	11763 SE Reedway St	Portland	OR	97266	5037623027	11/29/98	F
GRE7502	Melissa	Greco	1329 SE 135th Ave	Portland	OR	97233	5032564038	9/13/01	F
HAM7518	Akashi	Hamada	130 Maple Ave	Beaverton	OR	97229	5036177433	4/4/00	M
HIR7521	Erica	Hirsch	655 Langley Rd	Cornelius	OR	97123	5035472553	6/14/00	F
KAL7530	Ansis	Kalnajs	5504 SW 14th Ave	Portland	OR	97239	5032468864	4/23/00	M
LAM7537	Blake	Lamont	14305 SE Powell Blvd	Portland	OR	97236	5037611430	1/31/02	M
LIM7553	Youngho	Lim	14320 SW Barlow Rd	Beaverton	OR	97008	5034306218	3/15/03	M
MAK7556	Kim	Maki	15500 SW Allen St	Portland	OR	97223	5415636639	7/27/02	F
MCE7551	Danny	McElroy	1965 Alpine St	Cornelius	OR	97113	5035301269	12/13/99	M
MEH7551	Amrita	Mehta	18615 Yamhill Circle	Portland	OR	97233	5034910559	11/13/03	F
MEN7541	James	Mendoza	1347 Lancewood St	Beaverton	OR	97008	5036461155	4/18/97	M
MIL7512	Andy	Miller	6168 NE Rosebay Dr	Hillsboro	OR	97124	5036813379	2/16/99	M
NEL7584	Kayla	Nelson	15540 Belleview Dr	Portland	OR	97229	5415633156	10/13/03	F
PAP7524	Teddy	Pappas	78 Fairview Rd	Beaverton	OR	97007	5036726004	6/16/03	M
PEN7515	Dana	Pennimore	4015 SE 149th Ave	Portland	OR	97236	5037618155	11/20/97	F
RIV7511	John	Rivera	5436 NE Farmcrest St	Hillsboro	OR	97124	5036401517	5/22/99	M
ROC7545	Max	Roche	2385 Overlook Dr	Hillsboro	OR	97124	5036173717	3/23/98	M
SHA7522	Stacey	Shanahan	17440 Holcomb Dr	Portland	OR	97229	5415637998	8/22/02	F
SMI7538	Wilson	Smith	1634 Marian St	Gresham	OR	97233	5032584232	10/19/98	M
TAN7549	Kendra	Tang	2350 SE 157th Ave	Portland	OR	97233	5037617302	6/6/02	F
TEA7562	Jeff	Tealey	304 Forest Ave	Hillsboro	OR	97124	5036171027	10/14/03	M
THO7505	Maddie	Thorsten	60 Yearling Ct	Beaverton	OR	97008	5035242125	7/24/99	F
VAR7527	Kristin	Vargas	11084 SE 63rd St	Portland	OR	97222	5037625928	9/18/98	F
YAM7535	Angie	Yamamoto	8850 SW Burnham St	Portland	OR	97223	5036039451	9/30/96	F

TeacherID	FirstName	LastName	Degree	School	HireDate	TakesBeginners	Coordinator
13-1100	Yuka	Koyama	MM	Pacific University	1/13/12	false	17-1798
17-1798	Richard	Jacobson	PhD	Pacific University	1/15/12	false
22-0102	Andre	Dvorak	BM	University of Portland	3/3/12	false	91-0178
34-4506	Marilyn	Schwartz	BM	University of Portland	5/1/12	true	17-1798
55-5310	Annamaria	Romano	BM	Lewis & Clark College	3/12/12	true	17-1798
63-1554	Carey	Eberle	BM	University of Oregon	6/3/12	true	91-0178
70-4490	Roger	Norris	BA	University of Portland	9/2/12	true	17-1798
72-0005	Sachi	Tanaka	BM	Lawrence University	3/30/13	true	17-1798
77-1201	Joy	Culbertson	BM	Reed College	1/25/13	true	17-1798
91-0178	Fred	Bishop	DMA	Eastman School of Music	8/14/12	false
96-2151	Eric	Mueller	BA	Whitman College	6/22/13	true	91-0178
98-9123	Diana	Reese	MM	Berklee College of Music	9/2/13	false	91-0178

SELECT [tblStudent].[StudentID], [tblStudent].[LastName], [tblStudent].[FirstName] FROM tblStudent ORDER BY [LastName], [FirstName];

SELECT [tblTeacher].[TeacherID], [tblTeacher].[LastName], [tblTeacher].[FirstName] FROM tblTeacher ORDER BY [LastName], [FirstName];

SELECT [tblContract].[ContractID] FROM tblContract;

PARAMETERS __TeacherID Value; SELECT DISTINCTROW * FROM tblContract AS frmTeachersAndContracts WHERE ([__TeacherID] = TeacherID);

SELECT DISTINCTROW * FROM tblContract;

SELECT DISTINCTROW * FROM tblStudent;

SELECT DISTINCTROW * FROM tblTeacher;

SELECT [tblStudent].[FirstName], [tblStudent].[LastName] AS tblStudent_LastName, [tblStudent].[Phone], [tblContract].[ContractID], [tblTeacher].[LastName] AS tblTeacher_LastName, [tblContract].[LessonType], [tblContract].[LessonLength], [tblContract].[MonthlyLessonCost], [tblContract].[MonthlyRentalCost], [tblStudent].[StudentID] FROM tblTeacher INNER JOIN (tblStudent INNER JOIN tblContract ON tblStudent.StudentID=tblContract.StudentID) ON tblTeacher.TeacherID=tblContract.TeacherID;

SELECT DISTINCTROW * FROM tblStudent;

SELECT DISTINCTROW * FROM tblTeacher;

SELECT tblStudent.FirstName, tblStudent.LastName, tblStudent.City, tblStudent.Phone FROM tblStudent WHERE (((tblStudent.Phone) Like "541*")) ORDER BY tblStudent.LastName;

SELECT tblTeacher.LastName, tblContract.StudentID, tblContract.ContractEndDate, tblContract.LessonLength, tblContract.MonthlyLessonCost FROM tblTeacher INNER JOIN tblContract ON tblTeacher.TeacherID = tblContract.TeacherID WHERE (((tblContract.ContractEndDate)>=#7/1/2013#) AND ((tblContract.LessonType)="Guitar")) ORDER BY tblTeacher.LastName, tblContract.StudentID;

SELECT tblTeacher.LastName, tblContract.StudentID, tblContract.ContractEndDate, tblContract.LessonType, tblContract.LessonLength, tblContract.MonthlyLessonCost FROM tblTeacher INNER JOIN tblContract ON tblTeacher.TeacherID = tblContract.TeacherID WHERE (((tblContract.ContractEndDate)>=#7/1/2013#)) ORDER BY tblTeacher.LastName, tblContract.StudentID;

SELECT tblTeacher.LastName, tblContract.StudentID, tblContract.ContractEndDate, tblContract.LessonType, tblContract.LessonLength, tblContract.MonthlyLessonCost FROM tblTeacher INNER JOIN tblContract ON tblTeacher.TeacherID = tblContract.TeacherID ORDER BY tblTeacher.LastName, tblContract.StudentID;

TRANSFORM Count(tblContract.ContractID) AS CountOfContractID SELECT tblContract.LessonType, Count(tblContract.ContractID) AS [Total Number of Lessons] FROM tblContract GROUP BY tblContract.LessonType PIVOT IIf([LessonLength]=30,"Number of 30-Minute Lessons","Number of 60-Minute Lessons");

SELECT tblContract.ContractID, tblContract.StudentID, tblContract.TeacherID, tblContract.ContractStartDate, tblContract.ContractEndDate, tblContract.LessonType, tblContract.LessonLength, tblContract.MonthlyLessonCost, tblContract.MonthlyRentalCost FROM tblContract WHERE (((tblContract.LessonType) Like [Type the lesson type:] & "*"));

SELECT tblContract.StudentID, tblContract.LessonType, tblContract.ContractID, tblContract.TeacherID, tblContract.ContractStartDate, tblContract.ContractEndDate, tblContract.LessonLength, tblContract.MonthlyLessonCost, tblContract.MonthlyRentalCost FROM tblContract WHERE (((tblContract.StudentID) In (SELECT [StudentID] FROM [tblContract] As Tmp GROUP BY [StudentID],[LessonType] HAVING Count(*)>1 And [LessonType] = [tblContract].[LessonType]))) ORDER BY tblContract.StudentID, tblContract.LessonType;

SELECT tblStudent.FirstName, tblStudent.LastName, tblStudent.City, tblStudent.Phone FROM tblStudent WHERE (((tblStudent.City)<>"Portland")) ORDER BY tblStudent.City;

SELECT tblTeacher.LastName, tblContract.StudentID, tblContract.ContractEndDate, tblContract.LessonType, tblContract.LessonLength, tblContract.MonthlyLessonCost FROM tblTeacher INNER JOIN tblContract ON tblTeacher.TeacherID = tblContract.TeacherID WHERE (((tblContract.LessonType) In ("Cello","Flute","Violin"))) ORDER BY tblTeacher.LastName, tblContract.StudentID;

SELECT tblStudent.LastName, tblStudent.FirstName, tblStudent.Phone FROM tblStudent ORDER BY tblStudent.LastName, tblStudent.FirstName;

SELECT tblStudent.StudentID, tblStudent.FirstName, tblStudent.LastName, tblStudent.Address, tblStudent.City, tblStudent.State, tblStudent.Zip, tblStudent.Phone, tblStudent.BirthDate, tblStudent.Gender FROM tblStudent LEFT JOIN tblContract ON tblStudent.[StudentID] = tblContract.[StudentID] WHERE (((tblContract.StudentID) Is Null));

SELECT tblTeacher.LastName, tblTeacher.FirstName, tblContract.LessonType, tblStudent.FirstName, tblStudent.LastName, tblContract.MonthlyLessonCost, tblContract.MonthlyRentalCost FROM tblTeacher INNER JOIN (tblStudent INNER JOIN tblContract ON tblStudent.StudentID = tblContract.StudentID) ON tblTeacher.TeacherID = tblContract.TeacherID ORDER BY tblTeacher.LastName, tblTeacher.FirstName, tblContract.LessonType;

SELECT tblTeacher.TeacherID, [FirstName] & " " & [LastName] AS TeacherName, tblTeacher.Degree, tblTeacher.School, tblTeacher.HireDate, tblTeacher.TakesBeginners FROM tblTeacher ORDER BY tblTeacher.LastName, tblTeacher.FirstName;

SELECT tblTeacher.FirstName, tblTeacher.LastName, tblTeacher.HireDate FROM tblTeacher ORDER BY tblTeacher.LastName, tblTeacher.FirstName;

Access3/Case1/Room.accdb

RoomNum	RentalCost	RoomType
6	¤ 15.00	Private
7	¤ 15.00	Private
8	¤ 40.00	Group
9	¤ 20.00	Private
10	¤ 55.00	Group

Access3/Case2/Exercise.accdb

ClassID	MemberID
300	1103
300	1106
300	1135
300	1151
300	1158
301	1131
301	1144
302	1111
302	1115
302	1125
306	1137
306	1148
306	1160
306	1170
307	1121
307	1136
307	1155
307	1159
307	1169
311	1137
311	1170
313	1103
313	1135
313	1158
314	1165
315	1105
315	1117
315	1126
315	1140
315	1143
315	1154
315	1167
315	1172
316	1117
316	1140
316	1154

MemberID	CardNum	ExpDate
1137	9876-5432-1234-5678	3/4/14
1151	8642-0975-3196-3083	10/3/14
1155	3055-2529-8786-6812	9/4/14
1159	6334-4300-1000-7219	7/3/14

MemberID	ProgramID	FirstName	LastName	Street	City	State	Zip	Phone	JoinDate	ExpirationDate	MembershipStatus	MemberComments
1103	201	Student	Name	29 Prospect Street	Ashland	VA	23005	804-550-3050	1/16/13	1/16/14	Active	Commentary about the types of physical activities I pursue.
1105	204	Barry	Hassan	9 Harrington Avenue	Richmond	VA	23220	804-236-6717	3/2/13	3/2/15	Active
1106	202	Debbie	Ward	18 Barton Street	Richmond	VA	23219	804-674-8462	4/22/13	10/22/13	Active
1110	201	Ashish	Mittal	103 Hubbard Way	Glen Allen	VA	23058	804-553-7986	4/3/13	4/3/14	On Hold
1111	203	Liz	Sorrento	134 Lincoln Road	Chester	VA	23831	804-751-1270	2/10/13	8/20/13	Active
1115	203	Michelle	Kim	290 Brook Mill Road	Bon Air	VA	23235	804-323-0291	5/17/13	5/17/15	Active
1116	207	Robert	DeCosta	35 Washington Street	Richmond	VA	23220	804-236-4121	8/4/13	8/4/14	Inactive
1117	203	Paul	Novick	302 Great Road	Ashland	VA	23005	804-550-1164	9/20/13	9/20/15	Active
1120	202	Carmen	Sanchez	1294 Monument Avenue	Richmond	VA	23219	804-674-5991	10/18/13	9/25/14	On Hold
1121	209	Kelly	Smith	7 Lakeview Road	Chester	VA	23831	804-751-0466	7/18/13	7/18/14	Active
1125	203	Jessica	Picard	17 Briarwood Lane	Mechanicsville	VA	23111	804-559-0909	6/22/13	12/22/13	Active
1126	204	Sam	Bethel	258 Elm Street	Mechanicsville	VA	23111	804-559-1750	5/19/13	5/19/14	Active
1130	206	John	Ziminsky	1702 Hunter Ave	Chester	VA	23831	804-751-4052	10/5/13	10/5/15	On Hold
1131	207	Olivia	Alexander	29 Newtown Circle	Richmond	VA	23220	804-236-0642	3/11/13	3/11/15	Active
1135	201	Tina	Sun	682 Hickory Way	Chester	VA	23831	804-751-9111	6/3/13	6/3/14	Active
1136	212	Mary	Grant	913 Main Street	Chester	VA	23831	804-751-8772	5/1/13	5/1/15	Active
1137	210	Alan	Fraser	41 Carver Hill Road	Mechanicsville	VA	23111	804-559-5885	3/20/13	3/20/14	Active
1139	210	Peter	Grigas	3 Pleasant Street	Glen Allen	VA	23058	804-553-1642	6/9/13	6/9/15	Inactive
1140	206	Ronald	Cunningham	52 Putnam Road	Ashland	VA	23005	804-550-7732	2/4/13	8/4/13	Active
1142	207	Kye	Nguyen	456 Harringont Avenue	Richmond	VA	23220	804-236-0325	2/20/13	2/20/15	On Hold
1143	206	Barry	Feinberg	73 Endicott Avenue	Chester	VA	23831	804-751-1119	10/5/13	10/5/14	Active
1144	208	Colleen	Murphy	47 Winter Street	Mechanicsville	VA	23111	804-559-1651	6/9/13	6/9/14	Active
1146	209	Marlene	Halpin	75 Elm Boulevard	Bon Air	VA	23235	804-323-0346	7/17/13	7/17/15	On Hold
1147	210	Elijah	Slomich	108 Roosevelt Avenue	Mechanicsville	VA	23111	804-559-5060	8/2/13	8/2/15	On Hold
1148	212	Todd	Wolfe	18 Parker Avenue	Richmond	VA	23219	804-674-2140	9/20/13	9/20/14	Active
1150	203	Zach	Adgate	92 Apple Blossom Lane	Ashland	VA	23005	804-550-6119	10/15/13	10/15/14	Active
1151	202	Isabella	Cimino	429 South Street	Ashland	VA	23005	804-550-9870	8/2/13	8/2/14	Active
1153	212	Vinnie	Tumbiolo	358 East Street	Richmond	VA	23219	804-674-1434	5/19/13	5/29/15	Inactive
1154	204	Jamal	Asmal	9872 Turnpike Road	Richmond	VA	23220	804-236-2194	6/7/13	6/7/14	Active
1155	210	Juliette	Larsen	35 Walnut Street	Glen Allen	VA	23058	804-553-1275	8/1/13	8/1/14	Active
1156	208	Kurt	Eisler	5 Bradford Circle	Richmond	VA	23220	804-236-8961	4/17/13	4/17/15	On Hold
1158	201	Pedro	Fuente	89 Mockingbird Lane	Chester	VA	23831	804-751-1847	9/5/13	9/5/14	Active
1159	211	Maggie	Hadley	6 Rose Court	Ashland	VA	23005	804-550-6103	3/19/13	3/19/14	Active
1160	212	Mark	Reynolds	11 Pond Lane	Mechanicsville	VA	23111	804-559-4642	6/20/13	6/20/15	Active
1165	207	Shea	McKiernan	780 Flint Street	Ashland	VA	23005	804-550-0107	7/13/13	7/13/14	Active
1166	203	Abigail	Turner	99 Maple Road	Richmond	VA	23219	804-674-6807	9/1/13	9/1/15	On Hold
1167	204	George	Krukonis	14 Collins Drive	Richmond	VA	23219	804-674-4333	5/5/13	5/5/14	Active
1169	211	Maria	Gonzalez	100 Tower Drive	Richmond	VA	23220	804-236-6337	5/19/13	5/19/14	Active
1170	210	Ed	Curran	25 Fairway Drive	Bon Air	VA	23235	804-323-6824	10/18/13	12/3/13	Active
1172	206	Tung	Lin	40 Green Boulevard	Richmond	VA	23220	804-674-0227	11/16/13	11/26/14	Active

ProgramID	ProgramType	MonthlyFee	PhysicalRequired
201	Junior Full (ages 13-17)	¤ 35.00	true
202	Junior Limited (ages 13-17)	¤ 25.00	true
203	Young Adult Full (ages 18-25)	¤ 45.00	false
204	Young Adult Limited (ages 18-25)	¤ 30.00	false
205	Young Adult Special (ages 18-25)	¤ 20.00	false
206	Adult1 Limited (ages 26-35)	¤ 40.00	false
207	Adult2 Full (ages 36-50)	¤ 55.00	false
208	Adult2 Limited (ages 36-50)	¤ 40.00	false
209	Senior1 Full (ages 51-65)	¤ 45.00	true
210	Senior1 Limited (ages 51-65)	¤ 30.00	true
211	Senior2 Full (ages 66 and over)	¤ 35.00	true
212	Senior2 Limited (ages 66 and over)	¤ 25.00	true
213	Senior2 Special (ages 66 and over)	¤ 15.00	true

ClassID	ClassDay	StartTime	ClassLength
300	MW	12/30/99	12/30/99
301	MW	12/30/99	12/30/99
302	MW	12/30/99	12/30/99
303	MW	12/30/99	12/30/99
304	MW	12/30/99	12/30/99
305	TH	12/30/99	12/30/99
306	TH	12/30/99	12/30/99
307	TH	12/30/99	12/30/99
308	TH	12/30/99	12/30/99
309	TH	12/30/99	12/30/99
310	TH	12/30/99	12/30/99
311	TH	12/30/99	12/30/99
312	TH	12/30/99	12/30/99
313	TH	12/30/99	12/30/99
314	TH	12/30/99	12/30/99
315	TH	12/30/99	12/30/99
316	TH	12/30/99	12/30/99
317	TH	12/30/99	12/30/99

PARAMETERS __ProgramID Value; SELECT DISTINCTROW * FROM tblMember AS frmProgramsAndMembers WHERE ([__ProgramID] = ProgramID);

PARAMETERS __ProgramID Value; SELECT DISTINCTROW * FROM tblMember AS frmProgramsWithMembers WHERE ([__ProgramID] = ProgramID);

SELECT DISTINCTROW * FROM tblProgram;

SELECT tblProgram.ProgramID, tblProgram.ProgramType, tblProgram.MonthlyFee, tblProgram.PhysicalRequired FROM tblProgram;

SELECT DISTINCTROW * FROM tblProgram;

SELECT [tblProgram].[ProgramID], [tblProgram].[ProgramType], [tblProgram].[MonthlyFee], [tblMember].[MemberID], [tblMember].[FirstName], [tblMember].[LastName], [tblMember].[City], [tblMember].[Phone], [tblMember].[JoinDate] FROM tblProgram INNER JOIN tblMember ON tblProgram.ProgramID=tblMember.ProgramID;

SELECT tblMember.ExpirationDate, tblMember.MemberID, tblMember.ProgramID, tblMember.FirstName, tblMember.LastName, tblMember.Street, tblMember.City, tblMember.State, tblMember.Zip, tblMember.Phone, tblMember.JoinDate, tblMember.MembershipStatus FROM tblMember WHERE (((tblMember.ExpirationDate) In (SELECT [ExpirationDate] FROM [tblMember] As Tmp GROUP BY [ExpirationDate] HAVING Count(*)>1 ))) ORDER BY tblMember.ExpirationDate;

SELECT Max(tblProgram.MonthlyFee) AS MaximumFee, Min(tblProgram.MonthlyFee) AS MinimumFee, Avg(tblProgram.MonthlyFee) AS AverageFee FROM tblProgram;

SELECT tblMember.City, Max(tblProgram.MonthlyFee) AS MaximumFee, Min(tblProgram.MonthlyFee) AS MinimumFee, Avg(tblProgram.MonthlyFee) AS AverageFee FROM tblProgram INNER JOIN tblMember ON tblProgram.ProgramID = tblMember.ProgramID GROUP BY tblMember.City;

SELECT tblMember.MemberID, tblMember.FirstName, tblMember.LastName, tblMember.JoinDate, tblProgram.ProgramType, tblProgram.MonthlyFee FROM tblProgram INNER JOIN tblMember ON tblProgram.ProgramID = tblMember.ProgramID WHERE (((tblMember.JoinDate) Between #6/1/2013# And #6/30/2013#));

SELECT [FirstName] & " " & [LastName] AS MemberName, tblMember.Street, [City] & " " & [State] & " " & [Zip] AS CityLine, tblMember.Phone FROM tblMember ORDER BY tblMember.LastName, tblMember.FirstName;

TRANSFORM Sum(qryMonthlyFeeStatus.MonthlyFee) AS SumOfMonthlyFee SELECT qryMonthlyFeeStatus.ProgramType, Sum(qryMonthlyFeeStatus.MonthlyFee) AS [Total Of MonthlyFee] FROM qryMonthlyFeeStatus GROUP BY qryMonthlyFeeStatus.ProgramType PIVOT qryMonthlyFeeStatus.MonthlyFeeStatus;

SELECT tblProgram.ProgramType, tblProgram.MonthlyFee, tblMember.FirstName, tblMember.LastName, IIf([MembershipStatus]="Active","Active","Not Active") AS MonthlyFeeStatus FROM tblProgram INNER JOIN tblMember ON tblProgram.ProgramID = tblMember.ProgramID;

SELECT tblMember.FirstName, tblMember.LastName, tblMember.JoinDate, tblProgram.MonthlyFee, tblProgram.PhysicalRequired FROM tblProgram INNER JOIN tblMember ON tblProgram.ProgramID = tblMember.ProgramID WHERE (((tblProgram.PhysicalRequired)=Yes)) ORDER BY tblMember.JoinDate DESC;

SELECT tblProgram.ProgramID, tblProgram.ProgramType, tblProgram.MonthlyFee, tblProgram.PhysicalRequired FROM tblProgram;

SELECT tblProgram.ProgramID, tblProgram.ProgramType, tblProgram.MonthlyFee, tblMember.MembershipStatus, tblMember.FirstName, tblMember.LastName FROM tblProgram INNER JOIN tblMember ON tblProgram.ProgramID = tblMember.ProgramID ORDER BY tblProgram.ProgramID, tblMember.MembershipStatus, tblMember.LastName;

SELECT tblProgram.ProgramID, tblProgram.ProgramType, tblProgram.MonthlyFee, tblProgram.PhysicalRequired FROM tblProgram LEFT JOIN tblMember ON tblProgram.[ProgramID] = tblMember.[ProgramID] WHERE (((tblMember.ProgramID) Is Null));

SELECT tblMember.MemberID, tblMember.FirstName, tblMember.LastName, tblMember.JoinDate FROM tblMember WHERE (((tblMember.City) In ("Richmond","Chester")) AND ((tblMember.MembershipStatus)="Active")) ORDER BY tblMember.LastName;

SELECT tblMember.MemberID, tblMember.FirstName, tblMember.LastName, tblMember.JoinDate FROM tblMember WHERE (((tblMember.City)="Richmond") AND ((tblMember.MembershipStatus)="On Hold")) ORDER BY tblMember.LastName;

SELECT tblMember.City, tblMember.FirstName, tblMember.LastName, tblProgram.MonthlyFee, tblMember.Phone, tblMember.MembershipStatus FROM tblProgram INNER JOIN tblMember ON tblProgram.ProgramID = tblMember.ProgramID WHERE (((tblMember.City)="Bon Air" Or (tblMember.City)="Glen Allen")) ORDER BY tblMember.City, tblProgram.MonthlyFee DESC;

SELECT tblProgram.ProgramID, tblProgram.ProgramType, tblProgram.MonthlyFee, tblProgram.PhysicalRequired FROM tblProgram WHERE (((tblProgram.MonthlyFee) In (20,30,40))) ORDER BY tblProgram.ProgramID DESC;

SELECT tblProgram.ProgramID, tblProgram.ProgramType, tblProgram.MonthlyFee, tblProgram.PhysicalRequired FROM tblProgram WHERE (((tblProgram.MonthlyFee) Not In (20,30,40))) ORDER BY tblProgram.ProgramID DESC;

SELECT TOP 25 PERCENT tblMember.FirstName, tblMember.LastName, tblMember.Phone, tblMember.ExpirationDate, tblMember.MembershipStatus, tblMember.ProgramID FROM tblMember ORDER BY tblMember.ExpirationDate;

Access3/Case2/Routines.accdb

RoutineID	RoutineDesc
101	Basketball
102	Golf
103	Tennis
104	Skiing
105	Cycling
106	Strength Training
107	Agility Training
108	Speed Training
109	Pilates
110	Pylometrics
111	General Conditioning
112	Ice Skating
113	Endurance Training
114	Soccer
115	Volleyball
116	Baseball
117	Football
118	Hockey
119	Lacrosse
120	Gymnastics
121	Climbing

Access3/Case2/Weight.gif

Access3/Case3/Drivers.accdb

DriverID	FirstName	LastName
1	Sam	Hughes
2	Pat	Linden
3	Terry	Benson
4	Sally	Newburgh

Access3/Case3/Salina.accdb

AgencyID	AgencyName	ContactFirstName	ContactLastName	Address	City	State	Zip	Phone
K64	Community Development	Beth	Dayton	223 Penn Ave	Salina	KS	67401	785-309-3351
K82	SeniorCare Program	Todd	Groverman	718 N Walnut	McPherson	KS	67460	620-241-3668
N33	Daycare and Family Services	Renee	Baker	825 Manchester Rd	Salina	KS	67401	785-825-5402
N57	Disability Resources Bureau	Amy	Hoffman	203 N Kansas Ave	Salina	KS	67401	785-825-0922
R15	Elder Services Division	Ed	Rindahl	102 Jefferson St	Junction City	KS	67441	785-210-7740
R27	Recreational Assistance	Neal	Tomlinson	70 Lincoln Ave	Abilene	KS	67410	785-918-4327
R31	Mental Health of Salina	Wayne	Althorp	305 N Kansas Ave	Salina	KS	67401	785-825-5060
R86	Workforce Development Office	Nina	Williams	400 Main St	Abilene	KS	67410	785-263-4255
V21	Veterans Community Services	Maureen	Dykstra	15365 Leonard St	Abilene	KS	67410	785-742-8180
W22	Vocational Rehabilitation	Kelly	Kaysen	601 Midtown Rd	McPherson	KS	67460	620-241-0889
W43	Kansas Social Services	Ray	Showalter	900 Sheridan St	Salina	KS	67401	785-827-4939
Y68	After School Youth	Linda	Mercier	131 W 18th St	Junction City	KS	67441	785-761-7147

DonationID	DonorID	AgencyID	DonationDate	DonationDesc	DonationValue	PickupRequired
2100	36001	K82	1/12/13	Cash	¤ 30.00	false
2103	36033	N57	6/2/13	Small rugs	¤ 40.00	false
2105	36001	K64	3/10/13	Cash	¤ 40.00	false
2108	36062	W22	5/17/13	Computer equipment	¤ 225.00	true
2110	36041	R31	9/7/13	Cookware	¤ 35.00	false
2112	36060	Y68	3/5/13	Cash	¤ 100.00	false
2115	36056	N57	3/19/13	Cash	¤ 25.00	false
2116	36001	K82	6/1/13	Kitchen table and chairs	¤ 347.25	true
2117	36012	K82	2/20/13	Cash	¤ 50.00	false
2119	36051	W22	4/23/13	Table saw	¤ 265.00	true
2121	36058	K64	2/24/13	Vacuum cleaner	¤ 84.00	false
2122	36016	N33	3/22/13	Cash	¤ 35.00	false
2124	36055	Y68	2/2/13	Children's furniture	¤ 265.50	true
2125	36077	R31	3/3/13	Small appliances	¤ 180.00	false
2126	36019	N57	4/4/13	Cash	¤ 75.00	false
2129	36012	R86	8/30/13	Business suits	¤ 150.00	false
2131	36049	N33	1/3/13	Snow blower	¤ 230.00	true
2133	36042	W22	6/25/13	Cash	¤ 50.00	false
2136	36037	W43	1/31/13	Table linens	¤ 45.00	false
2137	36062	R31	11/1/13	Desk lamps	¤ 78.00	false
2142	36064	N33	8/26/13	Bookshelves	¤ 238.75	true
2144	36058	N57	11/12/13	Sofa and chairs	¤ 560.00	true
2145	36020	R15	5/10/13	Cash	¤ 25.00	false
2147	36032	R15	4/17/13	Washer and dryer	¤ 225.00	true
2148	36037	W22	3/10/13	Cash	¤ 75.00	false
2151	36037	N33	5/6/13	Cash	¤ 35.00	false
2153	36070	N57	2/2/13	Linens	¤ 47.00	false
2154	36025	R31	6/12/13	Cash	¤ 50.00	false
2155	36077	R31	7/1/13	Sporting goods	¤ 267.85	true
2157	36028	W22	7/18/13	Cash	¤ 50.00	false
2159	36041	K64	9/7/13	Outdoor furniture	¤ 345.00	true
2160	36032	W43	8/21/13	Cash	¤ 45.00	false
2163	36055	R86	5/29/13	Cash	¤ 35.00	false
2167	36037	W22	11/27/13	Portable heaters	¤ 489.00	true
2168	36033	Y68	6/2/13	Sporting goods	¤ 167.00	false
2170	36074	R31	3/10/13	Office equipment	¤ 366.50	true
2171	36056	W43	5/3/13	Cash	¤ 50.00	false
2172	36033	Y68	9/24/13	Cash	¤ 35.00	false
2175	36041	R15	12/12/13	Cash	¤ 125.00	false
2177	36019	W43	9/7/13	Cutlery	¤ 67.00	false
2179	36016	K64	4/11/13	Small appliances	¤ 236.50	true
2180	36037	K64	12/3/13	Cash	¤ 50.00	false
2181	36055	N33	9/7/13	Linens	¤ 68.00	false
2182	36012	N57	11/12/13	Microwave oven	¤ 75.00	false
2184	36020	Y68	8/28/13	Computer equipment	¤ 497.00	true
2185	36074	K82	6/11/13	Cash	¤ 100.00	false
2187	36070	W43	4/19/13	Kitchen tools	¤ 40.00	false
2188	36073	R31	10/30/13	Painting supplies	¤ 135.40	false
2190	36074	N57	10/14/13	Dinnerware	¤ 127.00	false
2191	36077	W43	7/1/13	Cookware	¤ 85.75	false
2192	36073	W22	11/2/13	Welding tools	¤ 468.00	false
2193	36042	N33	12/1/13	Cash	¤ 100.00	false
2195	36064	K64	10/15/13	Sporting goods	¤ 246.00	false
2196	36051	N33	6/4/13	Bookshelves	¤ 168.90	true
2197	36033	Y68	12/5/13	Bicycles	¤ 225.00	false
2200	36032	K82	12/4/13	Books on tape	¤ 50.00	false
2202	36051	W22	12/20/13	Power tools	¤ 565.00	true
2205	36056	N33	9/7/13	Office equipment	¤ 347.00	true
2210	36060	Y68	9/1/13	Computer equipment	¤ 388.00	false
2212	36077	R86	12/20/13	Computer equipment	¤ 350.00	true
2213	36055	W43	12/13/13	Small appliances	¤ 276.00	false
2215	36070	N33	11/12/13	Bicycles	¤ 178.00	false
2219	36077	W22	12/21/13	Toys	¤ 45.00	false

StorageID	DonationID
2	2103
2	2119
2	2131
2	2147
2	2192
3	2116
3	2124
3	2144
4	2110
4	2121
4	2125
4	2167
4	2179
4	2182
4	2187
4	2190
4	2191
4	2213
6	2108
6	2170
6	2184
6	2205
6	2210
6	2212

DonorID	Title	FirstName	LastName	Phone
36001	Dr.	First	Last	7854528736
36002	Dr.	Brian	Crane	7852778767
36012	Mr.	Joe	Martinson	7858239275
36016	Mr.	Doug	Showers	6207938477
36019	Ms.	Connie	Springen	7854521178
36020	Mrs.	JoAnn	Randolph	7853096540
36023	Ms.	Carole	Tiahrt	3167719152
36025	Mrs.	Mary	Durnen	7855980569
36028	Mr.	Michael	Carlucci	3162836334
36032	Mrs.	Barbara	Schmidt	7858279713
36033	Mr.	Brian	Park	6206631418
36037	Ms.	Sherill	Kussow	7858232284
36041	Mrs.	Celia	Brubaker	6206693970
36042	Mrs.	Helen	Taft	7852389451
36049	Mrs.	Katharine	Gardiner	7858262135
36051	Mr.	Leland	Jaeger	7853091655
36055	Ms.	Tina	Sun	7858272688
36056	Mr.	Robert	Parkinson	7852635896
36058	Mrs.	Rebecca	Lawry	7858255583
36060	Mrs.	Dianne	Corrigan	7858260063
36062	Mr.	Wesley	Tarkenton	3162822556
36064	Ms.	Erika	Lange	7852631054
36070	Mrs.	Ashley	Willis	7858271862
36073	Mr.	Gerald	Persinger	6202412526
36074	Mrs.	Maria	Gonzalez	7852380337
36077	Mrs.	Meredith	Dellkamp	7858201521

FacilityID	FacilityName
A01	Cramer Storage
A02	Oakview Building

StorageID	FacilityID	Location
1	A01	Room 140
2	A01	Room 141
3	A01	Room 142
4	A02	Suite 225
5	A02	Suite 240
6	A02	Suite 250

SELECT [tblDonor].[DonorID] FROM tblDonor;

PARAMETERS __DonorID Value; SELECT DISTINCTROW * FROM tblDonation AS frmDonorDonations WHERE ([__DonorID] = DonorID);

PARAMETERS __DonorID Value; SELECT DISTINCTROW * FROM tblDonation AS frmDonorsAndDonations WHERE ([__DonorID] = DonorID);

SELECT DISTINCTROW * FROM tblDonation;

SELECT DISTINCTROW * FROM tblDonor;

SELECT [tblAgency].[AgencyName], [tblDonation].[DonorID], [tblDonation].[DonationValue], [tblDonation].[DonationID], [tblDonation].[DonationDate], [tblDonation].[DonationDesc], [tblAgency].[AgencyID] FROM tblAgency INNER JOIN tblDonation ON tblAgency.AgencyID=tblDonation.AgencyID;

SELECT DISTINCTROW * FROM tblDonor;

SELECT tblAgency.City, tblAgency.AgencyName, tblAgency.ContactFirstName, tblAgency.ContactLastName FROM tblAgency ORDER BY tblAgency.City, tblAgency.AgencyName;

SELECT tblAgency.AgencyID, tblAgency.AgencyName, [ContactFirstName] & " " & [ContactLastName] AS ContactName, tblAgency.Address, tblAgency.City, tblAgency.State, tblAgency.Zip, tblAgency.Phone FROM tblAgency ORDER BY tblAgency.AgencyName;

SELECT tblDonor.DonorID, tblAgency.AgencyName, tblDonation.DonationDesc, tblDonation.DonationValue, [DonationValue]-8.75 AS NetDonation FROM tblDonor INNER JOIN (tblAgency INNER JOIN tblDonation ON tblAgency.AgencyID = tblDonation.AgencyID) ON tblDonor.DonorID = tblDonation.DonorID WHERE (((tblDonation.PickupRequired)=Yes)) ORDER BY tblDonation.DonationValue;

SELECT Sum(tblDonation.DonationValue) AS TotalDonations, Avg(tblDonation.DonationValue) AS AverageDonation, Count(tblDonation.DonationValue) AS NumberOfDonations FROM tblDonation;

SELECT tblAgency.AgencyName, Sum(tblDonation.DonationValue) AS TotalDonations, Avg(tblDonation.DonationValue) AS AverageDonation, Count(tblDonation.DonationValue) AS NumberOfDonations FROM tblAgency INNER JOIN tblDonation ON tblAgency.AgencyID = tblDonation.AgencyID GROUP BY tblAgency.AgencyName ORDER BY tblAgency.AgencyName;

SELECT tblDonation.DonationDesc, tblDonation.DonationDate, tblDonation.DonationValue, tblDonor.FirstName, tblDonor.LastName FROM tblDonor INNER JOIN tblDonation ON tblDonor.DonorID = tblDonation.DonorID ORDER BY tblDonation.DonationDesc, tblDonation.DonationDate, tblDonor.LastName;

SELECT tblDonor.DonorID, tblDonor.FirstName, tblDonor.LastName, tblDonor.Phone FROM tblDonor ORDER BY tblDonor.DonorID;

SELECT tblDonor.DonorID, tblDonor.Title, tblDonor.FirstName, tblDonor.LastName, tblDonor.Phone FROM tblDonor LEFT JOIN tblDonation ON tblDonor.DonorID = tblDonation.DonorID WHERE (((tblDonation.DonorID) Is Null));

SELECT tblDonor.DonorID, tblDonor.FirstName, tblDonor.LastName, tblDonation.DonationDesc, tblDonation.DonationValue FROM tblDonor INNER JOIN tblDonation ON tblDonor.DonorID = tblDonation.DonorID WHERE (((tblDonation.DonationDesc)="Cash") AND ((tblDonation.DonationValue)>50)) ORDER BY tblDonation.DonationValue;

SELECT qryNetDonations.DonorID, qryNetDonations.AgencyName, qryNetDonations.DonationDesc, qryNetDonations.DonationValue, qryNetDonations.NetDonation, qryNetDonations.PickupRequired FROM qryNetDonations WHERE (((qryNetDonations.DonorID) In (SELECT [DonorID] FROM [qryNetDonations] As Tmp GROUP BY [DonorID],[AgencyName] HAVING Count(*)>1 And [AgencyName] = [qryNetDonations].[AgencyName]))) ORDER BY qryNetDonations.DonorID, qryNetDonations.AgencyName;

SELECT tblDonor.DonorID, tblAgency.AgencyName, tblDonation.DonationDesc, tblDonation.DonationValue, IIf([PickupRequired],[DonationValue]-8.75,[DonationValue]) AS NetDonation, tblDonation.PickupRequired FROM tblDonor INNER JOIN (tblAgency INNER JOIN tblDonation ON tblAgency.AgencyID = tblDonation.AgencyID) ON tblDonor.DonorID = tblDonation.DonorID ORDER BY IIf([PickupRequired],[DonationValue]-8.75,[DonationValue]) DESC;

TRANSFORM Sum(qryNetDonations.NetDonation) AS SumOfNetDonation SELECT qryNetDonations.AgencyName, Sum(qryNetDonations.NetDonation) AS [Total Of NetDonation] FROM qryNetDonations GROUP BY qryNetDonations.AgencyName PIVOT IIf([PickupRequired],"Pickup Required","No Pickup");

SELECT tblAgency.City, tblAgency.AgencyName, tblAgency.ContactFirstName, tblAgency.ContactLastName FROM tblAgency WHERE (((tblAgency.City) Not In ("Abilene","Salina"))) ORDER BY tblAgency.City, tblAgency.AgencyName;

SELECT tblDonation.DonationID, tblDonation.DonorID, tblDonation.AgencyID, tblDonation.DonationDate, tblDonation.DonationDesc, tblDonation.DonationValue, tblDonation.PickupRequired FROM tblDonation WHERE (((tblDonation.AgencyID) Like "R*" Or (tblDonation.AgencyID) Like "W*")) ORDER BY tblDonation.DonationValue DESC;

SELECT tblAgency.AgencyName, tblDonation.DonationID, tblDonation.DonationDate, tblDonation.DonationDesc FROM tblAgency INNER JOIN tblDonation ON tblAgency.AgencyID = tblDonation.AgencyID WHERE (((tblAgency.AgencyID)="K82" Or (tblAgency.AgencyID)="R15")) ORDER BY tblAgency.AgencyName, tblDonation.DonationID;

SELECT TOP 40 PERCENT tblDonor.DonorID, tblAgency.AgencyName, tblDonation.DonationDesc, tblDonation.DonationValue, IIf([PickupRequired],[DonationValue]-8.75,[DonationValue]) AS NetDonation, tblDonation.PickupRequired FROM tblDonor INNER JOIN (tblAgency INNER JOIN tblDonation ON tblAgency.AgencyID = tblDonation.AgencyID) ON tblDonor.DonorID = tblDonation.DonorID ORDER BY IIf([PickupRequired],[DonationValue]-8.75,[DonationValue]) DESC;

SELECT tblAgency.AgencyName, tblDonation.DonationID, tblDonation.DonationDate, tblDonation.DonationDesc, tblAgency.AgencyID FROM tblAgency INNER JOIN tblDonation ON tblAgency.AgencyID = tblDonation.AgencyID WHERE (((tblAgency.AgencyID)="Y68")) ORDER BY tblAgency.AgencyName, tblDonation.DonationID;

Access3/Case4/Rentals.accdb

GuestID	GuestFirstName	GuestLastName	Address	City	StateProv	PostalCode	Country	Phone
201	Student	Name	153 Summer Ave	Evanston	IL	60201	USA	847-623-0975
202	Ingrid	Gorman	207 Riverside Dr West	Windsor	ON	N9A 5K4	Canada	519-977-8577
203	Tom	Davis	5003 Wilson Blvd	Chicago	IL	60603	USA	312-897-4515
205	Richard	Nelson	34 Settlers Dr	Tinley Park	IL	60477	USA	708-292-4441
206	Li	Zhu	6509 Great Rd	Gary	IN	46401	USA	219-655-8109
207	Greta	Simmons	17 Westview Ave	Chicago	IL	60601	USA	312-729-384
209	Alex	Neeley	308 Lakeside Dr	Chicago	IL	60601	USA	312-303-1290
210	Luiz	Neto	8552 Main St	Chicago	IL	60602	USA	312-481-7053
211	Sarah	Vanesse	35 Park Rd	Crown Point	IN	46307	USA	219-377-1882
212	Janet	Froberg	433 Monroe St	Aurora	IL	60504	USA	630-788-3055
215	Brian	Campbell	14 Spencer Blvd	Gary	IN	46402	USA	219-475-3044
216	Linda	Gupta	130 Elm St	Evanston	IL	60202	USA	847-489-3339
217	Maddie	Roux	443 Front Rd	LaSalle	ON	N9J 2N7	Canada	519-734-0065
219	Peter	LaSalle	2120 E Lombard Rd	Gary	IL	46401	USA	219-908-1672
220	Alberto	Azevedo	991 Crestview Dr	Chicago	IL	60601	USA	312-842-1183
221	Amy	Shevlin	118 Forest Rd	Hammond	IN	46320	USA	219-334-1275
222	Lucas	Kalnajs	3 March Rde	Crown Point	IN	46307	USA	219-377-0312
225	Kelly	Skolnik	15 Tobin Dr	Wilmington	IL	60481	USA	815-339-1597
228	Chris	Locke	4478 Howard Ave	Windsor	ON	N8X 3Y8	Canada	519-966-9094
229	Paul	Marchant	7533 Macleod Dr	Chicago	IL	60603	USA	312-783-2209
230	Susan	Goetz	29 Evergreen Rd	Aurora	IL	60507	USA	630-442-5943

PersonID	FirstName	LastName	JobTitle	Manager
CH2468	Catherine	Higgins	Property Director
CM2222	Christopher	Murphy	Property Director
EJ7531	Elizabeth	Jackson	Property Director
EJ8888	Edward	Leary	Finance Manager	CH2468
RC1111	Robert	Crane	Staff Manager	EJ7531
SL3333	Susan	Lawrence	Staff Manager	CM2222
TD5555	Thomas	Digby	Finance Manager	EJ7531
TP8642	Theodore	Paine	Finance Manager	CM2222
VW9751	Victoria	Watson	Staff Manager	CH2468

PropertyID	PropertyName	Location	Country	NightlyRate	Bedrooms	Sleeps	PropertyType	Description
3002	Chateau St. Antoine	Loire Valley	France	¤ 1,000.00	5	9	Castle	17th century; classic architecture; extensive gardens
3003	Manoir Rouy	Burgundy	France	¤ 800.00	3	6	Manor	Rural location; breathtaking views; close to vineyards
3007	Kilglen Castle	Inverness	Scotland	¤ 3,800.00	10	20	Castle	Traditional sporting pursuits; hunting; golf; historic tours
3010	Alberford Castle	Devon	England	¤ 5,000.00	8	16	Castle	1100-acre sporting estate; hunting; fishing; historic tours
3050	Riyad Primiere	Marrakesh	Morocco	¤ 1,600.00	4	8	Villa	Central location; courtyard; pool; art museums
3107	Villa Leopoldo	Tuscany	Italy	¤ 1,300.00	4	8	Villa	Close to beach and designer boutiques; biking; boating
3142	Clough Estate	Cork	Ireland	¤ 700.00	4	7	Manor	Early 17th century; extensive gardens; lake views; fishing
3179	Chateau Sur Mer	Cannes	France	¤ 2,500.00	5	10	Castle	Views over the bay; private beach; heated pool; secure
3190	Glenlow Castle	Dundee	Scotland	¤ 3,000.00	9	18	Castle	16th century refurbished castle; golf; fishing; horseback riding
3204	Hartfield Country Manor	Surrey	England	¤ 2,500.00	7	14	Manor	Late Tudor 14-acre estate; cultural and historic tours; floral art
3222	Casa Rosa Canina	Lake Como	Italy	¤ 1,700.00	6	12	Villa	English style architecture and gardens; panoramic lake views
3395	Villa San Vincenzo	Umbria	Italy	¤ 750.00	3	6	Villa	Hillside location; breathtaking views; pool; olive groves
3423	Castle Ballydare	Kerry	Ireland	¤ 5,000.00	9	25	Castle	Mountain views; golf; fishing; hunting; horseback riding
3488	Chateau du Boisvert	Provence	France	¤ 2,000.00	11	18	Castle	17th century chateau; formal gardens; pool; chapel
3515	Riyad Etoile	Marrakesh	Morocco	¤ 3,000.00	8	16	Villa	Multiple courtyards and terraces; pool; cultural tours
3529	La Amaranta	Valencia	Spain	¤ 1,700.00	6	8	Villa	Ocean views; close to beach; pool; art and music festivals
3611	Lumley Manor	Kent	England	¤ 2,000.00	6	11	Manor	Renaissance style with Benedictine origins; naturalist tours
3628	Kenyan Seaside Retreat	Mombasa	Kenya	¤ 900.00	3	7	Villa	Spectacular sea views; pool; diving; snorkeling; sailing
3670	Bellevue Villa	St. Tropez	France	¤ 1,300.00	4	8	Villa	Close to beach; heated pool; tennis courts; gardens
3675	Casa de Las Palmas	Mallorca	Spain	¤ 2,500.00	4	8	Villa	Private estate; multiple terraces; beautiful sea views; pool

ReservationID	GuestID	PropertyID	StartDate	EndDate	People	RentalRate
503	209	3488	6/12/13	6/19/13	12	¤ 2,000.00
504	203	3529	3/11/13	3/25/13	6	¤ 1,500.00
507	225	3002	5/12/13	5/22/13	8	¤ 1,000.00
510	220	3107	5/1/13	5/15/13	6	¤ 1,000.00
511	201	3142	8/20/13	8/27/13	7	¤ 700.00
512	211	3222	4/4/13	4/11/13	8	¤ 1,600.00
513	216	3007	7/20/13	7/27/13	20	¤ 3,800.00
515	202	3050	9/8/13	9/22/13	7	¤ 1,200.00
520	215	3002	8/10/13	8/17/13	6	¤ 800.00
521	206	3395	6/3/13	6/10/13	6	¤ 750.00
522	230	3190	6/22/13	7/6/13	15	¤ 3,000.00
525	212	3010	7/11/13	7/18/13	16	¤ 5,000.00
530	229	3204	8/5/13	8/12/13	14	¤ 2,400.00
531	205	3515	11/16/13	11/23/13	11	¤ 2,700.00
535	221	3003	9/10/13	9/17/13	6	¤ 800.00
536	222	3675	2/20/13	2/27/13	8	¤ 2,500.00
537	210	3611	8/4/13	8/11/13	10	¤ 2,000.00
540	211	3222	4/10/14	4/17/14	8	¤ 1,600.00
541	228	3628	5/20/13	5/27/13	6	¤ 900.00
545	203	3529	3/15/14	3/29/14	7	¤ 1,500.00
548	212	3395	10/2/13	10/16/13	5	¤ 700.00
550	229	3670	11/5/13	11/12/13	8	¤ 1,300.00

PropertyID	PersonID
3002	CH2468
3003	CH2468
3003	EJ8888
3003	VW9751
3007	EJ7531
3007	RC1111
3007	TD5555
3010	EJ7531
3050	CM2222
3050	TP8642
3107	CM2222
3107	SL3333
3142	EJ7531
3142	RC1111
3142	TD5555
3179	CH2468
3190	EJ7531
3190	TD5555
3204	EJ7531
3204	RC1111
3204	TD5555
3222	CM2222
3395	CM2222
3395	SL3333
3423	EJ7531
3423	TD5555
3488	CH2468
3515	CM2222
3529	CM2222
3529	SL3333
3529	TP8642
3611	EJ7531
3611	RC1111
3628	EJ7531
3628	RC1111
3670	CH2468
3675	CM2222
3675	TP8642

PARAMETERS __GuestID Value; SELECT DISTINCTROW * FROM tblReservation AS frmGuestsAndReservations WHERE ([__GuestID] = GuestID);

SELECT [tblGuest].[GuestID] FROM tblGuest;

SELECT DISTINCTROW * FROM tblGuest;

SELECT [tblGuest].[GuestID], [tblGuest].[GuestFirstName], [tblGuest].[GuestLastName], [tblGuest].[City], [tblGuest].[StateProv], [tblGuest].[Phone], [tblReservation].[StartDate], [tblReservation].[EndDate], [tblReservation].[People], [tblReservation].[RentalRate], [tblProperty].[PropertyName] FROM tblProperty INNER JOIN (tblGuest INNER JOIN tblReservation ON tblGuest.GuestID=tblReservation.GuestID) ON tblProperty.PropertyID=tblReservation.PropertyID;

SELECT tblProperty.PropertyID, tblProperty.PropertyName, tblProperty.Location, tblProperty.Country, tblProperty.NightlyRate, tblProperty.Bedrooms, tblProperty.Sleeps, tblProperty.PropertyType FROM tblProperty WHERE (((tblProperty.PropertyName) Like "Chateau*"));

SELECT tblProperty.PropertyID, tblProperty.PropertyName, tblProperty.Location, tblProperty.Country, tblProperty.NightlyRate, tblProperty.Bedrooms, tblProperty.Sleeps, tblProperty.PropertyType, tblProperty.Description FROM tblProperty WHERE (((tblProperty.Country) Like [Type a country:] & "*")) ORDER BY tblProperty.PropertyName;

SELECT tblProperty.Location, tblProperty.Country, tblProperty.PropertyID, tblProperty.PropertyName, tblProperty.NightlyRate, tblProperty.Bedrooms, tblProperty.Sleeps, tblProperty.PropertyType FROM tblProperty WHERE (((tblProperty.Location) In (SELECT [Location] FROM [tblProperty] As Tmp GROUP BY [Location],[Country] HAVING Count(*)>1 And [Country] = [tblProperty].[Country]))) ORDER BY tblProperty.Location, tblProperty.Country;

SELECT tblGuest.GuestID, tblGuest.GuestLastName, tblGuest.GuestFirstName, tblGuest.City, tblGuest.Phone FROM tblGuest ORDER BY tblGuest.GuestLastName, tblGuest.GuestFirstName;

SELECT tblGuest.GuestID, tblGuest.GuestFirstName, tblGuest.GuestLastName, tblGuest.Address, tblGuest.City, tblGuest.StateProv, tblGuest.PostalCode, tblGuest.Country, tblGuest.Phone, tblReservation.ReservationID, tblReservation.PropertyID, tblReservation.StartDate, tblReservation.EndDate, tblReservation.People, tblReservation.RentalRate FROM tblGuest INNER JOIN tblReservation ON tblGuest.GuestID = tblReservation.GuestID;

SELECT tblGuest.GuestFirstName, tblGuest.GuestLastName, tblGuest.City, tblGuest.Phone FROM tblGuest LEFT JOIN tblReservation ON tblGuest.[GuestID] = tblReservation.[GuestID] WHERE (((tblReservation.GuestID) Is Null));

SELECT tblGuest.GuestLastName, tblGuest.City, tblGuest.StateProv, tblReservation.ReservationID, tblReservation.StartDate, tblReservation.EndDate FROM tblGuest INNER JOIN tblReservation ON tblGuest.GuestID = tblReservation.GuestID ORDER BY tblReservation.StartDate;

SELECT tblGuest.GuestLastName, tblGuest.City, tblReservation.ReservationID, tblReservation.People, tblReservation.StartDate, tblReservation.EndDate FROM tblGuest INNER JOIN tblReservation ON tblGuest.GuestID = tblReservation.GuestID WHERE (((tblGuest.StateProv)="IL")) ORDER BY tblGuest.City;

SELECT tblProperty.PropertyName, tblProperty.Location, tblProperty.Country, tblProperty.NightlyRate, tblProperty.PropertyType FROM tblProperty ORDER BY tblProperty.NightlyRate;

SELECT tblProperty.PropertyName, tblProperty.Country, tblGuest.StateProv, tblReservation.StartDate, tblReservation.EndDate, tblReservation.People FROM tblProperty INNER JOIN (tblGuest INNER JOIN tblReservation ON tblGuest.GuestID = tblReservation.GuestID) ON tblProperty.PropertyID = tblReservation.PropertyID ORDER BY tblProperty.PropertyName, tblGuest.StateProv, tblReservation.StartDate;

SELECT Min(tblReservation.RentalRate) AS LowestRate, Avg(tblReservation.RentalRate) AS AverageRate, Max(tblReservation.RentalRate) AS HighestRate FROM tblReservation;

SELECT tblProperty.Country, Min(tblReservation.RentalRate) AS LowestRate, Avg(tblReservation.RentalRate) AS AverageRate, Max(tblReservation.RentalRate) AS HighestRate FROM tblProperty INNER JOIN tblReservation ON tblProperty.PropertyID = tblReservation.PropertyID GROUP BY tblProperty.Country;

SELECT tblReservation.ReservationID, tblReservation.StartDate, tblReservation.EndDate, tblProperty.PropertyID, tblProperty.PropertyName, tblReservation.People, tblReservation.RentalRate, [RentalRate]/[People] AS CostPerPerson FROM tblProperty INNER JOIN tblReservation ON tblProperty.PropertyID = tblReservation.PropertyID ORDER BY [RentalRate]/[People] DESC;

Access3/Case5/Always.accdb

AccessPlanID	AccessPlan	PlanMonthlyCost	PlanAnnualCost	PlanSetupFee
1	Dial-up limited	¤ 9.95
2	Dial-up no e-mail	¤ 14.95	¤ 149.50
3	Dial-up unlimited	¤ 19.95	¤ 199.50
4	DSL pro	¤ 29.95		¤ 69.00
5	DSL turbo	¤ 49.95		¤ 69.00
6	DSL business	¤ 89.95		¤ 129.00
7	Wireless city economy	¤ 39.95		¤ 99.00
8	Wireless city basic	¤ 49.95		¤ 99.00
9	Wireless city advanced	¤ 64.95		¤ 109.00
10	Wireless city business	¤ 99.95		¤ 199.00
11	Wireless rural basic	¤ 59.95		¤ 109.00

CustomerAcctNum	CompanyName	FirstName	LastName	Address	City	State	Zip	Phone	AccessPlanID	NextBillingDate	Email	UserID	Password
002-00001		Student	Name	349 Grant St	Drayton	MI	49853	9998988979	7	9/13/13	[email protected]	onyx	xyno
002-00004		Hava	Jukic	11 Lombard St	Drayton	MI	49853	9968975916	3	9/5/04	[email protected]	Panda	Adnap
002-00200		William	Walker	10 Alder Dr	Brunson	MI	49484	9965311886	3	9/5/13	[email protected]	rhino	onihr
002-00300		Xinyan	Jiang	328 Leonard St	Brunson	MI	49484	9976810191	3	9/5/13	[email protected]	lion	noil
002-13579		Amy	Zainea	2800 Blue Star Dr	Blade	MI	49421	9994530976	8	9/13/13	[email protected]	Tiger	regit
002-20103		Jorge	Suarez	9 Dove Blvd	Blade	MI	49421	9997840883	4	9/13/13	[email protected]	jaguar	raugaj
002-33343		Rosalinda	Ramirez	7485 Cottonwood Dr	Brunson	MI	49484	9974565815	4	9/13/13	[email protected]	llama	amall
002-59547		Jacob	Koppelmann	1901 Baldwin St	Blade	MI	49421	9994752955	7	9/13/13	[email protected]	lynx	xnyl
002-88394		Khue	Do	1007 Main St	Drayton	MI	49853	9999750104	7	9/13/13	[email protected]	cobra	arboc
002-92983		Marcia	Crane	90 Lincoln Ave	Grand Haven	MI	49416	9972431241	8	9/13/13	[email protected]	cygnet	tengyc
002-93090		Mayra	Uzarski	400 Beebe Ave	Blade	MI	49421	9996775349	4	9/13/13	[email protected]	TreeFrog	gorfeert
081-00002	Michelle's Boutique	Michelle	Bousu	25 S Division St	Grand Haven	MI	49417	9978966978	6	10/10/13	[email protected]	crater	retarc
081-00018	Visser Insurance	Brad	Visser	427 Grant St	Drayton	MI	49853	9965598652	6	10/10/13	[email protected]	eagle	elgae
081-00100	Food Gallery	Valencia	Sanchez	900 S Division St	Grand Rapids	MI	49503	9992417599	10	10/10/13	[email protected]	firebrand	dnarberi
081-00116	Styne Pizza	David	Styne	501 Monre Ave	Grand Rapids	MI	49503	9967352982	10	10/10/13	[email protected]	lizard	drazil
081-01001	Cook's Jewelers	Kristine	Cook	155 Chicago Dr	Grand Rapids	MI	49505	9996628878	6	10/10/13	[email protected]	opal	lapo
081-20228	Ayala Associates	Francisco	Ayala	90 Trumbull Ave	Drayton	MI	49853	9968375919	10	10/10/13	[email protected]	PolarBear	Raebralo
081-86410	Boats Unlimited	Edward	Petrosyan	745 W Douglas Ave	Drayton	MI	49853	9969544643	6	10/10/13	[email protected]	Leopard	drapoel

SELECT [tblAccessPlan].[AccessPlanID] FROM tblAccessPlan;

PARAMETERS __AccessPlanID Value; SELECT DISTINCTROW * FROM tblCustomer AS frmAccessPlansAndCustomers WHERE ([__AccessPlanID] = AccessPlanID);

SELECT [tblAccessPlan].[AccessPlanID] FROM tblAccessPlan;

PARAMETERS __AccessPlanID Value; SELECT DISTINCTROW * FROM tblCustomer AS frmAccessPlansAndCustomers WHERE ([__AccessPlanID] = AccessPlanID);

SELECT [tblAccessPlan].[AccessPlan], [tblAccessPlan].[AccessPlanID] FROM tblAccessPlan ORDER BY [AccessPlan];

SELECT [tblCustomer].[CustomerAcctNum] FROM tblCustomer;

SELECT DISTINCTROW * FROM tblAccessPlan;

SELECT DISTINCTROW * FROM tblCustomer;

SELECT tblAccessPlan.AccessPlan, tblAccessPlan.PlanMonthlyCost, tblCustomer.City, tblCustomer.FirstName, tblCustomer.LastName FROM tblAccessPlan INNER JOIN tblCustomer ON tblAccessPlan.AccessPlanID = tblCustomer.AccessPlanID ORDER BY tblAccessPlan.AccessPlan, tblCustomer.City, tblCustomer.LastName;

SELECT IIf(IsNull([CompanyName]),[LastName] & ", " & [FirstName],[CompanyName]) AS CustomerName, tblCustomer.CustomerAcctNum, tblCustomer.CompanyName, tblCustomer.FirstName, tblCustomer.LastName, tblCustomer.Address, tblCustomer.City, tblCustomer.State, tblCustomer.Zip, tblCustomer.Phone, tblCustomer.AccessPlanID, tblCustomer.NextBillingDate, tblCustomer.Email, tblCustomer.UserID, tblCustomer.Password FROM tblCustomer ORDER BY IIf(IsNull([CompanyName]),[LastName] & ", " & [FirstName],[CompanyName]);

TRANSFORM Count(qryPlansAndCustomers.AccessPlanID) AS CountOfAccessPlanID SELECT qryPlansAndCustomers.AccessPlan, Count(qryPlansAndCustomers.AccessPlanID) AS [Total Number of Plans] FROM qryPlansAndCustomers GROUP BY qryPlansAndCustomers.AccessPlan PIVOT qryPlansAndCustomers.City;

SELECT tblAccessPlan.AccessPlanID, tblAccessPlan.AccessPlan, tblAccessPlan.PlanMonthlyCost, tblAccessPlan.PlanAnnualCost, tblAccessPlan.PlanSetupFee, qryCustomerNames.CustomerName, qryCustomerNames.CustomerAcctNum, qryCustomerNames.CompanyName, qryCustomerNames.FirstName, qryCustomerNames.LastName, qryCustomerNames.Address, qryCustomerNames.City, qryCustomerNames.State, qryCustomerNames.Zip, qryCustomerNames.Phone, qryCustomerNames.NextBillingDate, qryCustomerNames.Email, qryCustomerNames.UserID, qryCustomerNames.Password FROM tblAccessPlan INNER JOIN qryCustomerNames ON tblAccessPlan.AccessPlanID = qryCustomerNames.AccessPlanID ORDER BY tblAccessPlan.AccessPlanID, qryCustomerNames.CustomerName;

SELECT tblAccessPlan.AccessPlanID, tblAccessPlan.AccessPlan, tblAccessPlan.PlanMonthlyCost, tblAccessPlan.PlanAnnualCost, tblAccessPlan.PlanSetupFee FROM tblAccessPlan LEFT JOIN tblCustomer ON tblAccessPlan.AccessPlanID = tblCustomer.AccessPlanID WHERE (((tblCustomer.AccessPlanID) Is Null));

Access3/Review/Ads.accdb

AdNum	AdDate	AdCost	Placed
1	7/18/13	¤ 850.00	Trade magazine
2	7/31/13	¤ 235.56	Newspaper
3	8/10/13	¤ 391.42	Flyer
4	8/10/13	¤ 375.00	Builders' show
5	8/31/13	¤ 300.00	Flyer
6	8/31/13	¤ 235.56	Newspaper

Access3/Review/Vendor.accdb

CompanyID	CompanyName	ProductType	Address	City	State	Zip	Phone	ContactFirstName	ContactLastName	InitialContactDate	CompanyComments
AND225	Anderson OnSite	Site furnishings	200 Lincoln Dr	Kalamazoo	MI	49007	2693379266	Student	Name	6/3/12	City State
BAC200	Backyard Structures	Play equipment	105 E 8th St	Holland	MI	49423	6163963989	Alan	Bastian	4/15/12
BEL273	Belmont Mulch	Organic and recycled mulch	3044 Edgewater Dr	Kalamazoo	MI	49007	2693436618	Natalie	Belmont	6/17/12
BES327	Best Paving	Pavers	780 N Main St	Rockford	MI	49341	6168666364	Shirley	Hauser	2/14/13
CHE802	Cheshire Supplies	Wholesale furniture	2129 Wolf Ave	Hudsonville	MI	49426	6166698111	Brenda	Fischer	2/14/13
DUN284	Dunn Rite Fence	Fencing	502 Market Ave	Grand Rapids	MI	49503	6164560943	Mike	Dunn	2/25/12
GEN359	Genesis Garden Center	Mulch and soil	901 Market Ave	Grand Rapids	MI	49503	6164561783	Dennis	Jung	5/7/12
GRA231	Grand Rapids Sports	Sports equipment	8022 Adams St	Grand Rapids	MI	49503	6163921403	Larry	Rudolph	5/1/13
HOL207	Holland Yard and Patio	Site furnishings	85 College Ave	Holland	MI	49424	6163554604	Pam	Garvey	3/28/12
HOL292	Holland Nursery	Plants	380 W 20th St	Holland	MI	49424	6163969330	Brenda	Ehlert	9/2/13
LAN348	Lansing Building Supply	Pavers and walls	624 Washington Ave	Lansing	MI	48901	5174824562	Ken	Gratz	10/1/12
MIC268	Michigan Surfaces	Recreational surfacing	265 W 12th St	Holland	MI	49424	6163921317	Ted	Silvers	3/1/12
MID312	Midwest Lighting	Outdoor lighting	2250 E Riverview St	Battle Creek	MI	49014	2699790700	Aimee	Gigandet	5/15/12
NAT304	National Fence Company	Ornamental fencing	3502 State St	Lansing	MI	48917	5174871114	Bruce	Dunleavy	4/1/13
PIN252	Pinnacle Wood Furnishings	Benches and tables	43 Linden Dr	Lansing	MI	48933	5174837786	Phyllis	Walstead	9/20/12
QUA331	Quarry Rock and Stone	Stone materials	105 Colgate Hill Rd	Holland	MI	49423	6163966787	Craig	Jensen	10/11/13
VAN251	VanderJagt Nursery	Plants	1812 Brandy Ln	Hudsonville	MI	49426	6166697110	Sam	Wilson	8/11/11
VEE261	Veenstra Nursery	Plants	230 Main St	Zeeland	MI	49464	6162849178	Elaine	Veenstra	3/28/11

CompanyID	CreditLine	CreditLineDate
BEL273	¤ 5,000.00	8/14/12
CHE802	¤ 3,200.00	2/14/13
GRA231	¤ 4,500.00	7/9/13
LAN348	¤ 2,750.00	10/1/12
MID312	¤ 1,500.00	5/15/12
PIN252	¤ 2,875.00	11/14/13
VEE261	¤ 1,500.00	3/28/11

InvoiceID	CompanyID	InvoiceDate	InvoiceAmt
1	AND225	10/11/13	¤ 8,345.98
2	BAC200	10/11/13	¤ 475.00
3	BEL273	11/9/13	¤ 956.30
4	CHE802	11/9/13	¤ 1,051.45
5	CHE802	10/11/13	¤ 848.84
6	DUN284	10/11/13	¤ 5,237.58
7	GEN359	10/11/13	¤ 5,657.81
8	GEN359	11/9/13	¤ 4,090.06
9	HOL207	11/9/13	¤ 1,814.97
10	HOL207	10/11/13	¤ 2,210.00
11	LAN348	10/11/13	¤ 6,000.52
12	MIC268	10/11/13	¤ 996.58
13	MIC268	11/9/13	¤ 565.90
14	PIN252	11/9/13	¤ 3,325.95
15	PIN252	10/11/13	¤ 1,516.24
16	QUA331	10/11/13	¤ 13,555.00
17	LAN348	11/9/13	¤ 5,685.14
18	VEE261	10/11/13	¤ 2,847.80
19	DUN284	11/9/13	¤ 2,124.80

InvoiceID	PaymentID	PaymentAmt
1	1	¤ 3,550.00
1	21	¤ 3,550.00
1	23	¤ 564.00
2	2	¤ 200.00
3	3	¤ 700.00
4	4	¤ 300.00
4	5	¤ 700.00
6	6	¤ 2,940.00
6	19	¤ 2,000.81
6	20	¤ 123.99
7	7	¤ 1,528.15
7	8	¤ 3,000.00
8	8	¤ 920.00
9	10	¤ 1,417.00
10	9	¤ 1,487.00
10	10	¤ 723.00
11	11	¤ 4,168.00
11	17	¤ 1,832.52
12	12	¤ 930.00
13	13	¤ 565.90
14	14	¤ 2,409.76
15	14	¤ 367.24
15	15	¤ 1,149.00
16	16	¤ 6,885.27
17	17	¤ 3,395.48
18	18	¤ 946.94

PaymentID	PaymentDate	PaymentAmt
1	10/26/13	¤ 3,550.00
2	10/26/13	¤ 200.00
3	11/22/13	¤ 700.00
4	11/22/13	¤ 300.00
5	10/26/13	¤ 700.00
6	10/26/13	¤ 2,940.00
7	10/26/13	¤ 1,528.15
8	11/22/13	¤ 3,920.00
9	11/22/13	¤ 1,487.00
10	10/26/13	¤ 2,140.00
11	10/26/13	¤ 4,168.00
12	10/26/13	¤ 930.00
13	11/22/13	¤ 565.90
14	11/22/13	¤ 2,777.00
15	10/26/13	¤ 1,149.00
16	10/26/13	¤ 6,885.27
17	11/22/13	¤ 5,228.00
18	10/26/13	¤ 946.94
19	11/22/13	¤ 2,000.81
20	12/10/13	¤ 123.99
21	11/22/13	¤ 3,550.00
23	12/10/13	¤ 564.00

ProductID	CompanyID	ProductType	Price	Unit	Color	Material	Size	WeightInLbs	DiscountOffered
5001	BAC200	Seesaw	¤ 1,394.00	Each	Yellow	Powder-coated steel	1.5 x 9 feet	112.0	false
5003	AND225	Small picnic table	¤ 830.00	Each	Green	Powder-coated steel	7 x 6 feet	299.0	false
5004	AND225	Large picnic table	¤ 990.00	Each	Blue	Powder-coated steel	9 x 6 feet	357.0	false
5006	BAC200	Spiderweb climber	¤ 1,722.00	Each	Green	Powder-coated steel	84 x 84 inches	146.0	false
5008	AND225	Small round table	¤ 575.00	Each	Red	Powder-coated steel	5 feet diameter	213.0	false
5009	AND225	Large round table	¤ 760.00	Each	Black	Powder-coated steel	6 feet diameter	257.0	false
5010	BAC200	Small play structure	¤ 4,960.00	Each	Beige-blue	Powder-coated steel	26 x 25 feet	790.0	false
5011	BAC200	Large play structure	¤ 12,750.00	Each	Beige-green	Powder-coated steel	33 x 34 feet	1890.0	false
5012	HOL207	Trash receptacle	¤ 672.00	Each	Black	Powder-coated steel	32 gallon	175.0	false
5013	HOL207	Small bench	¤ 712.00	Each	Green	Steel and cast iron	8 x 2 feet	266.0	false
5014	BAC200	Double bay swing	¤ 3,740.00	Each	Purple	Powder-coated steel	23 x 6 feet	364.0	false
5015	AND225	Large bench	¤ 825.00	Each	Green	Powder-coated steel	15 x 2 feet	273.0	false
5018	AND225	Square tree grate	¤ 390.00	Each	Black	Powder-coated steel	4 feet square	114.0	false
5020	HOL207	Small bench with back	¤ 688.00	Each	Redwood	Redwood and iron	6 x 2 feet	197.0	false
5021	HOL207	Large bench with back	¤ 763.00	Each	Redwood	Redwood and iron	8 x 2 feet	275.0	false
5030	GRA231	Basketball backstop	¤ 1,245.00	Each	White	Aluminum	3 x 4 feet	458.0	false
5031	GRA231	Tennis posts	¤ 153.00	Each	Green	Powder-coated steel	3 feet	69.0	false
5034	GRA231	Bicycle rack	¤ 486.00	Each	Blue	Powder-coated steel	5 feet	80.0	false
5100	PIN252	Small round table	¤ 551.00	Each	Wood	Teak	5 feet diameter	54.0	false
5101	PIN252	Large round table	¤ 980.00	Each	Wood	Teak	6 feet diameter	72.0	false
5105	PIN252	Side chair	¤ 250.00	Each	Wood	Teak	1.5 x 2 feet	17.0	false
5106	PIN252	Side chair with arms	¤ 425.00	Each	Wood	Teak	2 x 2 feet	24.0	false
5202	MIC268	Safety surface	¤ 9.60	Square foot	Black	Recycled rubber	22 gallon	58.0	false
5205	BES327	Matte paver	¤ 13.00	Each	Ginger	Precast concrete	1 square foot	21.0	false
5206	BES327	Granite paver	¤ 16.70	Each	Granite	Precast concrete	1 square foot	21.0	false
5207	BES327	Slate paver	¤ 19.90	Each	Charcoal	Precast concrete	1 square foot	21.0	false
5211	QUA331	Matte paver	¤ 9.65	Square foot	Blue-grey	Natural stone			false
5212	QUA331	Limestone paver	¤ 9.33	Square foot	Buff	Natural stone			false
5222	LAN348	Square paver	¤ 2.45	Square foot	Light brown	Precast concrete			false
5223	LAN348	Rectangular paver	¤ 4.05	Square foot	Light brown	Precast concrete			false
5227	LAN348	Retaining wall	¤ 11.45	Square foot	Grey	Precast concrete	1 x 1 foot		true
5228	QUA331	Wall stone	¤ 7.63	Square foot	Brown-grey	Natural stone			false
5230	BES327	Wall cap	¤ 8.50	Linear foot	White	Precast concrete	1 x 2 feet	40.0	false
5232	LAN348	Paver edging	¤ 1.95	Linear foot	Black	PVC	1 x 7 feet		false
5301	BEL273	Mulch	¤ 24.40	Cubic yard	Wood	Flat wood chips			true
5304	BEL273	Cypress mulch	¤ 20.45	Cubic yard		Shredded cypress			true
5305	GEN359	Cedar mulch	¤ 29.00	Cubic yard	Reddish	Cedar-pine blend			true
5306	GEN359	Pine mulch	¤ 23.35	Cubic yard	Dark brown	Softwoods-pine			true
5311	GEN359	Top soil	¤ 13.50	Cubic yard		Screened top soil			true
5313	GEN359	Potting soil	¤ 16.00	Cubic yard		Sifted soil and compost			true
5314	BEL273	Potting soil	¤ 16.50	Cubic yard		Sifted soil and compost			true
5315	NAT304	Chain link fencing	¤ 10.60	Linear foot	Black	Vinyl-coated chain link	3.5 feet high		true
5317	BEL273	Top soil	¤ 13.50	Cubic yard		Screen top soil			true
5322	NAT304	Short ornamental fence	¤ 48.00	Linear foot	Black	Powder-coated steel	2 feet high		true
5323	NAT304	Tall ornamental fence	¤ 72.00	Linear foot	Black	Powder-coated steel	4 feet high		true
5400	DUN284	Large pergola-round	¤ 17,830.00	Each	White	Vinyl	18 x 16 feet	2100.0	false
5401	DUN284	Large pergola-square	¤ 13,460.00	Each	White	Vinyl	18 x 16 feet	2100.0	false
5402	DUN284	Small pergola-round	¤ 9,455.00	Each	White	Vinyl	4 x 16 feet	700.0	false
5403	DUN284	Small pergola-square	¤ 7,526.00	Each	White	Vinyl	4 x 16 feet	700.0	false
5411	NAT304	Tennis court fencing	¤ 29.60	Linear foot	Black	Vinyl-coated chain link	10 feet high		true
5502	MID312	Halide lamp	¤ 312.00	Each	Bronze	Bronze	8.5 x 15 feet		false
5503	MID312	Lamp and post	¤ 1,007.00	Each	Black	Powder-coated aluminum	6 x .5 feet		false
5504	MID312	Lighting system	¤ 1,272.00	Each	Silver-grey	Powder-coated aluminum	12 feet high		false
5601	GEN359	Shade tree	¤ 250.00	Each			6-8 feet high		true
5602	HOL292	Shade tree	¤ 245.00	Each			7-9 feet high		true
5603	GEN359	Ornamental tree	¤ 135.00	Each			4-6 feet high		true
5604	HOL292	Ornamental tree	¤ 130.00	Each			6-7 feet high		true
5610	HOL292	Shrub	¤ 23.50	Each			3 gallon		true
5612	GEN359	Shrub	¤ 24.00	Each			3 gallon		true
5620	HOL292	Perennial	¤ 6.30	Each			1 gallon		true
5622	GEN359	Perennial	¤ 6.40	Each			1 gallon		true
5625	HOL292	Groundcover	¤ 3.25	Each			4 quart		true
5628	GEN359	Groundcover	¤ 3.25	Each			4 quart		true
5630	GEN359	Annual	¤ 2.70	Each			1 quart		true

PARAMETERS __CompanyID Value; SELECT DISTINCTROW * FROM tblProduct AS frmCompaniesAndProducts WHERE ([__CompanyID] = CompanyID);

SELECT [tblCompany].[CompanyID], [tblCompany].[CompanyName] FROM tblCompany;

PARAMETERS __CompanyID Text; SELECT DISTINCTROW * FROM tblProduct AS frmCompaniesWithProducts WHERE ([__CompanyID] = CompanyID);

SELECT DISTINCTROW * FROM tblCompany;

SELECT DISTINCTROW * FROM tblProduct;

SELECT DISTINCTROW * FROM tblCompany;

SELECT [tblCompany].[CompanyName], [tblCompany].[ContactFirstName], [tblCompany].[ContactLastName], [tblCompany].[City], [tblProduct].[ProductID], [tblProduct].[ProductType], [tblProduct].[Price], [tblProduct].[Unit], [tblCompany].[CompanyID] FROM tblCompany INNER JOIN tblProduct ON tblCompany.CompanyID=tblProduct.CompanyID;

SELECT tblProduct.ProductType, tblProduct.Price, tblProduct.Color, tblProduct.DiscountOffered FROM tblProduct WHERE (((tblProduct.Color) Like [Type the color:] & "*")) ORDER BY tblProduct.Price;

SELECT [ContactFirstName] & " " & [ContactLastName] AS ContactName, tblCompany.CompanyName, tblCompany.City, tblCompany.Phone FROM tblCompany ORDER BY tblCompany.CompanyName;

SELECT tblCompany.CompanyName, tblCompany.ProductType, tblCompany.ContactFirstName, tblCompany.ContactLastName, tblCompany.Phone FROM tblCompany ORDER BY tblCompany.CompanyName;

SELECT tblCompany.ProductType, tblCompany.CompanyName, tblProduct.ProductType, tblProduct.Price, tblProduct.Unit FROM tblCompany INNER JOIN tblProduct ON tblCompany.CompanyID = tblProduct.CompanyID ORDER BY tblCompany.ProductType, tblCompany.CompanyName, tblProduct.ProductType;

SELECT tblCompany.CompanyName, tblCompany.ContactFirstName, tblCompany.ContactLastName, tblCompany.Phone, tblCompany.InitialContactDate FROM tblCompany ORDER BY tblCompany.CompanyName;

SELECT tblProduct.ProductID, tblProduct.ProductType, tblProduct.Price, tblProduct.Unit, tblProduct.WeightInLbs, tblProduct.DiscountOffered FROM tblProduct WHERE (((tblProduct.Price)>5000) AND ((tblProduct.DiscountOffered)=No));

SELECT tblCompany.CompanyName, tblProduct.ProductType, tblProduct.Price, tblProduct.Unit, tblProduct.DiscountOffered FROM tblCompany INNER JOIN tblProduct ON tblCompany.CompanyID = tblProduct.CompanyID WHERE (((tblCompany.City)="Holland")) ORDER BY tblProduct.Price DESC;

SELECT tblCompany.CompanyID, tblCompany.CompanyName, tblCompany.ProductType, tblCompany.Address, tblCompany.City, tblCompany.State, tblCompany.Zip, tblCompany.Phone, tblCompany.ContactFirstName, tblCompany.ContactLastName, tblCompany.InitialContactDate FROM tblCompany WHERE (((tblCompany.City) Like "H*")) ORDER BY tblCompany.CompanyName;

SELECT Min(tblProduct.Price) AS LowestPrice, Max(tblProduct.Price) AS HighestPrice, Avg(tblProduct.Price) AS AveragePrice FROM tblProduct;

SELECT tblCompany.CompanyName, Min(tblProduct.Price) AS LowestPrice, Max(tblProduct.Price) AS HighestPrice, Avg(tblProduct.Price) AS AveragePrice FROM tblCompany INNER JOIN tblProduct ON tblCompany.CompanyID = tblProduct.CompanyID GROUP BY tblCompany.CompanyName;

SELECT tblProduct.ProductID, tblProduct.ProductType, tblProduct.Price, [Price]*0.05 AS Discount FROM tblProduct WHERE (((tblProduct.DiscountOffered)=Yes)) ORDER BY [Price]*0.05 DESC;

SELECT tblProduct.ProductID, tblProduct.CompanyID, tblProduct.ProductType, tblProduct.Price, tblProduct.Unit, tblProduct.Color, tblProduct.Material, tblProduct.Size, tblProduct.WeightInLbs, tblProduct.DiscountOffered FROM tblProduct WHERE (((tblProduct.Color) In ("Black","White","Grey"))) ORDER BY tblProduct.Price DESC;

SELECT TOP 25 PERCENT tblProduct.ProductID, tblProduct.ProductType, tblProduct.Price, [Price]*0.05 AS Discount FROM tblProduct WHERE (((tblProduct.DiscountOffered)=Yes)) ORDER BY [Price]*0.05 DESC;

Access3/Tutorial/Holland.accdb

EmployeeID	InvoiceNum
101	2612
101	2614
102	2061
102	2063
102	2111
102	2113
103	2611
103	2612
103	2613
103	2614
104	2611
104	2612
104	2613
104	2614
105	2612
105	2613
106	2061
106	2062
106	2063
106	2064
106	2111
106	2112
106	2113
106	2114
107	2061
107	2062
107	2063
107	2064
107	2111
107	2112
107	2113
107	2114
108	2061
108	2063
108	2064
108	2111
108	2112
108	2113
112	2064
114	2611
114	2613
115	2062
115	2063
115	2112
115	2113
116	2114
119	2611
119	2612
119	2613
119	2614

CustomerID	BillingCompany	BillingAddress	BillingCity	BillingState	BillingZip
11020	Dental Partners Inc	18 Herman Blvd	Battle Creek	MI	49014
11038	M. Grant Investment Company	440 Fulton Ave	Lansing	MI	48901
11065	Town of Holland	100 Lucas St	Holland	MI	49423
11068	Grand Rapids City Agencies	225 College Ave	Grand Rapids	MI	49504
11079	City of East Grand Rapids	3579 Jefferson St	East Grand Rapids	MI	49506
11087	Walker Investment Company	1780 S Main St	Battle Creek	MI	49014

ContractNum	CustomerID	ContractAmt	SigningDate	StartDate	ContractType
3011	11001	¤ 4,000.00	2/9/13	2/22/13	Residential landscape plan
3012	11027	¤ 300.00	2/18/13	2/19/13	Consultation for backyard, residential
3015	11005	¤ 1,500.00	3/1/13	3/18/13	Schematic plan for backyard, residential
3017	11012	¤ 2,250.00	3/1/13	3/15/13	Peer plan review for town
3020	11055	¤ 6,500.00	2/19/13	4/2/13	Landscape design for restaurant
3021	11040	¤ 28,000.00	5/3/13	8/17/13	Landscape plans for multifamily housing site
3022	11043	¤ 22,000.00	4/14/13	6/29/13	Landscape design for two entrances
3023	11070	¤ 39,000.00	3/22/13	7/12/13	Renovation of large multifamily housing open space
3025	11083	¤ 15,500.00	3/25/13	7/12/13	Landscape renovation for plaza
3026	11038	¤ 165,000.00	3/11/13	8/18/13	Landscape plans for large-scale housing development
3027	11008	¤ 1,250.00	4/7/13	5/28/13	Schematic plan for backyard, residential
3030	11042	¤ 5,250.00	6/11/13	7/29/13	Landscape plan for residential site
3031	11070	¤ 48,500.00	6/3/13	10/8/13	Landscape design for multifamily housing
3032	11014	¤ 6,500.00	6/22/13	8/3/13	Residential landscape plan
3033	11005	¤ 2,250.00	7/8/13	8/17/13	Residential landscape plan for front yard
3034	11088	¤ 8,500.00	5/28/13	6/14/13	Neighborhood park, nonprofit developer
3035	11088	¤ 10,500.00	5/28/13	6/9/13	Neighborhood park, nonprofit developer
3037	11045	¤ 68,000.00	6/22/13	8/11/13	Renovation of an existing city park
3038	11045	¤ 34,000.00	6/22/13	7/13/13	Renovation of an existing playground at a city park
3040	11068	¤ 38,500.00	7/27/13	8/20/13	Renovation of open space around public housing site
3043	11083	¤ 19,000.00	8/11/13	10/6/13	Landscape design of open space around new campus building
3044	11075	¤ 25,500.00	8/19/13	11/30/13	Handicap accessibility upgrades to public housing site
3045	11064	¤ 25,000.00	8/20/13	3/17/14	Open space corridor planning at new transit station
3046	11048	¤ 300.00	9/2/13	9/7/13	Consultation for backyard, residential
3048	11030	¤ 7,500.00	9/23/13	10/19/13	Landscape design for restaurant site
3050	11015	¤ 1,000.00	8/27/13	9/22/13	Schematic plan for backyard, residential
3051	11032	¤ 18,000.00	10/5/13	1/24/14	Site layout and landscape design for residential site
3053	11049	¤ 375.00	9/15/13	9/20/13	Consultation for front yard, residential
3056	11075	¤ 32,500.00	9/30/13	2/10/14	Handicap accessibility upgrades to public housing site
3057	11075	¤ 15,500.00	9/30/13	10/27/13	Handicap accessibility upgrades to public housing site
3060	11005	¤ 4,000.00	11/30/13	12/2/13	Front walk and drive design, residential
3061	11070	¤ 30,800.00	12/28/13	3/11/14	Landscape design for multifamily housing development
3062	11053	¤ 20,000.00	1/11/14	2/16/14	Site layout and landscape design for residential site
3063	11015	¤ 1,750.00	1/20/14	1/21/14	Landscape plan for front yard, residential
3065	11079	¤ 38,000.00	2/3/14	4/6/14	Renovation of playground at elementary school
3068	11049	¤ 4,500.00	2/7/14	3/17/14	Front walk and drive design, residential
3069	11030	¤ 15,750.00	2/24/14	3/25/14	Landscape design for restaurant site
3070	11079	¤ 35,000.00	3/3/14	3/16/14	Renovation of playground at elementary school
3071	11059	¤ 37,000.00	3/11/14	5/19/14	Landscape design for affordable housing site
3072	11059	¤ 46,000.00	3/11/14	6/28/14	Landscape design for affordable housing site
3073	11072	¤ 205,000.00	5/5/14	6/3/14	Landscape plans for large-scale housing development
3075	11067	¤ 16,500.00	4/8/14	5/13/14	Design of a playground for a daycare center
3076	11065	¤ 5,000.00	4/8/14	5/9/14	Peer plan review for town
3077	11058	¤ 6,500.00	5/17/14	6/28/14	Schematic landscape design for senior center
3078	11020	¤ 13,750.00	4/25/14	5/24/14	Landscape design for business property
3080	11030	¤ 22,800.00	4/14/14	6/16/14	Landscape design and site plan for restaurant site
3081	11071	¤ 21,000.00	5/10/14	7/7/14	Landscape design for business property
3082	11060	¤ 5,000.00	6/1/14	6/23/14	Residential landscape plan
3083	11084	¤ 1,000.00	9/27/14	5/23/15	Schematic plan for backyard, residential
3085	11068	¤ 52,500.00	6/23/14	9/14/14	Open space and playground design for public housing site
3086	11054	¤ 17,250.00	7/11/14	7/27/14	Landscape design for commercial property
3090	11079	¤ 25,500.00	6/14/14	9/19/14	Renovation of playground at elementary school
3093	11085	¤ 5,000.00	7/25/14	8/3/14	Residential landscape plan
3094	11015	¤ 5,000.00	7/26/14	8/9/14	Front walk and drive design, residential
3095	11045	¤ 14,500.00	6/29/14	8/16/14	Handicap accessibility upgrades to public housing site
3097	11080	¤ 8,500.00	8/2/14	8/23/14	Landscape design for renovation of restaurant front entry
3098	11065	¤ 35,000.00	7/15/14	9/9/14	Design of a small town park
3099	11067	¤ 6,500.00	7/25/14	9/21/14	Schematic landscape design for daycare center
3100	11031	¤ 10,500.00	8/8/14	10/6/14	Schematic landscape design for daycare center
3103	11087	¤ 252,000.00	8/17/14	12/6/14	Landscape plans for large-scale housing development
3104	11073	¤ 6,500.00	7/28/14	9/8/14	Front walk and drive design, residential
3105	11084	¤ 5,000.00	8/24/14	10/6/14	Residential landscape plan
3108	11045	¤ 50,000.00	8/2/14	10/18/14	Design of a small city park
3109	11059	¤ 41,000.00	8/18/14	1/12/15	Landscape design for affordable housing site
3110	11079	¤ 38,000.00	8/25/14	11/16/14	Renovation of playground at elementary school
3201	11014	¤ 4,000.00	8/11/14	6/4/15	Front walk and drive design, residential

CustomerID	Company	FirstName	LastName	Phone	Address	City	State	Zip	EmailAddress	Comments	CustomerType
11001		Sabrina	Fernandez	6168663901	49 Blackstone Dr	Rockford	MI	49341	[email protected]	An opportunity to gain a foothold in a new development in a growing community.	Residential
11004		Student	Name	6168669111	2807 Fairview Dr	Rockford	MI	49341	[email protected]		Residential
11005		Derek	Schwimmer	6163920622	102 Pineview Rd	Holland	MI	49423	[email protected]		Residential
11008		Melissa	Caputo	2699851122	302 Main St	Saint Joseph	MI	49085	[email protected]	We obtained approval to use this plan for other properties and to use her as a reference.	Residential
11012	Grand Rapids Engineering Dept	Anthony	Rodriguez	6164549801	225 Summer St	Grand Rapids	MI	49503	[email protected]		Government
11014		Amol	Mehta	6163961972	54 Lakeshore Ave	Holland	MI	49424	[email protected]		Residential
11015		John	Weiss	6166377783	456 Winchester St	South Haven	MI	49090	[email protected]		Residential
11020	Battle Creek Dental Partners	Harry	Billings	2699630808	587 Longmeadow Rd	Battle Creek	MI	49014	[email protected]	Contact again in one year to bid on next phase.	Commercial
11027		Karen	O'Brien	5174839244	38 Langley Rd	Lansing	MI	48933	[email protected]		Residential
11030	Finn's on the Waterfront	Devin	Finnerty	6163931228	78 E 8th St	Holland	MI	49423	[email protected]	They have other specialty restaurants that need similar design and site planning work.	Commercial
11031	Happy Haven Day Care	Kathy	Rowe	6168424603	29 Graham's Ln	Grand Haven	MI	49417	[email protected]		Commercial
11032		Pam	Wallner	2698571276	80 Cedar St	Saugatuck	MI	49453	[email protected]	An established, upscale neighborhood with many properties in need of estensive landscaping improvements.	Residential
11038	M. Grant Investment Company	Alex	Engber	5174821400	505 Washington Ave	Lansing	MI	48901	[email protected]		Commercial
11040	RiverView Development Company	Susan	Darcy	6169880777	144 E Tower Ave	Grand Rapids	MI	49503	[email protected]		Public Housing
11042		Michael	Ingram	2693379208	10 Lincoln Dr	Kalamazoo	MI	49007	[email protected]		Residential
11043	Monroe State College	Rachel	Kirk	6169881320	40 Monroe St	Grand Rapids	MI	49503	[email protected]		Education
11045	Kalamazoo Neighborhood Development	James	Blackhawk	2693437509	3047 Millbrook Ave	Kalamazoo	MI	49007	[email protected]		Nonprofit
11048		Olivia	Pappas	6166376591	4 N Orchard St	South Haven	MI	49090	[email protected]		Residential
11049		Claire	Boucher	2699832255	828 Turner St	Saint Joseph	MI	49085	[email protected]		Residential
11053		Hwan	Tang	6163968401	283 Cottrell St	Holland	MI	49423	[email protected]		Residential
11054	Gilded Goose Gift Shop	Taylor	Wilson	6163553989	258 Briar Ln	Holland	MI	49424	[email protected]		Commercial
11055	Fox and Hound Grille	Steve	Gorski	2699792004	1440 Beadle Lake Rd	Battle Creek	MI	49014	[email protected]		Commercial
11058	Cherrywood Senior Center	Dan	Lewis	2698571890	77 Forest Hill Rd	Saugatuck	MI	49453	[email protected]		Nonprofit
11059	G. R. Neighborhood Development Corp	Matthew	Fraser	6163920015	8045 Jefferson Ave	Grand Rapids	MI	49503	[email protected]		Park
11060		Jerome	Smith	6169493862	75 Hillcrest St	East Grand Rapids	MI	49506	[email protected]		Residential
11064	Northwest Transit Station	Henry	Goldberg	5174874700	3572 Clinton Ave	Lansing	MI	48917	[email protected]		Government
11065	Town of Holland	Amber	Ward	6163930403	24 Prospect St	Holland	MI	49423	[email protected]		Government
11067	Blossom Day Care Center	Christina	Garrett	2699851992	333 Empire Ave	Saint Joseph	MI	49085	[email protected]		Commercial
11068	Grand Rapids Housing Authority	Jessica	Ropiak	6164544002	5230 Fulton St	Grand Rapids	MI	49504	[email protected]		Public Housing
11070	Legacy Companies, LTD	Michael	Faraci	6164567093	250 Market Ave	Grand Rapids	MI	49503	[email protected]		Commercial
11071	Blue Star Mini Golf	Vanetta	Walker	2698574424	82 Mason St	Saugatuck	MI	49453	[email protected]		Commercial
11072	Sierra Investment Company	Rodrigo	Valencia	2693378200	5049 Red Arrow Hwy	Kalamazoo	MI	49007	[email protected]		Commercial
11073		Connor	Riley	2699831116	11 Pleasant St	Saint Joseph	MI	49085	[email protected]		Residential
11075	Kim Architects, Inc.	An	Kim	5174829932	1015 Williamston Rd	Lansing	MI	48933	[email protected]		Commercial
11079	Dept. of Neighborhood Development	Sarah	Russell	6169403380	722 Beechwood Dr	East Grand Rapids	MI	49506	[email protected]		Nonprofit
11080	Three Tulips Café	Jan	Van Dousen	6163924629	249 W 11th St	Holland	MI	49424	[email protected]		Commercial
11083	Hopedale State College	John	Williams	5173370990	1003 Albert Ave	East Lansing	MI	48823	[email protected]		Education
11084		Cathy	DeSantis	6168664882	78 Spring St	Rockford	MI	49341	[email protected]		Residential
11085		Edward	Phillips	6166375408	39 Water St	South Haven	MI	49090	[email protected]		Residential
11087	Walker Investment Company	Nancy	Belanger	2699830190	1752 S Main St	Battle Creek	MI	49014	[email protected]		Commercial
11088	Weston Community Parks Foundation	Sam	Kervin	6164543327	333 Pearl St	Grand Rapids	MI	49503	[email protected]		Park
11090	Lily's Boutique	Lily	Shaw	6168425361	751 E Maple St	Grand Haven	MI	49417	[email protected]		Commercial

EmployeeID	FirstName	LastName	Position	SupervisorID
101	Lynne	Perkins	Site manager	103
102	Timothy	Ering	Artist	106
103	Cynthia	Kadohata	Team leader
104	Linda	Park	Designer	103
105	Richard	Peck	Liaison	103
106	Christopher	Curtis	Team leader
107	Louis	Sacher	Planner	106
108	Karen	Hesse	Designer	106
112	Karen	Cushman	Site manager	106
114	Sharon	Creech	Artist	103
115	Lois	Lowry	Liaison	106
116	Cynthia	Rylant	Site manager	106
117	Jerry	Spinelli	Site manager	103
119	Paul	Fleischman	Planner	103

InvoiceNum	ContractNum	InvoiceDate	InvoiceItem	InvoiceAmt	InvoicePaid
2011	3011	3/23/13	Schematic Plan	¤ 1,500.00	true
2012	3011	5/10/13	Planting Plan	¤ 2,500.00	true
2021	3012	2/25/13	Consultation	¤ 300.00	true
2031	3020	4/19/13	Schematic Plan	¤ 1,500.00	true
2032	3020	8/19/13	Landscape Planting	¤ 2,000.00	true
2033	3020	9/20/13	Lighting Plan	¤ 2,000.00	true
2034	3020	10/20/13	Permitting	¤ 1,000.00	true
2041	3015	4/1/13	Schematic Plan	¤ 1,500.00	true
2051	3017	4/1/13	Peer Plan Review	¤ 2,250.00	true
2061	3026	3/11/14	Schematic Open Space Plan	¤ 35,000.00	true
2062	3026	9/12/14	Permitting	¤ 10,000.00	false
2063	3026	7/11/15	Construction Documents	¤ 70,000.00	false
2064	3026	4/4/17	Construction Admin. & Observation	¤ 50,000.00	false
2071	3023	11/22/13	Schematic Open Space Plan	¤ 9,000.00	true
2072	3023	7/22/14	Planting Plan	¤ 20,000.00	true
2073	3023	9/21/15	Construction Observation	¤ 10,000.00	false
2081	3025	7/23/13	Schematic Plan	¤ 4,000.00	true
2082	3025	12/20/13	Construction Documents	¤ 8,000.00	true
2083	3025	6/24/14	Construction Observation	¤ 3,500.00	true
2091	3027	6/7/13	Schematic Plan	¤ 1,250.00	true
2101	3022	7/14/13	Schematic Plan	¤ 4,500.00	true
2102	3022	11/15/13	Construction Documents	¤ 12,000.00	true
2103	3022	7/14/14	Construction Observation	¤ 5,500.00	true
2111	3021	10/12/13	Schematic Landscape Plan	¤ 4,500.00	true
2112	3021	11/12/13	Permitting	¤ 3,000.00	true
2113	3021	9/2/14	Construction Documents	¤ 12,000.00	false
2114	3021	9/5/15	Construction Observation	¤ 8,500.00	false
2121	3034	9/28/13	Community Meetings	¤ 3,500.00	true
2122	3034	12/28/13	Schematic Design	¤ 5,500.00	true
2131	3035	10/18/13	Community Meetings	¤ 4,500.00	true
2132	3035	3/28/14	Schematic Design	¤ 6,000.00	true
2141	3031	2/3/14	Schematic Open Space Plan	¤ 7,500.00	true
2142	3031	6/3/14	Planting Plans	¤ 16,000.00	true
2143	3031	10/3/14	Layout Plans	¤ 16,000.00	false
2144	3031	8/3/15	Construction Observation	¤ 9,000.00	false
2151	3030	8/11/13	Schematic Plan	¤ 1,500.00	true
2152	3030	10/15/13	Construction Documents	¤ 2,250.00	true
2153	3030	3/30/14	Construction Observation	¤ 1,500.00	true
2161	3032	9/10/13	Schematic Plan	¤ 2,500.00	true
2162	3032	10/12/13	Planting Plan	¤ 2,500.00	true
2163	3032	11/12/13	Construction Details	¤ 1,500.00	true
2171	3037	2/22/14	Community Meetings & Coordination	¤ 8,000.00	true
2172	3037	6/22/14	Schematic Plan	¤ 15,000.00	true
2173	3037	2/24/15	Construction Documents	¤ 30,000.00	false
2174	3037	3/20/16	Construction Admin. & Observation	¤ 15,000.00	false
2181	3038	10/22/13	Community Meetings	¤ 3,000.00	true
2182	3038	12/22/13	Schematic Plan	¤ 8,000.00	true
2183	3038	6/23/14	Construction Documents	¤ 15,000.00	true
2184	3038	4/27/15	Construction Admin. & Observation	¤ 8,000.00	false
2191	3033	9/29/13	Schematic Plan	¤ 2,250.00	true
2201	3040	1/12/14	Schematic Landscape Plan	¤ 8,500.00	true
2202	3040	8/26/14	Construction Documents	¤ 18,000.00	false
2203	3040	9/17/15	Construction Admin. & Observation	¤ 12,000.00	false
2211	3043	1/11/14	Schematic Landscape Plan	¤ 5,500.00	true
2212	3043	6/20/14	Construction Documents	¤ 10,500.00	true
2213	3043	3/30/15	Construction Observation	¤ 3,000.00	false
2221	3044	6/17/14	Construction Documents	¤ 15,500.00	true
2222	3044	5/1/15	Construction Admin. & Observation	¤ 10,000.00	false
2231	3045	12/22/14	Schematic Plans	¤ 18,000.00	false
2232	3045	8/15/15	Community Meetings	¤ 7,000.00	false
2241	3050	10/18/13	Schematic Plan	¤ 1,000.00	true
2251	3046	9/9/13	Consultation	¤ 300.00	true
2261	3053	9/22/13	Consultation	¤ 375.00	true
2271	3048	12/28/13	Schematic Design	¤ 1,500.00	true
2272	3048	2/28/14	Permitting	¤ 1,000.00	true
2273	3048	6/30/14	Landscape Planting & Lighting Plan	¤ 3,500.00	true
2274	3048	9/26/14	Construction Observation	¤ 1,500.00	false
2281	3056	9/30/14	Construction Documents	¤ 20,000.00	false
2282	3056	8/14/15	Construction Admin. & Observation	¤ 12,500.00	false
2291	3057	1/31/14	Schematic Design	¤ 4,000.00	true
2292	3057	6/30/14	Construction Documents	¤ 8,000.00	true
2293	3057	3/30/15	Construction Observation	¤ 3,500.00	false
2301	3051	3/7/14	Schematic Landscape Plan	¤ 5,500.00	true
2302	3051	7/7/14	Construction Documents	¤ 9,000.00	true
2303	3051	5/3/15	Construction Observation	¤ 3,500.00	false
2311	3060	12/21/13	Schematic Plan	¤ 1,500.00	true
2312	3060	5/27/14	Planting & Materials Plan	¤ 2,500.00	true
2321	3061	6/28/14	Schematic Open Space Plan	¤ 6,000.00	true
2322	3061	10/26/14	Permitting	¤ 3,000.00	false
2323	3061	4/25/15	Construction Documents	¤ 15,000.00	false
2324	3061	6/27/16	Construction Observation	¤ 6,800.00	false
2331	3062	6/10/14	Schematic Landscape Plan	¤ 6,000.00	true
2332	3062	11/14/14	Construction Documents	¤ 10,000.00	false
2333	3062	5/11/15	Construction Observation	¤ 4,000.00	false
2341	3063	3/21/14	Schematic Plan	¤ 1,750.00	true
2351	3065	6/3/14	Community Meetings	¤ 4,000.00	true
2352	3065	12/5/14	Schematic Plan	¤ 9,500.00	false
2353	3065	8/3/15	Construction Documents	¤ 15,000.00	false
2354	3065	8/1/16	Construction Admin. & Observation	¤ 9,500.00	false
2361	3068	4/21/14	Schematic Plan	¤ 1,500.00	true
2362	3068	7/7/14	Planting & Materials Plan	¤ 3,000.00	true
2371	3069	6/9/14	Schematic Plan	¤ 3,750.00	true
2372	3069	7/29/14	Permitting	¤ 2,500.00	true
2373	3069	9/23/14	Planting Plan	¤ 7,000.00	false
2374	3069	10/24/14	Lighting Plans	¤ 2,500.00	false
2381	3070	9/6/14	Community Meetings	¤ 3,500.00	false
2382	3070	1/3/14	Schematic Plan	¤ 8,500.00	false
2383	3070	11/5/15	Construction Documents	¤ 14,000.00	false
2384	3070	7/8/16	Construction Observation	¤ 9,000.00	false
2391	3071	12/9/14	Schematic Plan	¤ 8,500.00	false
2392	3071	3/12/15	Community Meetings & Permitting	¤ 4,500.00	false
2393	3071	3/11/15	Construction Documents	¤ 14,000.00	false
2394	3071	3/27/17	Construction Observation	¤ 10,000.00	false
2401	3072	11/10/14	Schematic Plan	¤ 12,000.00	false
2402	3072	9/13/15	Construction Documents	¤ 30,000.00	false
2403	3072	9/26/16	Construction Admin. & Observation	¤ 15,000.00	false
2411	3075	7/8/14	Schematic Plan	¤ 5,000.00	true
2412	3075	4/9/15	Construction Documents	¤ 8,000.00	false
2413	3075	9/27/15	Construction Observation	¤ 3,500.00	false
2421	3076	6/8/14	Peer Plan Review	¤ 5,000.00	true
2431	3080	8/3/14	Schematic Design	¤ 6,000.00	true
2432	3080	9/14/14	Permitting	¤ 1,500.00	false
2433	3080	2/15/15	Planting Plan	¤ 13,000.00	false
2434	3080	8/15/15	Lighting Plans	¤ 2,300.00	false
2441	3078	6/24/14	Schematic Landscape Plan	¤ 3,750.00	true
2442	3078	10/24/14	Construction Documents	¤ 7,500.00	false
2443	3078	6/25/15	Construction Observation	¤ 2,500.00	false
2451	3073	10/5/14	Schematic Open Space & Landscape Plan	¤ 45,000.00	false
2452	3073	5/6/16	Community Meetings & Permitting	¤ 25,000.00	false
2453	3073	5/5/17	Construction Documents	¤ 85,000.00	false
2454	3073	8/18/18	Construction Admin. & Observation	¤ 50,000.00	false
2461	3081	10/11/14	Schematic Landscape Plan	¤ 4,500.00	false
2462	3081	5/10/15	Construction Documents	¤ 12,000.00	false
2463	3081	10/18/15	Construction Observation	¤ 4,500.00	false
2471	3077	8/23/14	Schematic Landscape Plan	¤ 6,500.00	true
2481	3082	8/1/14	Schematic Plan	¤ 2,500.00	true
2482	3082	10/21/14	Planting Plan	¤ 2,500.00	false
2491	3090	10/14/14	Community Meetings	¤ 2,000.00	false
2492	3090	2/16/15	Schematic Plan	¤ 5,500.00	false
2493	3090	11/16/15	Construction Documents	¤ 12,000.00	false
2494	3090	9/9/16	Construction Observation	¤ 6,000.00	false
2501	3085	2/23/15	Schematic Landscape Plan	¤ 10,500.00	false
2502	3085	10/23/15	Construction Documents	¤ 26,000.00	false
2503	3085	8/26/16	Construction Observation	¤ 16,000.00	false
2511	3095	8/25/14	Schematic Design	¤ 3,000.00	true
2512	3095	3/29/15	Construction Documents	¤ 7,000.00	false
2513	3095	4/30/16	Construction Observation	¤ 4,500.00	false
2521	3086	10/11/14	Schematic Landscape Plan	¤ 4,750.00	false
2522	3086	3/12/15	Construction Documents	¤ 8,500.00	false
2523	3086	10/4/15	Construction Observation	¤ 4,000.00	false
2531	3098	12/15/14	Schematic Landscape Plan	¤ 8,000.00	false
2532	3098	3/15/15	Community Meetings	¤ 2,000.00	false
2533	3098	1/15/16	Construction Documents	¤ 17,000.00	false
2534	3098	7/15/16	Construction Observation	¤ 8,000.00	false
2541	3093	8/25/14	Schematic Plan	¤ 2,000.00	true
2542	3093	2/27/15	Planting Plan	¤ 3,000.00	false
2551	3099	12/23/14	Schematic Landscape Design	¤ 6,500.00	false
2561	3094	8/25/14	Schematic Plan	¤ 2,000.00	true
2562	3094	3/23/15	Planting Plan	¤ 3,000.00	false
2571	3104	9/28/14	Schematic Plan	¤ 2,000.00	false
2572	3104	1/27/15	Planting & Materials Plan	¤ 3,500.00	false
2581	3097	10/3/14	Schematic Plan	¤ 2,000.00	false
2582	3097	12/2/14	Landscape Layout & Planting Plan	¤ 4,000.00	false
2583	3097	4/20/15	Construction Observation	¤ 2,500.00	false
2591	3108	1/6/15	Schematic Landscape Plan	¤ 12,000.00	false
2592	3108	4/13/15	Community Meetings	¤ 3,000.00	false
2593	3108	2/15/16	Construction Documents	¤ 25,000.00	false
2594	3108	3/31/17	Construction Observation	¤ 10,000.00	false
2601	3100	3/8/15	Schematic Landscape Design	¤ 10,500.00	false
2611	3103	8/17/15	Schematic Open Space & Landscape Plan	¤ 45,000.00	false
2612	3103	8/16/16	Community Meetings & Permitting	¤ 25,000.00	false
2613	3103	12/17/17	Construction Documents	¤ 105,000.00	false
2614	3103	9/30/19	Construction Admin. & Observation	¤ 77,000.00	false
2621	3109	4/18/15	Schematic Plan	¤ 8,000.00	false
2622	3109	10/18/15	Construction Documents	¤ 18,000.00	false
2623	3109	9/23/16	Construction Admin. & Observation	¤ 15,000.00	false
2641	3105	10/24/14	Schematic Plan	¤ 2,000.00	false
2642	3105	5/25/15	Planting Plan	¤ 3,000.00	false
2651	3110	12/23/14	Community Meetings	¤ 4,000.00	false
2652	3110	2/24/15	Schematic Plan	¤ 9,500.00	false
2653	3110	12/21/15	Construction Documents	¤ 15,000.00	false
2654	3110	5/1/17	Construction Admin. & Observation	¤ 9,500.00	false

SELECT [tblContract].[ContractNum] FROM tblContract;

SELECT [qryCustomersByName].[Customer], [qryCustomersByName].[CustomerID] FROM qryCustomersByName ORDER BY [Customer];

PARAMETERS __CustomerID Value; SELECT DISTINCTROW * FROM tblContract AS frmCustomerContracts WHERE ([__CustomerID] = CustomerID);

SELECT DISTINCTROW * FROM tblContract;

SELECT DISTINCTROW * FROM tblCustomer;

SELECT DISTINCTROW * FROM tblInvoice;

SELECT DISTINCTROW * FROM tblContract;

SELECT DISTINCTROW * FROM tblCustomer;

SELECT [tblCustomer].[CustomerID], [tblCustomer].[Company], [tblCustomer].[FirstName], [tblCustomer].[LastName], [tblCustomer].[Phone], [tblCustomer].[Address], [tblCustomer].[City], [tblCustomer].[State], [tblCustomer].[Zip], [tblCustomer].[EmailAddress], [tblCustomer].[Comments], [tblContract].[SigningDate], [tblContract].[ContractNum], [tblContract].[ContractAmt], [tblContract].[StartDate], [tblContract].[ContractType] FROM tblCustomer INNER JOIN tblContract ON tblCustomer.CustomerID=tblContract.CustomerID;

SELECT tblCustomer.CustomerID, tblCustomer.Company, tblCustomer.FirstName, tblCustomer.LastName, tblCustomer.Phone, tblCustomer.Address, tblCustomer.City, tblCustomer.State, tblCustomer.Zip, tblCustomer.EmailAddress, tblCustomer.Comments FROM tblCustomer WHERE (((tblCustomer.Phone) Like "616*"));

SELECT Min(tblContract.ContractAmt) AS MinimumContractAmt, Avg(tblContract.ContractAmt) AS AverageContractAmt, Max(tblContract.ContractAmt) AS MaximumContractAmt FROM tblContract;

SELECT tblCustomer.City, Min(tblContract.ContractAmt) AS MinimumContractAmt, Avg(tblContract.ContractAmt) AS AverageContractAmt, Max(tblContract.ContractAmt) AS MaximumContractAmt FROM tblCustomer INNER JOIN tblContract ON tblCustomer.CustomerID = tblContract.CustomerID GROUP BY tblCustomer.City;

SELECT tblContract.ContractNum, tblContract.ContractAmt, tblContract.ContractType FROM tblContract ORDER BY tblContract.ContractNum;

SELECT tblCustomer.CustomerID, tblCustomer.Company, tblCustomer.FirstName, tblCustomer.LastName, tblCustomer.City, tblCustomer.EmailAddress FROM tblCustomer ORDER BY tblCustomer.CustomerID;

SELECT tblCustomer.CustomerID, tblCustomer.Company, tblCustomer.FirstName, tblCustomer.LastName, tblCustomer.City, tblInvoice.InvoiceAmt, tblInvoice.InvoicePaid FROM tblCustomer INNER JOIN (tblContract INNER JOIN tblInvoice ON tblContract.ContractNum = tblInvoice.ContractNum) ON tblCustomer.CustomerID = tblContract.CustomerID;

TRANSFORM Sum(qryCustomersAndInvoices.InvoiceAmt) AS SumOfInvoiceAmt SELECT qryCustomersAndInvoices.City, Sum(qryCustomersAndInvoices.InvoiceAmt) AS [Total Of InvoiceAmt] FROM qryCustomersAndInvoices GROUP BY qryCustomersAndInvoices.City PIVOT IIf([InvoicePaid],"Paid","Unpaid");

SELECT IIf(IsNull([Company]),[LastName] & ", " & [FirstName],[Company]) AS Customer, tblCustomer.CustomerID, tblCustomer.Company, tblCustomer.FirstName, tblCustomer.LastName, tblCustomer.Phone, tblCustomer.Address, tblCustomer.City, tblCustomer.State, tblCustomer.Zip, tblCustomer.EmailAddress, tblCustomer.Comments FROM tblCustomer ORDER BY IIf(IsNull([Company]),[LastName] & ", " & [FirstName],[Company]);

SELECT IIf(IsNull([Company]),[LastName] & ", " & [FirstName],[Company]) AS Customer, tblCustomer.CustomerID, tblCustomer.Company, tblCustomer.FirstName, tblCustomer.LastName, tblCustomer.Phone, tblCustomer.Address, tblCustomer.City, tblCustomer.State, tblCustomer.Zip, tblCustomer.EmailAddress, tblCustomer.Comments FROM tblCustomer WHERE (((tblCustomer.City) Like [Type the city:] & "*")) ORDER BY IIf(IsNull([Company]),[LastName] & ", " & [FirstName],[Company]);

SELECT tblContract.StartDate, tblContract.ContractNum, tblContract.CustomerID, tblContract.ContractAmt, tblContract.SigningDate, tblContract.ContractType FROM tblContract WHERE (((tblContract.StartDate) In (SELECT [StartDate] FROM [tblContract] As Tmp GROUP BY [StartDate] HAVING Count(*)>1 ))) ORDER BY tblContract.StartDate;

SELECT tblContract.ContractNum, tblContract.ContractAmt, tblContract.SigningDate, tblContract.ContractType, tblCustomer.Company, tblCustomer.FirstName, tblCustomer.LastName, tblCustomer.Phone, tblCustomer.Address, tblCustomer.EmailAddress FROM tblCustomer INNER JOIN tblContract ON tblCustomer.CustomerID = tblContract.CustomerID WHERE (((tblCustomer.City)="Holland")) ORDER BY tblContract.ContractNum;

SELECT qryCustomersByName.Customer, qryCustomersByName.CustomerID, qryCustomersByName.Company, qryCustomersByName.FirstName, qryCustomersByName.LastName, qryCustomersByName.Phone, qryCustomersByName.Address, qryCustomersByName.City, qryCustomersByName.State, qryCustomersByName.Zip, qryCustomersByName.EmailAddress, qryCustomersByName.Comments FROM qryCustomersByName LEFT JOIN tblContract ON qryCustomersByName.[CustomerID] = tblContract.[CustomerID] WHERE (((tblContract.CustomerID) Is Null));

SELECT tblInvoice.InvoiceItem, tblInvoice.InvoiceDate, tblInvoice.InvoiceAmt, tblInvoice.InvoicePaid, tblContract.ContractType, tblCustomer.City, qryCustomersByName.Customer FROM (qryCustomersByName INNER JOIN (tblCustomer INNER JOIN tblContract ON tblCustomer.[CustomerID] = tblContract.[CustomerID]) ON qryCustomersByName.[CustomerID] = tblContract.[CustomerID]) INNER JOIN tblInvoice ON tblContract.[ContractNum] = tblInvoice.[ContractNum] ORDER BY tblInvoice.InvoiceItem, tblInvoice.InvoiceDate;

SELECT tblCustomer.Company, tblCustomer.FirstName, tblCustomer.LastName, tblCustomer.Phone, tblCustomer.City, tblContract.ContractAmt, tblContract.SigningDate FROM tblCustomer INNER JOIN tblContract ON tblCustomer.CustomerID = tblContract.CustomerID WHERE (((tblCustomer.City)="Lansing") AND ((tblContract.ContractAmt)>25000));

SELECT tblContract.ContractNum, tblContract.ContractAmt, tblContract.SigningDate, tblContract.ContractType, tblCustomer.Company, tblCustomer.FirstName, tblCustomer.LastName, tblCustomer.Phone, tblCustomer.Address, tblCustomer.City, tblCustomer.EmailAddress FROM tblCustomer INNER JOIN tblContract ON tblCustomer.CustomerID = tblContract.CustomerID WHERE (((tblContract.ContractAmt)>25000)) ORDER BY tblContract.ContractAmt DESC;

SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.Company, tblCustomer.City, tblContract.ContractAmt, tblContract.SigningDate, tblContract.ContractType FROM tblCustomer INNER JOIN tblContract ON tblCustomer.CustomerID = tblContract.CustomerID WHERE (((tblContract.ContractAmt)<10000)) OR (((tblContract.SigningDate) Between #1/1/2014# And #3/1/2014#)) ORDER BY tblCustomer.LastName, tblCustomer.FirstName, tblContract.SigningDate;

SELECT tblContract.ContractNum, tblContract.ContractAmt, tblInvoice.InvoiceItem, tblInvoice.InvoiceAmt, [InvoiceAmt]*0.03 AS LateFee FROM tblContract INNER JOIN tblInvoice ON tblContract.ContractNum = tblInvoice.ContractNum WHERE (((tblInvoice.InvoicePaid)=No)) ORDER BY [InvoiceAmt]*0.03 DESC;

Access3/Tutorial/Prospect.accdb

CustomerID	FirstName	LastName	Phone	Address	City	State	Zip	EmailAddress
12001	Robin & Lee	Freeman	616-866-9891	3170 Lois Ln	Rockford	MI	49341	[email protected]
12002	Marsha & Ted	Strong	616-866-3946	3184 Lois Ln	Rockford	MI	49341	[email protected]
12003	Chris & David	Bowers	616-720-1093	3142 Kent Cir	Rockford	MI	49341	[email protected]
12004	Sandy & Frank	Lee	616-866-3337	3242 Lois Ln	Rockford	MI	49341	[email protected]