Hi,

I'm trying to get a query that returns buy and sell orders that were made at the same time and at the same price (to see if someone is trying to 'fix' the price).
I've got two tables - one table is named 'Trades' and holds trades information (ticker, price, time, account number, etc.), the other table, 'accounts', holds account information (account number, account name, account manger, etc)
My first query returned all buy and sell trades that were made at the same time:
Original Query:
Code:
SELECT DISTINCT trades.*
FROM trades INNER JOIN trades AS Tmp ON trades.Ticker = Tmp.Ticker WHERE (((trades.Ticker)=Tmp.Ticker) And ((trades.DateOf)=Tmp.DateOf)
 And ((trades.timeOf)=tmp.timeOf) And ((trades.Quantity)<0) And ((Tmp.Quantity)>0) And And ((trades.Price)=Tmp.Price) And ((accounts.codeHafrada)=1))
 Or (((trades.Ticker)=Tmp.Ticker) And ((trades.DateOf)=Tmp.DateOf) And ((trades.timeOf)=[tmp.timeOf]) And ((trades.Kamut)>0) And 
((Tmp.Kamut)<0 And ((trades.Price)=Tmp. Price))
ORDER BY trades.Ticker, trades.DateOf, trades.timeOf;
Result:


HTML Code:
Tick   Time  Qty Prc CodeOf Acc
Bla 10:20AM  -50 120 1      12
Bla 10:20AM   70 120 0      15
Bla 11:30AM  200 140 1      24
Bla 11:30AM -180 140 1      44

However, I want to narrow it down so my query will return buy and sell orders just from specified accounts (where the field codeof=1)

Modified Query:

Code:
SELECT DISTINCT trades.*
FROM accounts INNER JOIN (trades INNER JOIN trades AS Tmp ON trades.Ticker = Tmp.Ticker) ON accounts.AccountNum = trades. AccountNum
WHERE (((trades.Ticker)=Tmp.Ticker) And ((trades.DateOf)=Tmp.DateOf) And ((trades.timeOf)=tmp.timeOf) And ((trades.Kamut)<0) And ((Tmp.Kamut)>0)
 And ((trades.TimeOfriska)=Tmp.TimeOfriska) And ((accounts.codeOf)=1)) Or (((trades.Ticker)=Tmp.Ticker) And ((trades.DateOf)=Tmp.DateOf) And
 ((trades.timeOf)=[tmp.timeOf]) And ((trades.Quantity)>0) And ((Tmp.Quantity)<0) And ((trades.Price)=Tmp.Price) And ((accounts.codeOf)=1))
ORDER BY trades.Ticker, trades.DateOf, trades.timeOf;
The problem with the modified query is that although it omits trades with accounts , it leaves the other side of the trade (the sell in this example):

HTML Code:
Tick   Time  Qty Prc CodeOf Acc
Bla 10:20AM  -50 120 1      12
Bla 11:30AM  200 140 1      24
Bla 11:30AM -180 140 1      44
But I want it to return just buy and sell where both accounts have 'codeof=1'.... like this:

HTML Code:
Tick   Time  Qty Prc CodeOf Acc
Bla 11:30AM  200 140 1      24
Bla 11:30AM -180 140 1      44

So I need to somehow to select first just trades with accounts that have codeof=1 and then select the trades that have the same time and price....

But how do I do that?

Thanks in advance