It's possible in Monarch, but it two projects.
First, connect to the external source, then sort it on date descending and make a unique filter on the account number.
Export this as a local MDB file.
Then open the original table and make a lookup to this MDB, and you should just have the latest transaction date for each account.
Just posted this on the future feature follow-up to your post:
You can also handle this issue in reverse. Start with the many to 1 table and work from their. Once you have your datasets built, filter out what you dont need. This removes your need to know SQL and reduces the need for another pass.
Depending on the size of the tables this could take longer to run.