Max Date in a query whilst showing all fields

[franc sutherland]

Hi,
I'm using Access 2003, Windows XP.

I have a table of company turnover data. I want to query out the most
recent entry, by date, for each company (The most recent entry by
date, won't always be the most recent keyed in).
Using the MAX total in a Select query means that I have to remove all
fields except company_id and date, then Group on the company_id to
remove duplicate entries for a company. Is it possible to have all
five fields displayed whilst only showing the most recent entry, by
date, for each company?

The table is tbl_turnover

The fields are
turnover_id (autonumber)
turnover_company_id (integer)
turnover_year (date)
turover_amount (integer)

I've tried to use a sub-query but have been going round and round in
circles.

Any help would be greatly appreciated. I should know this, but I
can't get it to work!!

Thanks,

Franc.

 

[Allen Browne]

replied in comp.databases.ms-access

 

[Bob Barrows]

Hi,
I'm using Access 2003, Windows XP.

I have a table of company turnover data. I want to query out the most
recent entry, by date, for each company (The most recent entry by
date, won't always be the most recent keyed in).
Using the MAX total in a Select query means that I have to remove all
fields except company_id and date, then Group on the company_id to
remove duplicate entries for a company. Is it possible to have all
five fields displayed whilst only showing the most recent entry, by
date, for each company?

The table is tbl_turnover

The fields are
turnover_id (autonumber)
turnover_company_id (integer)
turnover_year (date)
turover_amount (integer)

I've tried to use a sub-query but have been going round and round in
circles.

Huh? Why not show us what you tried?
Anyways, you said you had 5 fields to display by you only show 4. This
should work:

SELECT turnover_id,t.turnover_company_id,t.turnover_year,
turover_amount, fifth_field
FROM tbl_turnover As t join (
SELECT turnover_company_id,MAX(turnover_year) AS MaxYear
FROM tbl_turnover GROUP BY turnover_company_id) As q
ON t.turnover_company_id=q.turnover_company_id AND
t.turnover_year=MaxYear

Assuming there are no ties (the name of that turnover_year field worries me:
why is a field that stores a date called "year"?), this should work. If it
doesn't you will need to provide more details, probably a few rows of sample
data in tabular format, what you want the query to return (again in tabular
format), and what the above query actually did return.

 

[franc sutherland]

Huh? Why not show us what you tried?
Anyways, you said you had 5 fields to display by you only show 4. This
should work:

SELECT turnover_id,t.turnover_company_id,t.turnover_year,
turover_amount, fifth_field
FROM tbl_turnover As t join (
SELECT turnover_company_id,MAX(turnover_year) AS MaxYear
FROM tbl_turnover GROUP BY turnover_company_id) As q
ON t.turnover_company_id=q.turnover_company_id AND
t.turnover_year=MaxYear

Assuming there are no ties (the name of that turnover_year field worries me:
why is a field that stores a date called "year"?), this should work. If it
doesn't you will need to provide more details, probably a few rows of sample
data in tabular format, what you want the query to return (again in tabular
format), and what the above query actually did return.

Hi Bob,

Sorry, there were only four fields. I took out ", fifth_field" and
ran it. It gives a syntax error in the FROM clause, then highlights
the word "join".

There are no ties to other tables, this is a base table with no
lookups or similar.

-- Some sample data ---

turnover_id turnover_company_id turnover_year
turnover_amount
1 100
01/04/2009 100
2 100
01/04/2010 200

-- sample output ---

turnover_id turnover_company_id turnover_year (most recent
date) turnover_amount
2 100
01/04/2010 200


Thanks,
Franc.

 

[John Spencer]

Bob forgot the INNER before the JOIN and forgot to specify the query alias
before MaxYear in the on clause. Also he entered turover_Amount since that is
what you posted as the field name. I am going to assume it should be
turnover_amount.



SELECT turnover_id
,t.turnover_company_id
,t.turnover_year
,T.turnover_amount
FROM tbl_turnover As t INNER JOIN
(
SELECT turnover_company_id, MAX(turnover_year) AS MaxYear
FROM tbl_turnover
GROUP BY turnover_company_id
) As q
ON t.turnover_company_id=q.turnover_company_id
AND t.turnover_year=q.MaxYear

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[franc sutherland]

Bob forgot the INNER before the JOIN and forgot to specify the query alias
before MaxYear in the on clause.  Also he entered turover_Amount since that is
what you posted as the field name.  I am going to assume it should be
turnover_amount.

SELECT turnover_id
,t.turnover_company_id
,t.turnover_year
,T.turnover_amount
FROM tbl_turnover As t INNER JOIN
(
    SELECT turnover_company_id, MAX(turnover_year) AS MaxYear
    FROM tbl_turnover
    GROUP BY turnover_company_id
) As q
ON t.turnover_company_id=q.turnover_company_id
AND t.turnover_year=q.MaxYear

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi John,
Thanks for that, it works perfectly.
Sorry about the initial typo on turnover_amount.
Franc.