Query to return the Max(Date) and other table fields based on a primary table list

Question

I am hoping somebody out there can help me with a query problem I can’t seem to resolve;

I have two tables;

Table1

T1_ID
T1_Serial

Table2

T2_ID
T1_ID –lookup to Table 1
T2_Date
T2_History

Table1 Data might look something like this;

T1_ID   T1_Serial
1,  ABC1
2,  ABC2
3,  ABC3
4,  ABC4

Table2 Data might look like this;

T2_ID,  T1_ID,  T2_Date,    T2_History
1,  1,  05/05/15,   “Some History1”
2,  1,  05/17/15,   “Some History2”
3,  2,  05/09/15,   “Some History3”
4,  2,  05/21/15,   “Some History4”
5,  3,  05/12/15,   “Some History5”

I would like to set up a query to give me the record containing Max(Date) of table History for each record in table Units

Table1.T1_Serial , Max(T2_Date), T2_History;

For this example;

ABC1    05/17/15    “Some History2”
ABC2    05/21/15    “Some History4”
ABC3    05/12/15    “Some History5”

I have constructed the SQL to give me the T1_Serial and Max(Date), which is working correctly;

SELECT Table2.T1_ID,  Max(Table2.T2_Date) AS MaxDate
FROM Table2
GROUP BY Table2.T1_ID;

But when I try adding the T2_History to the query I end up getting all the other history besides the Max(Date).

Hope someone can lead me on the right path. Thanks!

Simply take the results you have as an inline view, alias it as a table and join from it back to table1 and table 2. — xQbert, May 28 '15 at 18:22
Hi xQbert, Thanks for responding. I am not sure how to use alias in this context. So, my SQL looks like this; (sorry, struggling with formatting...) SELECT tbl_History.Link_RMAUnit, Max(tbl_History.Hist_Date) AS MaxDate FROM tbl_History GROUP BY tbl_History.Link_RMAUnit; How do I wrap this all together to rejoin to my initial tables? — Jeff Mitchell, May 28 '15 at 18:50

Juan Carlos Oropeza · Answer 1 · 2015-06-02T18:05:01.910

0

I use similar approach as Sam. But removed the T2_ID from the wrapped SQL to avoid issues with the GROUP BY. Also Join by Date.

SQL Fiddle

WITH MaxDate (T1_ID, MaxDate) as 
(
    SELECT T1_ID, Max(Table2.T2_Date) AS MaxDate
    FROM Table2
    GROUP BY Table2.T1_ID
)
SELECT T1_Serial, T2_Date, T2_History
FROM 
           MaxDate 
INNER JOIN Table2 ON MaxDate.MaxDate = Table2.T2_Date
INNER JOIN Table1 ON MaxDate.T1_ID = Table1.T1_ID;

vba version fiddle

SELECT tbl_RMAunit.RMA_SN, tbl_History.Hist_Date,tbl_History.Hist_History 
FROM ( 
  SELECT RMA_ID, Max(tbl_History.Hist_Date) as MaxDate 
  FROM tbl_History 
  Group by tbl_History.RMA_ID 
) MaxDate 
Inner Join tbl_History on MaxDate.MaxDate = tbl_History.Hist_Date 
Inner Join tbl_RMAunit on MaxDate.RMA_ID = tbl_RMAunit.RMA_ID

edited Jun 02 '15 at 18:05

answered May 29 '15 at 16:35

Juan Carlos Oropeza

47,252
12
78
118

