Query problem..plz help

[r_p]

Hi,


MS Access database like this,

SCRIP_CODE SCRIP_NAME CLIENT_CODE QTY B_S
---------- ---------- ----------- ---------- ----------
500100 TISCO 47198 1000 BUY
500100 TISCO 47198 1000 SELL
500101 RELCAPITAL 47198 1000 BUY
500101 RELCAPITAL 47198 1000 SELL
500102 RELIANCE 47198 1000 BUY
500103 SATYAM 47198 2000 BUY


select scrip_code, scrip_name, client_code,
(max(decode(b_s,'BUY',qty,0)) - max(decode(b_s,'SELL',qty,0)) net)
from daily_trans
group by (scrip_code, scrip_name, client_code)
having (max(decode(b_s,'BUY',qty,0))
- max(decode(b_s,'SELL',qty,0))) <> 0

This query retreiving the rows in Oracle SQL as,

SCRIP_CODE SCRIP_NAME CLIENT_CODE NET
---------- ---------- ----------- ----------
500102 RELIANCE 47198 1000
500103 SATYAM 47198 2000


But when I m trying to run this query in Microsoft Query giving error,

"Syntax error (Missing operator) in query expression
'(max(decode(b_s,'BUY',qty,0)) - max(decode(b_s,'SELL',qty,0)) net)'. "

How can i solve this problem? I am trying to write an application in VB and MS Access.

Thanks
Rathish

 

[Jon K]

SQL statement in Access:

SELECT [scrip_code], [scrip_name], [client_code], max(IIf([b_s]='BUY',[qty],0))-max(IIf([b_s]='SELL',[qty],0)) AS net
FROM daily_trans
GROUP BY [scrip_code], [scrip_name], [client_code]
HAVING max(IIf(b_s='BUY',qty,0)) - max(IIf(b_s='SELL',qty,0)) <> 0;
.