most recent/ top 1?? 1

[simon551]

I'm trying to pull records based on the most recent date for tblBondPrices.UpdateD and I've been trying to insert a Top 1 in there but it doesn't seem to work for me. Is there (another) way to pull Most Recent date on multiple records?
Thanks,
Simon

SELECT tblBonds.BondDesc, tblBonds.MatureD, tblPurch.PurchDate, [Par]*[PurchPrice]/[FxPurch]/100 AS Cost, [Par]*[Price]/[FxRt]/100 AS [Value], tblFxRates.FxRtDate, tblFxRates.FxRt, tblBondPrices.UpdateD, tblBondPrices.Price
FROM (tblFx INNER JOIN ((tblBonds INNER JOIN tblPurch ON tblBonds.BondID = tblPurch.BondID) INNER JOIN tblBondPrices ON tblBonds.BondID = tblBondPrices.BondID) ON tblFx.FxID = tblBonds.FxID) INNER JOIN tblFxRates ON tblFx.FxID = tblFxRates.FxID
WHERE (((tblFxRates.FxRtDate)=[Forms]![frmValuation].[txtDate]));

 

[PHV]

The TOP predicate is useless without an ORDER BY clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[simon551]

Do you think there is a better way to do it? I'm not having any success with the TOP predicate. I've been trying things like this:
SELECT tblBonds.*, tblFx.*, tblFxRates.*
FROM (tblFx INNER JOIN (tblBonds INNER JOIN (SELECT Top 1[UpdateD] FROM tblBondPrices ORDER By BondID) ON tblBonds.BondID =tblBondPrices.BondID) ON tblFx.FxID = tblBonds.FxID) INNER JOIN tblFxRates ON tblFx.FxID = tblFxRates.FxID;

But it's not working.
Thanks,
Simon

 

[PHV]

Something like this ?
SELECT B.BondDesc, B.MatureD, P.PurchDate, [Par]*[PurchPrice]/[FxPurch]/100 AS Cost
, [Par]*[Price]/[FxRt]/100 AS [Value], FR.FxRtDate, FR.FxRt, BP.UpdateD, BP.Price
FROM ((((tblFx AS F
INNER JOIN tblBonds AS B ON F.FxID = B.FxID)
INNER JOIN tblPurch AS P ON B.BondID = P.BondID)
INNER JOIN tblBondPrices AS BP ON B.BondID = BP.BondID)
INNER JOIN tblFxRates AS FR ON F.FxID = FR.FxID)
INNER JOIN (SELECT BondID, Max(UpdateD) AS LastD FROM tblBondPrices GROUP BY BondID) AS L
ON BP.BondID = L.BondID AND BP.UpdateD = L.LastD
WHERE FR.FxRtDate=[Forms]![frmValuation].[txtDate];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[simon551]

Max! You're the best PHV!