Error Message on Query 1

[azhutch]

Can Anyone help me with finding where I have made the following error "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." in the query statement:

SELECT a.Ticker AS Expr1, a.CoName AS Expr2, (Sum(PQuantityBefore)-Sum(SQuantityBefore)) AS BalanceBefore, Sum(a.QuantityPurchased) AS QuantityPurchased, Sum(a.QuantitySold) AS QuantitySold, (Sum(PQuantityBefore)-Sum(SQuantityBefore)+Sum(QuantityPurchased)-Sum(QuantitySold)) AS Balance
FROM (SELECT Ticker, CompanyName, Quantity AS pt.PQuantityBefore, 0 AS pt.SQuantityBefore, 0 AS QuantityPurchased, 0 AS pt.QuantitySold, pt.TradeDate FROM qryTransOpen pt
WHERE pt.TradeDate < [STARTDATE ]
UNION ALL
SELECT Ticker, CoName, 0 AS st.PQuantityBefore, Quantity AS SQuantityBefore, 0 AS st.QuantityPurchased, 0 AS st.QuantitySold, TradeDate FROM qryTransClose st
WHERE st.TradeDate < [STARTDATE
UNION ALL
SELECT Ticker, CoName, 0 AS pt.PQuantityBefore, 0 AS pt.SQuantityBefore, pt.Quantity AS QuantityPurchased, 0 AS pt.QuantitySold FROM qryTransOpen pt
WHERE pt.TradeDate BETWEEN [STARTDATE] AND [ENDDATE]
UNION ALL
SELECT Ticker, CompanyName, 0 AS st.PQuantityBefore, 0 AS st.SQuantityBefore,
0 AS st.QuantityPurchased, Quantity AS QuantitySold FROM qryTransClose st
) AS a
WHERE ((([st].[TradeDate]) Between [STARTDATE] And [ENDDATE]))
GROUP BY a.Ticker, a.CoName;

Any help would be greatly appreciated.

 

[SkipVought]

hi,

I think that your aliases need to be literals. This seems to be a problem, AFAIK...

Select .....
, Quantity AS pt.PQuantityBefore
, 0 AS pt.SQuantityBefore
, 0 AS QuantityPurchased
, 0 AS pt.QuantitySold
....
FROM qryTransOpen pt

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[azhutch]

Skip: thank you. I am trying to set these to zero. Can you help me with the code to do that?

 

[SkipVought]

lets just look at ONE of your sub queries...

SELECT
  Ticker
, CompanyName
, Quantity AS pt.PQuantityBefore
, 0 AS pt.SQuantityBefore
, 0 AS QuantityPurchased
, 0 AS pt.QuantitySold
, pt.TradeDate  
FROM qryTransOpen pt 
WHERE pt.TradeDate < [STARTDATE ]

Can you paste the code in your SQL editor and run?

In what table is Ticker, CompanyName & Quantity?

What are you trying to do?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[azhutch]

Sorry for the delay. I replaced my SQL for the first "pt SELECT" statement with your suggested SQL. It returned "Syntax Error in Union Query

 

[SkipVought]

I did not suggest anything. I merely posted a portion of your SQL with relevant questions.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[azhutch]

Skip: Not a problem I made a copy of the query and replaced the SQL to see if it would work. Both Ticker and CoName are fields in the tblTransactions table. I am trying to get a FIFO inventory of open security positions which I will then use to calculate realized gains/ or losses and also an open position unrealized gains / losses. Would you like to see a sample of the database?

 

[azhutch]

I still cant get the query to work. Can anyone Help?

 

[PHV]

What about this ?

SELECT Ticker, CoName, Sum(PQuantityBefore-SQuantityBefore) AS BalanceBefore
, Sum(Quantity_Purchased) AS QuantityPurchased, Sum(Quantity_Sold) AS QuantitySold
, Sum(PQuantityBefore-SQuantityBefore+Quantity_Purchased-Quantity_Sold) AS Balance
FROM (SELECT Ticker, CompanyName AS CoName, Quantity AS PQuantityBefore
, 0 AS SQuantityBefore, 0 AS Quantity_Purchased, 0 AS Quantity_Sold
 FROM qryTransOpen WHERE TradeDate < [STARTDATE]
UNION ALL SELECT Ticker, CompanyName, 0, Quantity, 0, 0
 FROM qryTransClose WHERE TradeDate < [STARTDATE]
UNION ALL SELECT Ticker, CompanyName, 0, 0, Quantity, 0
 FROM qryTransOpen WHERE TradeDate BETWEEN [STARTDATE] And [ENDDATE]
UNION ALL SELECT Ticker, CompanyName, 0, 0, 0, Quantity
 FROM qryTransClose WHERE TradeDate BETWEEN [STARTDATE] And [ENDDATE]
)  AS a
GROUP BY Ticker, CoName

Note: you have to know if the queries return CompanyName or CoName ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[azhutch]

PH: Thankyou so very much for your suggestion. I changed all CompanyName references to CoName and adjusted the math to work for "negative" open positions and it works!!!! Thank you again - Azhutch