Last data entry of each month! 1

[schredder]

I have a table with:
Date balance
12/05/2003 3
12/18/2003 7
12/29/2003 6
01/06/2004 9
01/12/2004 5
01/26/2004 4
02/03/2004 7
02/21/2004 2
The query should extract the last data entry of each month with the respective balance like:
12/29/2003 6
01/26/2004 4
02/21/2004 2

Couldn't find any similar problem in the forums. Don't think this is tough but i can't figure it out.
Thanks for any help.
Chris

 

[Steve101]

Try the following:

SELECT [Date], Balance
FROM tblYourTable
WHERE [Date] IN (
SELECT MAX([Date])
FROM tblYourTable
GROUP BY YEAR([Date]), MONTH([Date])
)
ORDER BY [Date]

By the way, its not a good idea to use 'Date' as a field name; thats why I've used the square brackets, to ensure that the SQL interpreter does not 'get confused'.

I havn't tested this, but hopefully it works.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[schredder]

Hammer!
I send a star down to Australia!
Thanks
Chris