Selecting the latest records
-
Hi, I have a table that lists the incomes and expenditures of various organizations.
Organization | Report Year | Income | Expenditure Org 1 | 1/4/2008 | £20 | £30 Org 1 | 1/4/2007 | £20 | £30 Org 2 | 1/4/2003 | £20 | £30 Org 2 | 1/4/2002 | £20 | £30 Org 2 | 1/4/2001 | £20 | £30 Org 3 | 1/4/2006 | £20 | £30 Org 4 | 1/4/2002 | £20 | £30
I have been trying to write a query that will return the latest record for each organization. So the result would beOrg 1 | 1/4/2008 | £20 | £30 Org 2 | 1/4/2003 | £20 | £30 Org 3 | 1/4/2006 | £20 | £30 Org 4 | 1/4/2002 | £20 | £30
Tried searching but came up with no uselful answers. Can someone help? It needs to be compatible with Microsoft Access X| -
Hi, I have a table that lists the incomes and expenditures of various organizations.
Organization | Report Year | Income | Expenditure Org 1 | 1/4/2008 | £20 | £30 Org 1 | 1/4/2007 | £20 | £30 Org 2 | 1/4/2003 | £20 | £30 Org 2 | 1/4/2002 | £20 | £30 Org 2 | 1/4/2001 | £20 | £30 Org 3 | 1/4/2006 | £20 | £30 Org 4 | 1/4/2002 | £20 | £30
I have been trying to write a query that will return the latest record for each organization. So the result would beOrg 1 | 1/4/2008 | £20 | £30 Org 2 | 1/4/2003 | £20 | £30 Org 3 | 1/4/2006 | £20 | £30 Org 4 | 1/4/2002 | £20 | £30
Tried searching but came up with no uselful answers. Can someone help? It needs to be compatible with Microsoft Access X| -
Hi, I have a table that lists the incomes and expenditures of various organizations.
Organization | Report Year | Income | Expenditure Org 1 | 1/4/2008 | £20 | £30 Org 1 | 1/4/2007 | £20 | £30 Org 2 | 1/4/2003 | £20 | £30 Org 2 | 1/4/2002 | £20 | £30 Org 2 | 1/4/2001 | £20 | £30 Org 3 | 1/4/2006 | £20 | £30 Org 4 | 1/4/2002 | £20 | £30
I have been trying to write a query that will return the latest record for each organization. So the result would beOrg 1 | 1/4/2008 | £20 | £30 Org 2 | 1/4/2003 | £20 | £30 Org 3 | 1/4/2006 | £20 | £30 Org 4 | 1/4/2002 | £20 | £30
Tried searching but came up with no uselful answers. Can someone help? It needs to be compatible with Microsoft Access X|Hi Try this:
SELECT Organization, [Report Year], [Income], [Expenditure] FROM Table1 a WHERE [Report Year] >= ( SELECT TOP 1 [Report Year] FROM Table1 b WHERE a.Organization = b.Organization ORDER BY [Report Year] DESC ) ORDER BY Organization
Hope you are getting results as expected.Harini
-
Hi Try this:
SELECT Organization, [Report Year], [Income], [Expenditure] FROM Table1 a WHERE [Report Year] >= ( SELECT TOP 1 [Report Year] FROM Table1 b WHERE a.Organization = b.Organization ORDER BY [Report Year] DESC ) ORDER BY Organization
Hope you are getting results as expected.Harini
Thanks for replying blue_boy and harini. I used blue_boys solution in the end and it works as i want it. it actually fits into another much longer sql statement.
-
Thanks for replying blue_boy and harini. I used blue_boys solution in the end and it works as i want it. it actually fits into another much longer sql statement.