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.

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Possibly this:

SELECT N.[name], T.[date]
FROM tblName As N LEFT JOIN tblTran As T ON T.nameID = N.nameID
WHERE T.[date] = (SELECT MAX([date]) FROM tblTran
WHERE nameID = N.nameID)
OR T.[date] IS NULL
ORDER BY T.[date], N.[name]

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSfJgSIechKqOuFEgEQJOmACg4PivF5gO+Ot5YyY2FG0g06dxWB0An118
pn1WTnRM6QiLYBig5Wd5arbC
=3Dt7
-----END PGP SIGNATURE-----

 

[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