Improper SQL query?

[JapanFreak]

Hi there,

I have a problem with an SQL query, which should combine data out of three different tables (tblTransactions, tblTradedSecurities, tblStopps, tblQuotes) using a combination of Inner-joins and sub-queries. However, I get an error message, which tells me that this is an improper SQL query and that Access rather expects a "Delete, Insert, Select or Update"... what does this exactly mean, what is wrong with my query?

SELECT X.Ticker, S.Name, sum(X.Qty) AS TotQty, X.qCl, (SELECT Top 1 [Stop] FROM [tblStopps] WHERE [Date]<=#3/09/2007# AND X.Ticker=tblStopps.Ticker ORDER BY [Date] DESC) AS Stop
FROM [tblTransactions AS T INNER JOIN tblQuotes AS Q ON T.Ticker=Q.qTicker]. AS X INNER JOIN tblTradedSecurities AS S ON X.Ticker=S.Ticker
WHERE X.Date<=#3/9/2007# AND X.qDate=#3/09/07#
GROUP BY X.Ticker, S.Name, X.qCl
HAVING sum(X.Qty)>0
ORDER BY X.Ticker;

I would be very happy for any hint.

Best regards,
JapanFreak

 

[KeithG]

I think your subquery is the problem. First I would get ride of the Order by clause because the subquery can only return 1 record. Next I would change the subquery to a totals query and use Max(Stop). Then in the where clause of the subquery reference the field from tblStopps before the value it needs to equal. So your subquery should look similar to below

Select Max(Stop) from tblStopps Where [Date]=#3/09/2007# and tblStopps.Ticker=x.Ticker as Stops

 

[JapanFreak]

Keith,

thank you very much for your reply. I made the changes you suggested, however unfortunately it still does not work and the error message remains the same.
Regarding the subquery: I used a "Top 1"-query because tblStopps does not contain data for each and every date but only for dates where the value changed. That is, if the request is for a data in between such changes the query should then select the value in the most recent past relative to the requested date. I'm not sure whether the max(stop) query would help here...

SELECT X.Ticker, S.Name, sum(X.Qty) AS TotQty, X.qCl, (SELECT max(Stop) FROM [tblStopps] WHERE [Date]=#3/09/2007# AND tblStopps.Ticker=X.Ticker) AS Stops
FROM [tblTransactions AS T INNER JOIN tblQuotes AS Q ON T.Ticker=Q.qTicker]. AS X INNER JOIN tblTradedSecurities AS S ON X.Ticker=S.Ticker
WHERE X.Date<=#3/9/2007# AND X.qDate=#3/09/07#
GROUP BY X.Ticker, S.Name, X.qCl
HAVING sum(X.Qty)>0
ORDER BY X.Ticker;

Best regards,
JapanFreak

 

[KeithG]

Can you post a copy of the db? I would like to have a look

 

[JapanFreak]

Sample attached

Hi Keith,

thank you very much for your help, I really appreciate that. Attached please find the database with some sample data. I have already changed some field names so that I do not use reserved words like “Stop” any longer. However, it still does not work. The problem exists with the query qryHistoricalPortfolio.
Additionally, please find the query qryPortfolioHoldingsWithStops (credit goes to Jon_K -> http://www.access-programmers.co.uk/forums/showthread.php?t=134670), which shows how I use the Top 1-query in order to find the stops for any requested data.
Just as a remark: I know that would make more sense to combine tblStops and tblQuotes into one table. However, I have to keep them separate as they have different external sources.

Once again thank you very much for your help.

Best regards,
JapanFreak

 

[ByteMyzer]

First of all, because you enclosed text within [ ]. does not make it a subquery. What you enclosed was part of a Join Expression.

I'm not quite certain what you are attempting to accomplish with your query, but you might try the following SQL:

SELECT T.Ticker, S.Name, Sum(T.Qty) AS TotQty, Q.qCl, T3.qStop
FROM ((tblTransactions AS T
INNER JOIN tblQuotes AS Q ON T.Ticker=Q.qTicker)
INNER JOIN tblTradedSecurities AS S ON T.Ticker=S.Ticker)
INNER JOIN
 (SELECT T1.Ticker, T1.qStop
  FROM tblStops T1
  WHERE T1.Date=
   (SELECT MAX(T2.Date)
    FROM tblStops T2
    WHERE T2.Ticker=T1.Ticker
    AND T2.Date<=#3/9/2007#
   )
 ) T3 ON T.Ticker=T3.Ticker
GROUP BY T.Ticker, S.Name, Q.qCl, T3.qStop
HAVING sum(T.Qty)>0
ORDER BY T.Ticker;

 

[JapanFreak]

Not yet there...

Thank you very much for your contribution.

Some background re my problem:
- tblTransactions contains security transactions: Date of the transaction, ticker (the identifier of a stock), number of stocks traded (Qty), price (Entry)
- tblTradedSecurities contains information of the stocks traded: ticker (identifier of a stock), name, country, sector etc.
- tblQuotes contains stock prices for each date and ticker
- tblStops contains stop prices for each ticker and date when it is changed (stops are price levels, which trigger a sell order when the stock price falls under that stop limit)

Objective:
I want to have for any date requested by the user (e.g., 03/16/07) a query, which tells me the portfolio composition on that date (i.e. the stocks / ticker, which were in the portfolio on that day, i.e. sum(Qty)>0) + name of each stock + price for each stock + stop for each stock
At the end of the day the query should include several other columns, however I've limited it to one column per table in order to keep my question as simple as possible.

I have included your suggestion in the file attached (tblHistoricalPortfolio_2 - it works without any error message, however it delivers stock prices qCl not for a specific date but for the whole time period included in tblQuotes. Any changes by myself only produce error messages...

Thank you for your help.

Best regards,
JapanFreak

 

[ByteMyzer]

I'm afraid your requirement is still somewhat obscure, at least to me. Perhaps you could provide some sample dummy input/output data in an Excel spreadsheet to illustrate what you are trying to accomplish.

 

[JapanFreak]

My objective

I am sorry... The attachment above does also contain a query called qryPortfolioHoldingsWithStops. It is based on the following SQL code:

SELECT T.Ticker, sum(T.Qty) AS SumOfQuantity, (SELECT Top 1 [qStop] FROM [tblStops] WHERE [Date]<=#3/09/2007# AND T.Ticker=tblStops.Ticker ORDER BY [Date] DESC) AS qStop
FROM tblTransactions AS T
WHERE T.Date<=#3/9/2007#
GROUP BY T.Ticker;

This query gives the portfolio holdings as per 3/09/2007. That is, the query takes the table tblTransactions and adds until the date given (here 3/09/2007) the quantities traded (Qty) and lists them as long as the sum delivers a number > 0 (= 0 means that the stock has been sold and is no longer part of the portfolio).
Additionally, the query gives me the stop price which was valid on 3/09/2007.

What I want to do now is to expand this query by including the column Name (the data must come from tblTradedSecurities) and the current stock price qCl on 3/09/2007 (the data must come from tblQuotes). That is, the result should then be:

Ticker --- Name --- SumOfQuantity --- qStop --- qCl
ADBL --- Audible Inc. --- 30 --- 7.89 --- 10.51
BRCD --- Brocade Communications Sys --- 75 --- 7.99 --- 9.56
GT --- Goodyear Tire & Rubber Co --- 30 --- 23.79 --- 28.51

I hope it has become somewhat clearer what I am looking for...

Best regards,
JapanFreak

 

[JapanFreak]

Problem solved.

Hi there,

I finally found the solution by including a WHERE condition in ByteMyzer's code:

SELECT T.Ticker, S.Name, Sum(T.Qty) AS TotQty, Q.qCl, T3.qStop
FROM ((tblTransactions AS T
INNER JOIN tblQuotes AS Q ON T.Ticker=Q.qTicker)
INNER JOIN tblTradedSecurities AS S ON T.Ticker=S.Ticker)
INNER JOIN
 (SELECT T1.Ticker, T1.qStop
  FROM tblStops T1
  WHERE T1.Date=
   (SELECT MAX(T2.Date)
    FROM tblStops T2
    WHERE T2.Ticker=T1.Ticker
    AND T2.Date<=#3/9/2007#
   )
 ) T3 ON T.Ticker=T3.Ticker
WHERE Q.qDate=#3/9/2007#
GROUP BY T.Ticker, S.Name, Q.qCl, T3.qStop
HAVING sum(T.Qty)>0
ORDER BY T.Ticker;

Thanks to everybody who helped me with regards to that question.

Best,
JapanFreak