LittleJohn LittleJohn - 9 days ago 5
SQL Question

Query of three related tables, showing only the latest records for each person, with SQL in MS Access

I have three tables:

Registrants


  • Registrant ID (Primary key)



Correspondences


  • Correspondence ID

  • Correspondence Time

  • Correspondence Date

  • Registrant ID (Foreign key)

  • Course ID (Foreign key)



Course


  • Course ID (Primary key)



The idea is that we can record who has registered for which course, whether a course is full and so on, while keeping track of all of the changes along the way and keeping detailed notes for each phone-call, email, fax, etc (there are way more fields than I have shown here).

What I want is a query with one unique Registrant ID and the Course ID in the latest Correspondence record (as determined by Corresponence Time and Date) in each row. So basically I can look at the query and read off something like this:


  • "Person A last said he would like to attend Course C at this time and date"

  • "Person D last said she would like to attend Course A at this time and date"



Solutions that use SQL or the MS Access 'Design View' UI are both fine.

Answer

You need a bunch of parentheses in MS Access.

SELECT c1.[correspondence id],
       r.[registrant id],
       c2.[course id],
       [correspondence date] + [correspondence time] AS expr1
FROM   course c2
       INNER JOIN (registrants r
                   INNER JOIN correspondences c1
                     ON r.[registrant id] = c1.[registrant id])
         ON c2.[course id] = c1.[course id]
WHERE  [correspondence id] IN (SELECT [correspondence id]
                               FROM   correspondences b
                               WHERE  b.[registrant id] = c1.[registrant id]
                                      AND [correspondence date] +
                                          [correspondence time]
                                          =
                                          (SELECT MAX([correspondence date] +
                                                      [correspondence time])
                                           FROM   correspondences
                                           WHERE
                                              [registrant id] =
                                              c1.[registrant id]))

I would go mad very quickly if I had to work with these tables. Get rid of all the spaces.

Comments