Add additional "max" statement to 4-line SQL Code

[jwb1012]

Hello, I am very new to SQL and Access - most of my experience is in VBA and Excel. I am trying to add "max" to the "TXN_Number" field...Any guidance would be much appreciated.

SELECT t.ID, t.TXN_Type, t.Program_TXN_Number, t.TXN_Date
FROM PURCHASING_DB AS t INNER JOIN (SELECT ID, max(TXN_Date) AS MaxDate FROM PURCHASING_DB GROUP BY ID)  AS tm ON (t.ID= tm.ID) AND (t.TXN_Date = tm.MaxDate)
WHERE (((t.TXN_Type)="Purchase" Or (t.TXN_Type)="Initial Balance"));

 

[Russell Hauf]

So what do you want to see in your results? Maybe something like:

ID                     TXN_TYPE          Program_TXN_NUMBER      MaxDate
1                       Purchase            44                           12/12/2016
1                       Initial Balance    55                            10/01/2016
2                       Purchase            44                           09/07/2016
2                       Initial Balance     55                           08/31/2016

??