adding to union query

[rudwan]

hi
i have this tow queries :
SELECT countofProductID,sumofaccrudamnt
FROM FileOfTransferDistributed as Query1

SELECT code , Actual
FROM FileOfTransferDistributed as query2
then i wld like to join it all as :

SELECT Code, Actual
FROM query2
UNION SELECT countofProductID,sumofaccrudamnt
FROM Query1

until here is ok
now , there is one a specific amount Amt , must be join
also to this union query , but by provision ,
if query1.sumofaccrudamnt>0 then , add amt to it
otherwise , add it to query2.actual
how i could do it

 

[Brendan Reynolds]

Should be possible with an IIf() expression, something like ...

SELECT tblTest1.TestNum, tblTest1.TestText,
IIf([tblTest1].[TestNum]=0,[tblTest1].[TestDate],Null)
FROM tblTest1
UNION SELECT tblTest2.TestNum, tblTest2.TestText,
IIf([tblTest2].[TestNum]=0, Null, [tblTest2].[TestDate])
FROM tblTest2;

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.