Help with group by query in access

[PankajBanga]

My data look something like this

CompanyKey Date Status Price Price_Dil
1 01/12/1997 H 1.40 1.38
1 26/08/1998 N 0.81 0.80
1 12/02/2004 H 1.54 1.54
1 11/08/2004 N 1.41 1.41
4 17/03/2000 H 1.30 6.50
4 21/09/2000 N 0.52 2.60
5 01/12/1997 U 1.13 1.11
5 02/03/2004 U 1.78 1.78
9 01/12/1997 U 9.85 9.85
9 28/08/1998 N 9.77 9.77
9 12/03/2003 U 10.81 10.81
9 11/08/2004 N 12.67 12.67
11 04/03/2003 U 2.24 2.24
11 31/03/2003 N 2.42 2.42
11 23/02/2004 H 3.61 3.61
11 07/09/2004 N 3.75 3.75
12 01/04/1998 H 0.07 0.07
12 07/12/1998 H 0.12 0.12
12 02/03/2000 H 0.45 0.45
12 20/09/2000 H 0.19 0.19
13 30/12/2003 U 3.98 3.95
13 01/07/2003 N 5.05 5.01
16 17/08/2000 U 5.60 5.60
16 16/02/2001 N 5.67 5.67

I want most recent row (by date) for each CompanyKey where Status is H. for eg.

CompanyKey Date Status Price Price_Dil
1 12/02/2004 H 1.54 1.54
4 17/03/2000 H 1.30 6.50
11 23/02/2004 H 3.61 3.61
12 20/09/2000 H 0.19 0.19


I have written the query below, but not getting appropriate results

SELECT
TableName.CompanyKey,
Max(TableName.Date) AS MaxOfDate,
Last(TableName.Status) AS LastOfStatus,
Last(TableName.Price) AS LastOfPrice,
Last(TableName.Price_Dil) AS LastOfPrice_Dil
FROM TableName
WHERE ((TableName.Status) Like "H")
GROUP BY TableName.CompanyKey

Please help. Thanks

 

[Jon K]

Last() arbitrarily returns one record which may not be the one that you intended. The following is taken from Access's help file:-

The First and Last functions are analogous to the MoveFirst and MoveLast methods of a DAO Recordset object. They simply return the value of a specified field in the first or last record, respectively, of the result set returned by a query. Because records are usually returned in no particular order (unless the query includes an ORDER BY clause), the records returned by these functions will be arbitrary.

To ensure that the correct records are returned, you can use a Totals Query to select the Max() dates and, in a second query, join the Totals Query back to the data table to retrieve the correct records.

As an illustration, I have attached a sample database. You can run the second query in it.
.

 

[PankajBanga]

Thanks Jon K.

Out of curiosity, is there anyway of achieving similar result with one query rather than using two queries.

 

[Jon K]

SELECT TableName.CompanyKey, TableName.Date, TableName.Status, TableName.Price, TableName.Price_Dil
FROM TableName INNER JOIN
(SELECT [TableName].[CompanyKey], [TableName].[Status], Max([TableName].[Date]) AS MaxOfDate
FROM TableName
GROUP BY [TableName].[CompanyKey], [TableName].[Status]
HAVING TableName.Status="H") as S
ON (TableName.Status = S.Status) AND (TableName.Date = S.MaxOfDate) AND (TableName.CompanyKey = S.CompanyKey);


There are some limitations:

Unlike the two-query approach in which you can build the queries in Design View, you have to directly type the SQL statement in query SQL View.

When the statement is saved, Access may put the subquery in a pair of Square Brackets. If you subsequently edit the query in SQL View, Access may treat the Square Brackets as syntax errors.

The SQL statement is not supported in Access 97.
.

 

[PankajBanga]

Thanks a lot for helping out.