show only newest date for given criteria

[sunil rathore]

the table having following fields
1 Account_number
2 deposit_amount
3 date_of_deposit


4 balance_on_deposit

I want to design query that shows the account_number, newest date of deposit, and balance on the newest date.

[JoeM]

On easy way is to do this in a two-step query:

Query One
Create an Aggregate Query to get the latest deposit date for each account_number.
Do this by creating a new query adding only the "account_number" and "date_of_deposit" fields to it.
Click on the Totals button to make this an Aggregate Query (button looks like a Sigma).
On the Totals Row that gets added under each field, change the value of "Group By" under the "date_of_deposit" field to "Max".

Query Two
Link Query One back to your original table on account_number and date_of_deposit fields and return those two fields along with the "balance_on_deposit" field.

Or, if you feel so inclined, you can try nesting these two queries in one query by writing the query in SQL code directly.