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.