Solved

union query conditional

Posted on 2012-03-25
5
652 Views
Last Modified: 2012-06-27
I am using following Union Query.
And its working like charm.

SELECT CM.CM_Date AS Cdate, CM.Cash_MemoNo AS CmNo,0 as BookDate, 0 as LR_No, CM.CM_Total as CMAmt, 0 as Amt_Rcvd, 0 as ChqDDNo, 0 as ChqDDdate, 0 as Acc_Typ, 0 as RcptNo,0 as MemoNo, 0 as M_Date, CM.CNee as conName
from (CashMemo as CM
INNER JOIN ClientLedger ON (ClientLedger.CName = CM.CNee))
UNION ALL SELECT Receipt_CLNT.Rcpt_Date, 0 as CmNo, 0 as BookDate, 0 as LR_No, 0 as CM_Total, Receipt_CLNT.Amt_Rcvd as RcptAmt,Receipt_CLNT.ChqDDNo as RefNo, Receipt_CLNT.ChqDDdate as RefDate, Receipt_CLNT.Amt_Mode as Mode, Receipt_CLNT.RcptNo as RcptNo, 0 as MemoNo, 0 as MDate, Receipt_CLNT.G_Name
from Receipt_CLNT
INNER JOIN ClientLedger ON (ClientLedger.CName = Receipt_CLNT.G_Name)
ORDER BY Cdate;

Open in new window


now a new field ( GrMod ) added in CashMemo table.
What i want is if GrMod = PAID then FROM CM_Total - CM.Freight else CM_Total
CM.Freight is not in this query but it is exist in CM table.
how can i do this..?
if GrMod = PAID then FROM CM_Total - CM.Freight else CM_Total
0
Comment
Question by:crystal_Tech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 250 total points
ID: 37762668
At the end of line 1 add

, iif(grmod="Paid", CM_Total - CM.Freight ,CM_Total ) as CMTotal

At the end of line 4 add

, Null as CMTotal

I see that you are not showing any fields from the ClientLedger  table; I assume it is there to limit the records from the other tables?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37762785
Looks good.  You could also pass both GrMod and CM.Freight into the report with NULLs from the other part and do the calculation there.

mlmcc
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 37762854
@peter57r
Thanks its working.

@Michael
which way is best for speed,
calculation from report
or filter from Union Query..?
Please suggest me.
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 37762887
generally it will be faster from the SQL.

Particularly in this case since only part of the data needs the calculation

Another considerationis you are passing less data to the report so the network traffic will be less.

In this case, you probably won't notice much difference unless you have 10,000 or 100,000 records.  Not sure you would even really notice the difference then.

mlmcc
0
 
LVL 1

Author Closing Comment

by:crystal_Tech
ID: 37762919
Thanks for Quick Reply..
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question