Hello everyone,

I hope you can help me. I work in a mutual funds operations environment, and I have created a database to track our balance of an account to the fund's balance of that account.

Here is how the database is set up: One table for accounts, which has an accountID, and one table for balances, which has an accountID and a balanceID. I have set up a form for accounts with a subform for balances. That way, I can see a running history of balances.

For reporting purposes, I need to use the current balance (I have a date field to show the date it was balanced). The problem is that I don't know how to do this, aside from having a field labeled "flag" where you put an 'x' by the most recent record. In the balances table, there may be 5 records that have the same accountID, but different dates. So I need to select the most recent one of those 5, for each accountID. I can do this with a flag field, but I would also like to do a report showing how long it takes to balance something, and I can't make that work with the flag. Any ideas on how I can do this? Is my design flawed?

Sorry for the long post, any help would be greatly appreciated. Thanks!

Eric

Either totals query for the report with the date as Max or order the date field desc.

The reason I don't think that will work is because there will be, as an example, 20 records in the balances table. Of those 20, there may be 5 records each for 4 accountID's. If I use a total query with the date as max, I will only get the most recent entry for one accountID.

Unless I misunderstood what you were saying, I don't think this would work... Any other ideas? Thanks for the help so far!

You'll get the Max date for each account number add GroupBy to the account ID, you'll need a second query to get the correct amount though. Are you trying to do this at form level?

[This message has been edited by Rich (edited 01-29-2002).]

OK, that worked brilliantly. Thanks! Now, what if I want to find the latest two records per accountID? How could I do that? Again, thanks for the help!

Oh, and I'm not trying to do it on a form level. The form is fine, I can just sort the subform by date. Its for making reports, etc.

[This message has been edited by ejstefl (edited 01-29-2002).]

I haven't tried this so it's just theory but you might try the Top values property of the query.
HTH

That only gives me the top two AccountID's...