Hi Mody,
Thank you for the reply. I tried to follow your instructions and used the MakeQueryTable. However, I am using the more complex query to get the max value (collectiondate) for a whole row with the group by subquery. The query was listed below. The attached file shows the invalid SQL Syntax. However, in MSSQL Server 201, the syntax works to get the whole row with the max date. I am not sure if the Expression in the MakeQueryTable could work wit the complex query. Could you share your opinions?
Many thanks,
Hank
SELECT
t.POINT_ID,
t.CollectionDate,
t.HistoricDate,
t.U,
t.LAT,
t.LONG,
t.WellDepth,
t.DataSource
FROM
(SELECT POINT_ID, MAX(CollectionDate) AS MaxValue
FROM
NM_Water_Quality.dbo.MASTER_UNION_Water_Quality
WHERE
(U IS NOT NULL) AND (U > 0)
GROUP BY POINT_ID) AS x INNER JOIN
NM_Water_Quality.dbo.MASTER_UNION_Water_Quality AS t ON x.POINT_ID = t.POINT_ID AND x.MaxValue = t.CollectionDate
WHERE
(t.U IS NOT NULL) AND (t.U > 0)