CODEWITHSUNDEEP
sqlvbams-access
intruesiive
intruesiive

Reputation: 65

VBA/SQL recordsets

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:

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;

Upvotes: 1

Views: 208

Answers (1)

HansUp
HansUp

Reputation: 97131

Use your query as a subquery and INNER JOIN it back to the [ALL] table to filter the rows.

SELECT
    a.Professor,
    a.Year,
    a.Course,
    a.title
FROM
    [ALL] AS a
    INNER JOIN
        (
            SELECT [All].Professor, [All].Course, Max([All].Year) AS MaxOfYear
            FROM [All]
            GROUP BY [All].Professor, [All].Course
        ) AS sub
    ON
            a.Professor = sub.Professor
        AND a.Course = sub.Course
        AND a.Year = sub.MaxOfYear;

Upvotes: 2

Related Questions