Re: find record with max date less than or equal to today

[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
..

 

[John Spencer]

Yep it sure does need two comparisons in the join. Thanks for the backup

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

For the second one on John script, I think it needs one additional join
criteria:

SELECT T.Security, T.PriceDate, T.Price
FROM YourTable as T INNER JOIN
(SELECT Security, Max(PriceDate) AS [PriceDate]
FROM YourTable
WHERE PriceDate <= Date()
GROUP BY Security) as T2
ON T.Security = T2.Security
AND T.[PriceDate] = T2.[PriceDate]

Lucas

Thank you John, Your first suggestion works great, but the second one
just
returns all the records. I don't know why, the logic seems pretty
airtight.

 

[John Spencer]

Whoops! Missed something.

SELECT T.Security, T.PriceDate, T.Price
FROM YourTable as T INNER JOIN
(SELECT Security, Max(PriceDate) AS LatestDate
FROM YourTable
WHERE PriceDate <= Date()
GROUP BY Security) as T2
ON T.Security = T2.Security
AND T.[PriceDate] = T2.LatestDate

Note that the subquery in the FROM clause cannot have any square brackets in
it. If it does, Access will generate an error.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

For the second one on John script, I think it needs one additional join
criteria:

SELECT T.Security, T.PriceDate, T.Price
FROM YourTable as T INNER JOIN
(SELECT Security, Max(PriceDate) AS [PriceDate]
FROM YourTable
WHERE PriceDate <= Date()
GROUP BY Security) as T2
ON T.Security = T2.Security
AND T.[PriceDate] = T2.[PriceDate]

Lucas

Thank you John, Your first suggestion works great, but the second one
just
returns all the records. I don't know why, the logic seems pretty
airtight.

 

[Guest]

What I am trying to do is obtain the first [trans_dt] before
a certain date and the first [trans_dt] after that certain date. And in this
case below it is #1/31/2007#, but i also need to put the amount which is the
amount at the first [trans_dt] after that certain date (Next). This is what
i have so far. My problem is the field labeled "Prev" is all showing up false
in the IIf statement because my [trans_dt] is set to >#31/01/2007#, so its
showing up the [settle_dt] instead. How am I able to obtain both the Prev and
Next fields while showign the amount of the Next [trans_dt]

SELECT dbo_gltnrpt.deal_no AS [Deal No],
Max(IIf([trans_dt]<#1/31/2007#,[trans_dt],[settle_dt])) AS Prev,
dbo_gltnrpt.trans_dt As Next, dbo_gltnrpt.amount, dbo_deals.face_value AS
[Face Value], dbo_gltnrpt.entity, dbo_gltnrpt.coa_code AS [Coa Code]
FROM dbo_deals RIGHT JOIN dbo_gltnrpt ON dbo_deals.deal_no =
dbo_gltnrpt.deal_no
WHERE ((([dbo_gltnrpt.trans_dt])=(SELECT Min([trans_dt]) FROM dbo_gltnrpt as
Y WHERE Y.deal_no = dbo_gltnrpt.deal_no AND [trans_dt]>#1/31/2007#)) AND
((dbo_deals.settle_dt)<#2/1/2007#) AND ((dbo_deals.mature_dt)>#1/31/2007#)
AND ((dbo_gltnrpt.type)="a_int_bor") AND ((dbo_gltnrpt.reversed)="No"))
GROUP BY dbo_gltnrpt.deal_no, dbo_gltnrpt.trans_dt, dbo_gltnrpt.amount,
dbo_deals.face_value, dbo_gltnrpt.entity, dbo_gltnrpt.coa_code
HAVING (((dbo_gltnrpt.entity)='1061' Or (dbo_gltnrpt.entity)='1062' Or
(dbo_gltnrpt.entity)='1063' Or (dbo_gltnrpt.entity)='1064' Or
(dbo_gltnrpt.entity)='1065' Or (dbo_gltnrpt.entity)='1817' Or
(dbo_gltnrpt.entity)='1880' Or (dbo_gltnrpt.entity)='3968' Or
(dbo_gltnrpt.entity)='5756' Or (dbo_gltnrpt.entity)='6258' Or
(dbo_gltnrpt.entity)='6353' Or (dbo_gltnrpt.entity)='6734' Or
(dbo_gltnrpt.entity)='4020') AND ((dbo_gltnrpt.comments)<>"ADHOC INTEREST")
AND ((dbo_gltnrpt.coa_code)='7000FRN (3M)' Or (dbo_gltnrpt.coa_code)='7000FRN
(1M)' Or (dbo_gltnrpt.coa_code)='7000FRN (6M)') AND
((Min(IIf([trans_dt]>#1/31/2007#,[trans_dt],Null))) Is Not Null));