Thanks Juan, Again, I claim a great deal of ignorance with working with SQL but I tried using your template which looks like; With MaxDate (RMA_ID,MaxDate) as ( select RMA_ID, Max(tbl_History.Hist_Date) as MaxDate From tbl_History Group by tbl_History.Hist_ID ) Select tbl_RMAunit.RMA_SN, tbl_History.Hist_Date,tbl_History.Hist_History From MaxDate Inner Join tbl_History on MaxDate.MaxDate = tbl_History.Hist_Date Inner Join tbl_RMAunit on MaxDate.RMA_ID = tbl_RMAunit.RMA_ID but when executing I get "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE' – Jeff Mitchell Jun 01 '15 at 12:47
What sql version are you working with? Try creating that schema in SQL fiddle so I can check it. Also try running each part to see what data are you bringing or where is the error. – Juan Carlos Oropeza Jun 01 '15 at 20:51
@JeffMitchell i think the problem is you put Hist_ID and should be `Group by tbl_History.RMA_ID` – Juan Carlos Oropeza Jun 01 '15 at 20:52
@JeffMitchell Try this one http://sqlfiddle.com/#!6/e0fa2/3 and let me know. Or update the schema there. – Juan Carlos Oropeza Jun 01 '15 at 20:56
Juan, Thanks for setting up the sqlfiddle link. this is awesome. I did not know this tool existed. You have created the exact table format and result I am looking for but, and perhaps I should have been clear that I am using this in Access 2010 VBA, I am having trouble porting this to my SQL query. If I copy the code verbatim, I continue to get the message "Invalid SQL statement; expected 'DELETE', 'INSERT','PROCEDURE', 'SELECT', or 'UPDATE'. – Jeff Mitchell Jun 02 '15 at 16:51
@JeffMitchell check VBA version. – Juan Carlos Oropeza Jun 02 '15 at 18:13
Looks like: VBA 7.0.1628 – Jeff Mitchell Jun 02 '15 at 20:10
I mean check the VBA version code I put above in my answer ;) – Juan Carlos Oropeza Jun 02 '15 at 20:32
Juan, I tested the code and got the following error; 'Syntex error (missing operator) in query expression 'MaxDate.MaxDate = tbl_History.Hist_Date Inner Join tbl_RMAunit on MaxDate.RMA_ID = tbl_RMAunit.RMA_I' . it seems to truncate the end of the expression IE: not RMA_ID. I though it might have been some buried ctrl character from the cut/paste but I got the same message even after entering the expression manually. – Jeff Mitchell Jun 02 '15 at 22:28

score 0 · Answer 2 · answered Jun 03 '15 at 14:56

I found this example for nested queries on VBA. But i couldnt try it. Good luck.

Sub TestNestedQuery()
    Dim RS As DAO.Recordset, strSql As String
    Dim qdfTemp As QueryDef
    Dim qdfNew As QueryDef
    Dim strSQL1 as string
    Dim strSQL2 as string


   strSQL1 = "SELECT RMA_ID, Max(tbl_History.Hist_Date) as MaxDate " & _
             "FROM tbl_History " & _ 
             "Group by tbl_History.RMA_ID"

   strSQL2 = "SELECT tbl_RMAunit.RMA_SN, tbl_History.Hist_Date,tbl_History.Hist_History " & _
             "FROM  qryTemp " & _
             "Inner Join tbl_History on qryTemp.MaxDate = tbl_History.Hist_Date  " & _
             "Inner Join tbl_RMAunit on qryTemp.RMA_ID = tbl_RMAunit.RMA_ID "

    With CurrentDb()
        Set qdfTemp = .CreateQueryDef("qryTemp", strSQL1) '' SQL 1

        Set qdfNew = .CreateQueryDef("qryNew", strSQL2)' SQL 2

        GetrstTemp qdfNew

        ''' Delete the two querydefs if necessary
        .QueryDefs.Delete qdfTemp.Name
        .QueryDefs.Delete qdfNew.Name
    End With

End Sub

Function GetrstTemp(qdfTemp As QueryDef)

   Dim rstTemp As DAO.Recordset
   Dim i As Integer: i = 0

   With qdfTemp
      Debug.Print .Name
      Debug.Print "  " & .sql
      '' Open Recordset from QueryDef.
      Set rstTemp = .OpenRecordset(dbOpenSnapshot)


      Do While Not rstTemp.EOF
          i = i + 1
          Debug.Print rstTemp.Fields("RMA_SN")    '' Change the field name 
          Debug.Print rstTemp.Fields("Hist_Date") '' Change the field name 
          rstTemp.MoveNext
      Loop
      Debug.Print
      Debug.Print "  Number of records = " & _
      rstTemp.RecordCount
   End With

End Function

Query to return the Max(Date) and other table fields based on a primary table list

2 Answers2