Get Value at Last Date

[BRANDON99]

Hi

I have a table with 3 columns, Account, Amount_Due, Date.
The amount due list the outstaning balance by date. The are multiple amouts for each account no various dates. I need to extract the balance on the last date for each account. I have tried a simple query, but this groups all amounts for each account, and will not just pick the last. How can I just pick the outstanding balance for the last date only, for each account?

Many thanks

Brandon

 

[cmmrfrds]

You can do it with 2 queries in the first query find the max and the second query find the balance = to the max date or do it in 1 query with a derived query.

derived query example.

SELECT dt.Item, dt.maxdatesold, A.vendor
FROM tblSales AS A INNER JOIN [Select b.item, Max(B.DateSold) AS MaxDateSold from tblSales as B
Group by b.item]. AS dt ON dt.Item = A.Item
WHERE dt.maxdatesold = A.datesold;

In this query the item is equivalent to the account and the vendor to the balance in your query.

 

[BRANDON99]

Thanks - I will try this and let you know how I get on

 

[BRANDON99]

This is fine - except there are some records that have multiple entries for one date! there is noe time stamp( the data is from another data base). I think this may be a lost cause!

Thnanks for you help