Query to find max date value for an ID in a linked table

[Tim F]

Hi all! I wonder if anyone could offer advice - I am not exactly an SQL gun and have been playing with this for a day now. Any nudges in the right direction would be very much appreciated.

I am trying to create a query based on 2 linked tables, that gives me a grouped list of IDs from table 1 and a the latest date in table 2 for a row that has the same ID as table 1

So for example I have 2 tables.

table1 includes
MyID - primary key
MyName


table2 includes
ID
MyID - foreign key
MyDate
MyComment

I want to find table2.MyComment for the latest table2.MyDate where table2.MyID matches table1.MyID (eg the grouping level).

So say table1 is

MyID    Myname
1    name1
2    name2

and table2 is

ID    MyID    MyDate    MyComment
1    1    1/1/12    Comment1
2    1    2/1/12    Comment2
3    2    1/1/12    Comment3

My query should return

MyID    MyName    MyComment
1    name1    Comment2
2    name2    Comment3

I have been able to achieve this using the following:

=Dlookup("[MyComment]","table2","[MyID] = " & [MyID] & " And datevalue([MyDate]) = #" & format(dmax("[MyDate]","table2","[MyID] = " & [MyID]),"mm/dd/yyyy") & "#")

in a continuous form with table1 as the recordsource but it is very slow, so I'm hoping to replace this with a single query I can use as the recordsource.

Where I am stuck is what syntax to use for the query. In single instances of dcount in a continuous form I can just refer to a field in the current row, if that makes sense but not sure how to do it in SQL.

I have got as far as this

SELECT table1.myID
, table1.MyName
, table2.MyComment WHERE MyDate = MAX(SELECT MyDate FROM table2 WHERE table2.MyID = table1.MyID)
FROM table1 INNER JOIN table2 ON table1.MyID = table2.MyID
GROUP BY table1.MyID;

But this gives me a syntax error (missing operator) in the WHERE statement, I suspect I am referencing the tables and fields wrong and I also wonder if there is a completely different way to go about this?

I have also tried using the query builder but with no success yet. I have had a search on this forum but I could only find examples querying a single table.

[Seth Schrock]

Try moving your WHERE clause to between lines 4 & 5. This would leave you with:

SELECT...
FROM...
WHERE...
GROUP BY...

[Tim F]

Cheers for the reply!
I tried your suggestion but got error 'Cannot have aggregate function in WHERE clause (MAX()=)'
A little googling suggested I need to use HAVING instead of where so I ended up with:

SELECT 
   tblProjects.Project_Number,
   tblProjects.Project_Name,
   tblProjectStatus.Exec_Status 
FROM tblProjects
   INNER JOIN tblProjectStatus 
      ON tblProjects.Project_Number
        tblProjectStatus.Project_Number
   GROUP BY tblProjects.Project_Number
   HAVING Date_Updated = 
      MAX(SELECT 
            Date_Updated 
         FROM tblProjectStatus 
         GROUP BY tblProjects.Project_Number 
         HAVING tblProjectStatus.Project_Number =
            tblProjects.Project_Number);

Which unfortunately produces 'cannot have aggregate function in expression (MAX()=)'

So it seems I might not be able to use MAX in this way - is there an alternative?

[Seth Schrock]

In your nested query, replace the HAVING with WHERE as the HAVING isn't needed. Unfortunately, I don't have a database that I can test this with, but that I what I'm reading from the error message. So your last line would be (broken down for easier reading):

HAVING Date_Updated = MAX(
SELECT Date_Updated 
FROM tblProjectStatus 
GROUP BY tblProjects.Project_Number 
WHERE tblProjectStatus.Project_Number = tblProjects.Project_Number
);

[zmbd]

The way I think of these queries is to break them up into small chunks:

Starting with table2:
What you want is the last (max) date of each entry made by the my id. So you need to group on the user ID and return the max date. Call this Q1
(myid,mydate) = (1,2/1/12) and (2,1/1/12)
SQL

SELECT 
   Table2.FK_MyID,
   Max(Table2.MyDate) 
      AS MaxOfMyDate
FROM Table2
GROUP BY Table2.FK_MyID;

This is great now we need the comment associated with these... well only two records will match so do a simple inner join with table2 against the agregate query Q1 on the FK_Myid and the MyDate fields. Show the fields from table 2 that we're interested in. Call this Q2
(PK_tbl2, FK_MyID, MYDate, Comment)=
(2, 1, 2/12/2012, Comment2)
(3, 2, 1/1/2012, Comment3)
SQL

SELECT 
   Table2.PK_Tbl2, 
   Table2.FK_MyID, 
   Table2.MyDate, 
   Table2.Comment
FROM Table2 
   INNER JOIN Q1 
      ON (Table2.MyDate = Q1.MaxOfMyDate) 
         AND (Table2.FK_MyID = Q1.FK_MyID);

Finally we need to get the names from table one... easy same inner join on table1 and q2 showing the name field from table1 and the date and comment from q2. Call this Q3
(Q2!PK_tbl2, Table1!MyName, Q2!MYDate, Q2!Comment)=
(2, Name1, 2/12/2012, Comment2)
(3, Name2, 1/1/2012, Comment3)

SQL -Q3:

SELECT 
   Q2.PK_Tbl2, 
   Table1.MyName, 
   Q2.MyDate,
    Q2.Comment
FROM Table1 
   INNER JOIN Q2 
      ON Table1.MyID = Q2.FK_MyID;

So the final SQL looks like:

SELECT 
   Q2.PK_Tbl2, 
   Table1.MyName, 
   Q2.MyDate,
    Q2.Comment
FROM Table1 
   INNER JOIN 
   ( SELECT 
       Table2.PK_Tbl2, 
       Table2.FK_MyID, 
       Table2.MyDate, 
       Table2.Comment
    FROM Table2 
       INNER JOIN
          (SELECT 
             Table2.FK_MyID,
             Max(Table2.MyDate) 
      AS MaxOfMyDate
          FROM Table2
          GROUP BY Table2.FK_MyID) 
      AS Q1 
          ON (Table2.MyDate = Q1.MaxOfMyDate) 
             AND (Table2.FK_MyID = Q1.FK_MyID))
    AS Q2 
      ON Table1.MyID = Q2.FK_MyID;

-edit- found typo
Ofcourse, in Q2 you could go ahead and add in table1 to get the name value so you'd get something like:

SELECT 
   Table2.PK_Tbl2, 
   Table1.MyName, 
   Table2.FK_MyID, 
   Table2.MyDate, 
   Table2.Comment
FROM Table1 
   INNER JOIN 
      (Table2 
         INNER JOIN
         (SELECT 
            Table2.FK_MyID,
            Max(Table2.MyDate)
               AS MaxOfMyDate
          FROM Table2
          GROUP BY Table2.FK_MyID)
       AS Q1 
       ON (Table2.MyDate = Q1.MaxOfMyDate) 
          AND (Table2.FK_MyID = Q1.FK_MyID)) 
      ON Table1.MyID = Table2.FK_MyID;

Which I think looks a little neater and one less sub.


Now I'm sure there are more elegant ways of doing this, and I understand that the subqueries can slow things down on large databases; however, I've returned 100's of records from my databases with just such a query (well, same concept) in just a blink of an eye.

[Tim F]

Thank you very much Seth & zmbd - I have followed your advice and learnt a bit in the process. I'm using zmbd's suggestion and following the logic have been able to make a few changes to get it doing exactly what I want.
Cheers!