Query Criteria - Pull Most Recent Data

[Jeremy]

Currently I have a query which pulls data similar to the following

CLIENT FINANCIAL DATE SALES
Pets Inc 12/31/2002 500,000
Pets Inc 12/31/2001 600,000
Pets Inc 12/31/2000 300,000
Toy Factory 12/31/1999 1,000,000
Toy Factory 12/31/1998 800,000
Shoe Company 12/31/2001 1,500,000
Shoe Company 12/31/2000 1,200,000
Shoe Company 12/31/1999 1,450,000
Shoe Company 12/31/1998 1,600,000
Shoe Company 12/31/1997 1,550,000

I would like to add some criteria so that the query only pulled the records associated with the most recent financial date. Therefore, there would only be one row for each company as follows

CLIENT FINANCIAL DATE SALES
Pets Inc 12/31/2002 500,000
Toy Factory 12/31/1999 1,000,000
Shoe Company 12/31/2001 1,500,000

Is this possible. Thanks in advance

Jeremy

 

[Steve Schapel]

Jeremy,

The SQL of this query will lok like this...

SELECT Client, [Financial Date], Sales FROM YourTable
WHERE [Financial Date] In(SELECT Max([Financial Date])
FROM YourTable GROUP BY Client)

- Steve Schapel, Microsoft Access MVP

 

[VLR]

(Sorry to butt in here, but I am trying to solve the same problem.)

I typed in the table and query in this example and get the following
results:

CLIENT FINANCIAL DATE SALES
Pets Inc 12/31/2002 500,000
Pets Inc 12/31/2001 600,000
Toy Factory 12/31/1999 1,000,000
Shoe Company 12/31/2001 1,500,000
Shoe Company 12/31/1999 1,450,000

here is a copy of the query

SELECT YourTable.client, YourTable.[financial date], YourTable.sales
FROM YourTable
WHERE (((YourTable.[financial date]) In (SELECT Max([Financial Date]) FROM
YourTable GROUP BY Client)));

thanks

Jeremy,

The SQL of this query will lok like this...

SELECT Client, [Financial Date], Sales FROM YourTable
WHERE [Financial Date] In(SELECT Max([Financial Date])
FROM YourTable GROUP BY Client)

- Steve Schapel, Microsoft Access MVP

Currently I have a query which pulls data similar to the following:

CLIENT FINANCIAL DATE SALES
Pets Inc 12/31/2002 500,000
Pets Inc 12/31/2001 600,000
Pets Inc 12/31/2000 300,000
Toy Factory 12/31/1999 1,000,000
Toy Factory 12/31/1998 800,000
Shoe Company 12/31/2001 1,500,000
Shoe Company 12/31/2000 1,200,000
Shoe Company 12/31/1999 1,450,000
Shoe Company 12/31/1998 1,600,000
Shoe Company 12/31/1997 1,550,000

I would like to add some criteria so that the query only pulled the

records associated with the most recent financial date. Therefore, there
would only be one row for each company as follows:

 

[Steve Schapel]

VLR and Jeremy,

Thank you very much, VLR, for this. And my humble apologies for an
incorrect reply previously. Here is an answer which I did check!...
1. Make a Totals Query based on the table like this...
SELECT Client, Max([Financial Date]) AS LatestDate
FROM YourTable
GROUP BY Client
2. Save this query, let's sat you name it LatestClientDate.
3. Make another query, which includes this first query, and also your
table, joined on both the client and financial date fields...
SELECT YourTable.Client, [Financial Date], Sales
FROM YourTable INNER JOIN LatestClientDate ON (YourTable.[Financial
Date] = LatestClientDate.[LatestDate]) AND (YourTable.Client =
LatestClientDate.Client)

- Steve Schapel, Microsoft Access MVP