CODEWITHSUNDEEP
sqlms-accessrelationship
LittleJohn
LittleJohn

Reputation: 183

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

I have three tables:

Registrants

Correspondences

Course

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:

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

Upvotes: 1

Views: 803

Answers (2)

Fionnuala
Fionnuala

Reputation: 91356

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.

Upvotes: 1

GregHNZ
GregHNZ

Reputation: 8969

This is made more difficult because the date and time are in separate fields.

If you are using the sequential Autonum ID in Access, you can use the CorrespondenceID to indicate the latest one.

Try something like this:

SELECT 
  Reg.Name,
  Course.Name,
  C.CorrespondenceDate,
  C.CorrespondenceTime
FROM
  (SELECT MAX(CorrespondenceID) AS ID, RegistrantID, CourseID
     FROM Correspondence
      GROUP BY RegistrantID, CourseID) AS CX
  INNER JOIN Correspondences AS C ON CX.CorrespondenceID = CX.ID
  INNER JOIN Registrants Reg on CX.RegistrantID = Reg.RegistrantID
  INNER JOIN Course ON CX.CourseID = Course.CourseID;

If you are not using sequential autonum, you'll have to combine the date and time fields to determine which is the latest. You could probably substitute MAX(CorrespondenceDate + CorrespondenceTime) in the CX Subquery above.

A couple of other things you might consider:

  • Use a single DateTime field, populate with Now() will make it easier to determine the latest.
  • You have mixed pluralities on the table names "course" (singular) vs "registrants" (plural) - you should probably be consistent - many people prefer singular
  • I would consider having a table enrollment which does the mapping between course and registrant, then have correspondence map to enrollment. It feels a better structure to me.

Upvotes: 2

Related Questions