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.