none
Select group by Query in Access RRS feed

  • Question

  • I am attempting to display my most recently reconciled accounts in a query.  I would like to see a resultant set of the fields RecAccount, To Date (as Most recent Date), and the Ending Amount corresponding to the most recent date.

    generically:
    Account, Most Recently Reconciled, Amount of Most Recently Reconciled.

    However, I cannot make anything work or get passed this:

    SELECT [Reconciled Accts].RecAccount, Max([Reconciled Accts].[To Date]) AS [MR Date]
    FROM [Reconciled Accts]
    GROUP BY [Reconciled Accts].RecAccount

    Any suggestions?

    Sunday, September 15, 2019 3:04 PM

Answers

  • Try

    SELECT RecAccount, [To Date] AS [MR Date], [Ending Amount] AS [MR Amount]
    FROM [Reconciled Accts]
    WHERE [To Date]=(SELECT Max(T.[To Date]) FROM [Reconciled Accts] AS T WHERE T.RecAccount = [Reconciled Accts].RecAccount)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, September 15, 2019 7:01 PM

All replies

  • What exactly do you mean :

     I cannot make anything work or get passed this:

    Your Query looks OK ...

    Sunday, September 15, 2019 6:37 PM
  • Try

    SELECT RecAccount, [To Date] AS [MR Date], [Ending Amount] AS [MR Amount]
    FROM [Reconciled Accts]
    WHERE [To Date]=(SELECT Max(T.[To Date]) FROM [Reconciled Accts] AS T WHERE T.RecAccount = [Reconciled Accts].RecAccount)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, September 15, 2019 7:01 PM
  • Thank you, this worked.  
    Sunday, September 15, 2019 10:52 PM