Max Date in a query whilst showing all fields
in [Help]
|
Prev: Source code browser for VBA
Next: Shifting data from columns and removing question mark charactersfrom columns
From: franc sutherland on 2 Jul 2010 07:10 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.
From: Allen Browne on 2 Jul 2010 08:39 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(a)googlemail.com> wrote in message news:e12e6155-851f-485e-a624-73c6ea5603dc(a)i1g2000vbk.googlegroups.com... > 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.
From: Salad on 2 Jul 2010 11:12 Allen Browne wrote: > 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. > That certainly would be the quickest and more effecient. Another option is using Dmax()
From: franc sutherland on 2 Jul 2010 11:16
On Jul 2, 1:39 pm, "Allen Browne" <AllenBro...(a)SeeSig.invalid> wrote: > 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.sutherl...(a)googlemail.com> wrote in message > > news:e12e6155-851f-485e-a624-73c6ea5603dc(a)i1g2000vbk.googlegroups.com... > > > 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. Hi Allen, Thanks for your help, works a treat. All the best, Franc. |