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.

The most efficient way to do this might be to make a simple Totals query
that just returns the company and max date:
SELECT turnover_company_id,
Max(turnover_year) AS TheDate
FROM tbl_turnover
GROUP BY turnover_company_id;

Save that query, and use it as an input 'table' for another query, along
with your original table. Join them on the company and date. This inner join
restricts it to the desired record, and you can then get all the fields you
want from the main table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"franc sutherland" <franc.sutherland (AT) googlemail (DOT) com> wrote

Allen Browne wrote:
is using Dmax()

On Jul 2, 1:39*pm, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) invalid> wrote:
Hi Allen,

Thanks for your help, works a treat.

All the best,

Franc.