The project I'm asking about is for sending an email to teachers asking what books they're using for the classes they're teaching next semester, so that the books can be ordered. I have a query that compares the course number of this upcoming semester's classes to the course numbers of historical textbook orders, pulling out only those classes that are being taught this semester. That's where I get lost.
I have a table that contains the following:
-Professor
-Course Number
-Year
-Book -Title
The data looks like this:
professor year course number title
smith 13 1111 Pride and Prejudice
smith 13 1111 The Fountainhead
smith 13 1222 The Alchemist
smith 12 1111 Pride and Prejudice
smith 11 1222 Infinite Jest
smith 10 1333 The Bible
smith 13 1333 The Bible
smith 12 1222 The Alchemist
smith 10 1111 Moby Dick
johnson 12 1222 The Tipping Point
johnson 11 1333 Anna Kerenina
johnson 10 1333 Everything is Illuminated
johnson 12 1222 The Savage Detectives
johnson 11 1333 In Search of Lost Time
johnson 10 1333 Great Expectations
johnson 9 1222 Proust on the Shore
Here's what I need the code to do "on paper":
Group the records by professor. Determine every unique course number in that group, and group records by course number. For each unique course number, determine the highest year associated. Then spit out every record with that professor+course number+year combination.
With the sample data, the results would be:
professor year course number title
smith 13 1111 Pride and Prejudice
smith 13 1111 The Fountainhead
smith 13 1222 The Alchemist
smith 13 1333 The Bible
johnson 12 1222 The Tipping Point
johnson 11 1333 Anna Kerenina
johnson 12 1222 The Savage Detectives
johnson 11 1333 In Search of Lost Time
I'm thinking I should make a record set for each teacher, and within that, another record set for each course number. Within the course number record set, I need the system to determine what the highest year number is - maybe store that in a variable? Then pull out every associated record so that if the teacher ordered 3 books the last time they taught that class (whether it was in 2013 or 2012 and so on) all three books display. I'm not sure I'm thinking of record sets in the right way, though.
My SQL so far is basic and clearly doesn't work:
SELECT [All].Professor, [All].Course, Max([All].Year)
FROM [All]
GROUP BY [All].Professor, [All].Course;
Thanks for your help.
