How to make a query selected every company's latest record??

[Martin]

Hello! I want to establish a query which can pick up every Company's latest
financial statment.
How can I make it? Thanks!


Company Name Statement year Statement month currency asset
liability ...

A Co. 2005 3
A Co. 2005 2
A Co. 2005 1
A Co. 2004 12
B Co. 2005 2
B Co. 2004 6
C Co. 2004 9
C Co. 2004 10
C Co. 2004 11
D Co. 2005 2



Query result:

Company Name Statement year Statement month currency asset
liability ...

A Co. 2005 3
B Co. 2005 2
C Co. 2004 11
D Co. 2005 2

 

[gert birkner]

hi martin,

I don´t know if this is the best way but it works.

you can calculate the 2 columns like

SELECT MAX((StatementYear * 100) + Statementmonth) AS SortCol, CompanyName,
.....
FROM ..
WHERE..
GROUP BY CompanyName, ...
ORDER BY CompanyName


hope that´s it

gert birkner

 

[Martin]

Thanks! But it can not work.

hi martin,

I don´t know if this is the best way but it works.

you can calculate the 2 columns like

SELECT MAX((StatementYear * 100) + Statementmonth) AS SortCol,
CompanyName, ....
FROM ..
WHERE..
GROUP BY CompanyName, ...
ORDER BY CompanyName


hope that´s it

gert birkner