sql statement

[tsluu]

tblName
NameID Name
1 Mr A
2 Mr B
3 Mr C

tblTran
TranID NameID Date
1 1 01/01/2009
2 1 02/02/2009
3 2 01/01/2009
4 2 02/01/2009
5 2 03/01/2009

Result:
Name Date
Mr A 02/02/2009
Mr B 03/01/2009
Mr C

Can anyone help with the SQL query to give the above results. Is it
possible? I want all names in tblName and those with corresponding records
from tblTran, only the latest dates.

 

[Ken Sheridan]

Try this:

SELECT tblName.NameID, [Name],
MAX([Date]) AS LatestDate
FROM tblName LEFT JOIN tblTran
ON tblName.NameID = tblTran.NameID
GROUP BY tblName.NameID, [Name];

Note that the NameID should be included in the result table to cater for the
possibility of there being duplicate names in tblName.

BTW naming columns Name, Date etc should be avoided as the former is the
name of a built in property and the latter of a built in function in Access.
If you do use them be sure to wrap them in square brackets when referencing
the columns in queries or in code, but its best to use more specific terms
such as ClientName, TransactionDate.

Ken Sheridan
Stafford, England