highest value (1 Viewer)

[bendiver]

I have a query which shows the balances of various accounts and the dates to which these balances belong. What I need to be able to do if possible is to reduce the balances down to the latest date for each account id so that the most up to date balance for each account is shown. Is this possible, if so how do i do it...?

 

[Rich]

Groupby Account Id, Max DateField
HTH

 

[Pat Hartman]

You need a totals query.

Select AccountNum, Sum(TranAmt) As AccountBal
From YourTable
Group By AccountNum;

If you need a last transaction date also, then you'll need two more queries.

Select AccountNum Max(Trandate) As MaxTranDate
From YourTable
Group By AccountNum;

To get all the data together:


Select Q1.AccountNum, Q1.AccountBal, Q2.MaxTranDate
From query1 as Q1 Inner Join query2 as Q2 On Q1.AccountNum = Q2.AccountNum;