J
John Spencer
SELECT Security, PriceDate, Price
FROM YourTable
WHERE PriceDate =
(SELECT Max(PriceDate)
FROM YourTable as Y
WHERE Y.Security = YourTable.Security
AND PriceDate <= Date())
You could also use a sub-query embedded in the FROM clause
SELECT T.Security, T.PriceDate, T.Price
FROM YourTable as T INNER JOIN
(SELECT Security, Max(PriceDate)
FROM YourTable
WHERE PriceDate <= Date()
GROUP BY Security) as T2
ON T.Security = T2.Security
AN easy way to construct this type of query, if you are using two queries is
to build both and then switch to SQL view of the first query and copy the
SQL.
Next, open the second query in SQL view and find the reference to the query
in the from Clause (something like)
FROM Table INNER JOIN MaxDateQuery
Insert a set of parentheses and AS before MaxDateQuery
FROM Table INNER JOIN () AS MaxDateQuery
Insert the cursor between the parentheses and paste the SQL of the first
query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
FROM YourTable
WHERE PriceDate =
(SELECT Max(PriceDate)
FROM YourTable as Y
WHERE Y.Security = YourTable.Security
AND PriceDate <= Date())
You could also use a sub-query embedded in the FROM clause
SELECT T.Security, T.PriceDate, T.Price
FROM YourTable as T INNER JOIN
(SELECT Security, Max(PriceDate)
FROM YourTable
WHERE PriceDate <= Date()
GROUP BY Security) as T2
ON T.Security = T2.Security
AN easy way to construct this type of query, if you are using two queries is
to build both and then switch to SQL view of the first query and copy the
SQL.
Next, open the second query in SQL view and find the reference to the query
in the from Clause (something like)
FROM Table INNER JOIN MaxDateQuery
Insert a set of parentheses and AS before MaxDateQuery
FROM Table INNER JOIN () AS MaxDateQuery
Insert the cursor between the parentheses and paste the SQL of the first
query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..