Trying DLast function for first time 2

[simon551]

Hi,
I have a table with stock trades (many in a day) I want to pull the first and last trade on each day with corresponding price. Is it possible to do this all on one line so that I can then compute average change? I'm thinking Dlast is the way to go but can't get it to work.

Any help is appreciated:



LastPrice: DLast("Price","tblTrade","Hour = " & DMax("Hour",tblTrade"))

This is giving an error: invalid string

 

[Remou]

You seem to be missing a quote:

LastPrice: DLast("Price",[red]"[/red]tblTrade","Hour = " & DMax("Hour",tblTrade"))

But I don't quite understand. Surely the last trade is the trade that happened on Max of Hour? Wouldn't DlookUp suit?

 

[simon551]

not sure how to write a dLookup either.

 

[rjoubert]

http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[PHV]

not sure how to write a dLookup either
Perhaps like this ?
LastPrice: DLookUp("Price","tblTrade","Hour=#" & DMax("Hour",tblTrade) & "#")

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

 

[Remou]

No need to go so far:
DLookup Function Examples
faq705-4978

 

[simon551]

Oh. Thanks for the help. I must be doing something wrong. That returned the very first value in the whole table for the last price of each stock per day. I'm trying to get the last price for each day. That gives me the max price.

I had to change
& DMax("Hour",tblTrade) & "#")
to
& DMax("Hour","tblTrade") & "#")

is that the problem? I was getting an error.

 

[PHV]

What is your actual SQL code ?

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

 

[simon551]

SELECT tblTrade.StockSymbol, tblTrade.Date, tblTrade.Hour, DLookUp("Price","tblTrade","Hour=#" & DMax("Hour","tblTrade") & "#") AS LastPrice
FROM tblTrade
GROUP BY tblTrade.StockSymbol, tblTrade.Date, tblTrade.Hour;

 

[simon551]

Also tried this but it is returning multiple mindate entries in the Min2 query when it gets combined:

SELECT Broker.BrokerFirst, Broker.BrokerLast, Stock.CompanyName, Stock.StockSymbol, Max2.Date, Avg(([LastPrice]-[FirstPrice])/[FirstPrice]) AS Perc
FROM Broker INNER JOIN ((Stock INNER JOIN (SELECT max.StockSymbol, max.Date, TblTrade.Price AS LastPrice
FROM (SELECT TblTrade.StockSymbol, TblTrade.Date, Max(TblTrade.Hour) AS MaxOfHour
FROM TblTrade
GROUP BY TblTrade.StockSymbol, TblTrade.Date) AS Max
INNER JOIN TblTrade ON (max.MaxOfHour = TblTrade.Hour) AND (max.Date = TblTrade.Date) AND (max.StockSymbol = TblTrade.StockSymbol)) AS Max2 ON Stock.StockSymbol = Max2.StockSymbol) INNER JOIN (SELECT min.StockSymbol, min.Date, TblTrade.Price AS FirstPrice
FROM (SELECT TblTrade.StockSymbol, TblTrade.Date, Min(TblTrade.Hour) AS MinOfHour
FROM TblTrade
GROUP BY TblTrade.StockSymbol, TblTrade.Date) As Min
INNER JOIN TblTrade ON (min.MinOfHour = TblTrade.Hour) AND (min.Date = TblTrade.Date) AND (min.StockSymbol = TblTrade.StockSymbol)) AS Min2 ON Stock.StockSymbol = Min2.StockSymbol) ON Broker.BrokerID = Stock.BrokerID
GROUP BY Broker.BrokerFirst, Broker.BrokerLast, Stock.CompanyName, Stock.StockSymbol, Max2.Date
HAVING (((Avg(([LastPrice]-[FirstPrice])/[FirstPrice]))>0.05));

 

[simon551]

Can I change the response to your question PHV:

SELECT tblTrade.StockSymbol, tblTrade.Date, tblTrade.Hour, DLookUp("Price","tblTrade","Hour=#" & DMax("Hour","tblTrade") & "#") AS LastPrice
FROM tblTrade
GROUP BY tblTrade.StockSymbol, tblTrade.Date;

 

[PHV]

So, you wanted something like this ?
SELECT G.StockSymbol, G.Date, G.FirstHour, F.Price AS FirstPrice, G.LastHour, L.Price AS LastPrice
FROM ((
SELECT StockSymbol, [Date], Min([Hour]) AS FirstHour, Max([Hour]) AS LastHour
FROM tblTrade GROUP BY StockSymbol, [Date]
) AS G
INNER JOIN tblTrade AS F ON G.StockSymbol = F.StockSymbol AND G.Date = F.Date AND G.FirstHour = F.Hour)
INNER JOIN tblTrade AS L ON G.StockSymbol = L.StockSymbol AND G.Date = L.Date AND G.LastHour = L.Hour

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

 

[simon551]

you're the best!