Test 2

Hope it works

Contract.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 Phone BirthDate Gender

APP7509 Pat Nielson 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

13-1100 Yuka Koyama MM Pacific University 1/13/12 false

17-1798 Richard Jacobson PhD Pacific University 1/15/12 false

22-0102 Andre Dvorak BM University of Portland 3/3/12 false

34-4506 Marilyn Schwartz BM University of Portland 5/1/12 true

55-5310 Annamaria Romano BM Lewis & Clark College 3/12/12 true

63-1554 Carey Eberle BM University of Oregon 6/3/12 true

70-4490 Roger Norris BA University of Portland 9/2/12 true

72-0005 Sachi Tanaka BM Lawrence University 3/30/13 true

77-1201 Joy Culbertson BM Reed College 1/25/13 true

91-0178 Fred Bishop DMA Eastman School of Music 8/14/12 false

96-2151 Eric Mueller BA Whitman College 6/22/13 true

98-9123 Diana Reese MM Berklee College of Music 9/2/13 false

TeacherID FirstName LastName Degree School HireDate TakesBeginners

13-1100 Yuka Koyama MM Pacific University 1/13/12 false

17-1798 Richard Jacobson PhD Pacific University 1/15/12 false

22-0102 Andre Dvorak BM University of Portland 3/3/12 false

34-4506 Marilyn Schwartz BM University of Portland 5/1/12 true

55-5310 Annamaria Romano BM Lewis & Clark College 3/12/12 true

63-1554 Carey Eberle BM University of Oregon 6/3/12 true

70-4490 Roger Norris BA University of Portland 9/2/12 true

72-0005 Sachi Tanaka BM Lawrence University 3/30/13 true

77-1201 Joy Culbertson BM Reed College 1/25/13 true

91-0178 Fred Bishop DMA Eastman School of Music 8/14/12 false

96-2151 Eric Mueller BA Whitman College 6/22/13 true

98-9123 Diana Reese MM Berklee College of Music 9/2/13 false

TeacherID FirstName LastName Degree School HireDate TakesBeginners

13-1100 Yuka Koyama MM Pacific University 1/13/12 false

17-1798 Richard Jacobson PhD Pacific University 1/15/12 false

22-0102 Andre Dvorak BM University of Portland 3/3/12 false

34-4506 Marilyn Schwartz BM University of Portland 5/1/12 true

55-5310 Annamaria Romano BM Lewis & Clark College 3/12/12 true

63-1554 Carey Eberle BM University of Oregon 6/3/12 true

70-4490 Roger Norris BA University of Portland 9/2/12 true

72-0005 Sachi Tanaka BM Lawrence University 3/30/13 true

77-1201 Joy Culbertson BM Reed College 1/25/13 true

91-0178 Fred Bishop DMA Eastman School of Music 8/14/12 false

96-2151 Eric Mueller BA Whitman College 6/22/13 true

98-9123 Diana Reese MM Berklee College of Music 9/2/13 false

TeacherID FirstName LastName Degree School HireDate TakesBeginners

PARAMETERS __TeacherID Value;
SELECT DISTINCTROW *
FROM tblContract AS frmTeachersAndContracts
WHERE ([__TeacherID] = TeacherID);
SELECT DISTINCTROW *
FROM tblStudent;
SELECT DISTINCTROW *
FROM tblTeacher;
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 tblTeacher;
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;
SELECT tblStudent.LastName, tblStudent.FirstName, tblStudent.Phone
FROM tblStudent
ORDER BY tblStudent.LastName, tblStudent.FirstName;
SELECT tblTeacher.FirstName, tblTeacher.LastName, tblTeacher.HireDate
FROM tblTeacher
ORDER BY tblTeacher.LastName, tblTeacher.FirstName;
SELECT tblStudent.Phone, tblStudent.Zip
FROM tblTeacher INNER JOIN (tblStudent INNER JOIN tblContract ON tblStudent.StudentID = tblContract.StudentID) ON tblTeacher.TeacherID = tblContract.TeacherID
WHERE (((tblStudent.Phone) Like “541*”));
SELECT tblStudent.FirstName, tblStudent.LastName, tblStudent.City, tblStudent.Phone
FROM tblTeacher INNER JOIN (tblStudent INNER JOIN tblContract ON tblStudent.StudentID = tblContract.StudentID) ON tblTeacher.TeacherID = tblContract.TeacherID
WHERE (((tblStudent.Phone) Like “541*”))
ORDER BY tblStudent.LastName;
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#) AND ((tblContract.LessonType)=”Cello”)) OR (((tblContract.LessonType)=”Flute”)) OR (((tblContract.LessonType)=”Violin”))
ORDER BY tblTeacher.LastName, tblContract.StudentID;
SELECT tblStudent.FirstName, tblStudent.LastName, tblStudent.City, tblStudent.Phone
FROM tblTeacher INNER JOIN (tblStudent INNER JOIN tblContract ON tblStudent.StudentID = tblContract.StudentID) ON tblTeacher.TeacherID = tblContract.TeacherID
ORDER BY tblStudent.City;
SELECT tblStudent.FirstName, tblStudent.LastName, tblStudent.City, tblStudent.Phone
FROM tblTeacher INNER JOIN (tblStudent INNER JOIN tblContract ON tblStudent.StudentID = tblContract.StudentID) ON tblTeacher.TeacherID = tblContract.TeacherID
WHERE (((tblStudent.City)<>“Portland”));
SELECT tblTeacher.TeacherID, tblTeacher.Degree, tblTeacher.School, tblTeacher.HireDate, tblTeacher.TakesBeginners
FROM tblTeacher
ORDER BY tblTeacher.LastName, tblTeacher.FirstName DESC;
SELECT tblTeacher.TeacherID, tblTeacher.Degree, tblTeacher.School, tblTeacher.HireDate, tblTeacher.TakesBeginners, tblTeacher.[FirstName] & ” ” & [LastName] AS Name
FROM tblTeacher;
SELECT [FirstName] & ” ” & [LastName] AS TeacherName
FROM tblTeacher;
SELECT tblContract.ContractID, tblContract.StudentID, tblContract.TeacherID, tblContract.ContractStartDate, tblContract.ContractEndDate, tblContract.ContractEndDate, tblContract.LessonType, tblContract.LessonLength, tblContract.MonthlyLessonCost, tblContract.MonthlyRentalCost
FROM tblContract
WHERE (((tblContract.LessonType)=[What LessonType]));
TRANSFORM Count(tblContract.ContractID) AS CountOfContractID
SELECT tblContract.LessonType
FROM tblContract
GROUP BY tblContract.LessonType
PIVOT tblContract.LessonLength;
SELECT tblContract.[StudentID], tblContract.[LessonType], tblContract.[ContractID]
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.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));