• I have a simple tblAcctsRcvable table in Access 2002, with 3 fields: [CustomerID, Date, Outstanding Balance]. Say I have just 6 records in tblAcctsRcvable as follow:


    [CustomerID, Date, Outstanding Balance]
    [1, 31/12/2006, 45678.95]
    [1, 31/1/2007, 55700.95]
    [1, 28/2/2007, 39098.95]
    [2, 31/12/2006, 145678.95]
    [2, 31/1/2007, 105212.95]
    [2, 28/2/2007, 125434.95]



    I need to create a simple query table {call it qryLatestAcctsRcvable} which filters just the LATEST record for each CustomerID. So from the above tblAcctsRcvable, the query result table will show just 2 records:


    [CustomerID, Date, Outstanding Balance]
    [1, 28/2/2007, 39098.95]
    [2, 28/2/2007, 125434.95]


    What is/are the criteria formula(s) I need to put in the query design grid for my qryLatestAcctsRcvable to achieve this? Thanks in advance.

  • Re: Query


    Try this SQL.


    SELECT tblAcctsRcvable.CustomerID, tblAcctsRcvable.MyDate, tblAcctsRcvable.[Outstanding Balance]
    FROM tblAcctsRcvable
    WHERE (((tblAcctsRcvable.MyDate)=DMax("MyDate","tblAcctsRcvable","CustomerID=" & [CustomerID])));

    Boo!:yikes:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!