Looking for "intelligent" WHERE statement

[JapanFreak]

Hi there,

I wonder whether the following problem can be solved with a Query or if I have to adjust my database.

I want to take records of my stock market trades and among others I have created two tables: The first one, called tblTransactions, contains transaction-related data and the columns are Date, Ticker, Quantity and Price.

Based on this the following query gives me then for any requested date the actual portfolio holdings for any requested date, e.g. March 9, 2007:

SELECT T.Ticker, sum(T.Quantity)
FROM tblTransactions AS T
WHERE T.Date <=#3/09/2007#
GROUP BY T.Ticker
HAVING sum(T.Quantity)>0
ORDER BY T.Ticker;

Additionally, I have a table called tblStops with columns Date, Ticker and StopPrice. The function of the stop price is, that if the last price quoted for a stock is below that price the stock is sold immediately at the market. However, this table does only include a price if it is changed, i.e. a price for a certain ticker is valid as long as a new stop is entered at a later point of time.

Example
Date. Ticker. StopPrice
03/09/07 XY 27.23
04/01/07 XY 28.89
04/15/07 XY 30.56
04/30/07 XY 31.23

In a next step I want to have a query, which gives me for a certain point in time the actual portfolio positions and additionally the stop prices which were in place at that certain date.

If I take again March 9, 2007 the task is simple:

SELECT T.Ticker, sum(T.Quantity), X.Stop
FROM tblTransactions AS T INNER JOIN [SELECT P.Ticker, P.Stop FROM tblStops AS P WHERE P.Date=#3/09/2007#]. AS X ON T.Ticker=X.Ticker
WHERE T.Date <=#3/09/2007#
GROUP BY T.Ticker, X.Stop
HAVING sum(T.Quantity)>0
ORDER BY T.Ticker;

My question: Is it possible to write a query which "understands" that if there is no stop price for the requested date it has to look up the one which is in the nearest past before the requested date? That is, when requesting the portfolio composition for April 13, 2007 it should recognize that the valid stop price for stock XY is 28.89 (i.e. that of April 1, 2007, the date it was changed the last time before April 13).
Or do I have to adjust my database, i.e. take tblStops and fill up the dates and corresponding stop prices in between?

At the end of the day I want to request the data from Excel via VBA and ADO, i.e. an VBA solution would be helpful as well...

I hope I was clear enough and would be more than happy for any hint that could lead to a solution.

Best regards
JapanFreak

 

[Jon K]

Try this query:

SELECT T.Ticker, Sum(T.Quantity) AS SumOfQuantity,
(Select Top 1 [Stop] from [tblStops] where [Ticker]=T.[Ticker] and [Date]<=#4/13/2007# Order By [Date] desc) AS Stop
FROM tblTransactions AS T
WHERE Date<=#4/13/2007#
GROUP BY T.Ticker
HAVING Sum(Quantity)>0


Note:
Date is a reserved word. Since you would use VBA, it's better not to use it as field names.
.

 

[JapanFreak]

Problem solved

Thank you very much. This code works perfectly. I didn't know the TOP 1-syntax before...

Best,
JapanFreak