Queries not displaying correct information

[bknight]

Hans:
You have helped me with this issue and I thought both of us had it fixed, but alas it is not.

Please look at quOpenPositionsFirst It is a combination of a union query you designed for me and one of my own.

Driving the union query first part is quOpenPositionsLong

quOpenPositionsLong is made of quSumOpenPositions, Trdes, QuLatestSell

I added the quSumOpenPositions about a month ago, to display the correct records. Now hoever it is not displaying correctly.

SELECT Trades.Symbol, quSumOpenPositions.[Sum of Symbol], Sum(Trades.Amount) AS [Open Cost], Sum(Abs(Nz([amount])))/Sum(Nz([qtyfilled])) AS [Avg Cost]
FROM quSumOpenPositions LEFT JOIN (Trades LEFT JOIN quLatestSell ON Trades.Symbol = quLatestSell.Symbol) ON quSumOpenPositions.Symbol = Trades.Symbol
WHERE (((Trades.Filled)>#8/6/2015# And (Trades.Filled)>Nz([quLatestSell].[LastSell],#1/1/1900#)) AND ((Trades.Type)="Buy"))
GROUP BY Trades.Symbol, quSumOpenPositions.[Sum of Symbol]
HAVING (((quSumOpenPositions.[Sum of Symbol])>0))
ORDER BY Trades.Symbol;

If you run just quSumOpenPositions by itself the correct records and sums are displayed. But with the other two it isn't correct.

Could you suggest an alternative?

https://app.box.com/s/fjcvcz6gme2vhgpvbrolg4fii73lk4xz" onclick="window.open(this.href);return false;

[HansV]

I'll see if I can work my way through all those levels of queries...

[HansV]

I've taken a look, but it's not immediately clear to me what's wrong. Can you provide one or two specific examples where quOpenPositionsFirst returns an incorrect result, and explain what it should have been in your opinion?

[bknight]

OK, here you go.

[HansV]

Thanks!

For symbol BPT, the related value of Filled in Trades is 12/28/2016 09:08:15, and the related value of LastSell in quLatestSell is 01/06/2017 09:57:26.
You have a condition in quOpenPositionsLong that Filled must be greater than LastSell. This condition is not met for BPT, so it is not returned by quOpenPositionsLong.

[bknight]

Ok, the: And >Nz([quLatestSell].[LastSell],#1/1/1900#) messed me up expecting [filled]> etc.
But works for now
Thanks
BTW
On A short sell the buy date [Filled] would be necessarily > sell date [Filled]and that's probably why it was in there.