Solved

# I need to combine two (2) statements in an Access Query.

Posted on 2009-02-19
248 Views
I need to combine two (2) statements in an Access Query.

My currency codes are RPCRCD = "RUP" for India and RPCRCD = "EUR for Euros.

My conversion factors for EUR & RUP are in table tblF0015RUP2USD2 where CXCRCD = the currency and CXCRR = the conversion factor.

CXCRCD      CXCRR
RUP      0.020375
EUR      1.466276

If CXCRCD is RUP, I need to multiply RPAAP (my invoice amount) by CXCRR (0.020375 from the RUP row). But, if CXCRCD is EUR, I need to multiply RPAAP (my invoice amount) by CXCRR (1.466276 from the EUR row).

In my query, qappJDESatcomData, I have the following statement.

Invoice_Amt: IIf (([RPCRCD]="RUP" And [CXCRCD]="RUP"),(([RPAAP]/100)*[CXCRR]),(([RPAAP]/100)))

This works for RUP (Rupees)  ONLY WHEN THE TABLE HAS A SINGLE LINE FOR RUP.

I need to add Euros incorporate into a single statement. Below is the EUR statement.

Invoice_Amt: IIf (([RPCRCD]="EUR" and [CXCRCD]="EUR"),(([RPAAP]/100)* [CXCRR]),(([RPAAP]/100)))

When CXCRCD = RUP I need to multiply CXCRR by 0.020375 and when CXCRCD = EUR I need to multiply CXCRR by 1.466276.

Tw

``````SELECT PRODDTA_F03B11.RPAN8 AS Customer, IIf(([RPCRCD]="EUR" And [CXCRCD]="EUR"),(([RPAAP]/100)*[CXCRR]),(([RPAAP]/100))) AS Invoice_Amt, PRODDTA_F03B11.RPDOC AS Invoice, ConvertJulian([RPDIVJ]) AS Invoice_Date, Right([RPKCO],3) AS Company, PRODDTA_F03B11.RPDDJ, PRODDTA_F03B11.RPVR01, PRODDTA_F03B11.RPBCRC, PRODDTA_F03B11.RPCRRM, PRODDTA_F03B11.RPCRCD, PRODDTA_F03B11.RPCRR, PRODDTA_F03B11.RPACR

FROM PRODDTA_F03B11, tblF0015EURUP2USD2

WHERE (((IIf(([RPCRCD]="EUR" And [CXCRCD]="EUR"),(([RPAAP]/100)*[CXCRR]),(([RPAAP]/100))))<>0) AND ((Right([RPKCO],3))=232 Or (Right([RPKCO],3))=241 Or (Right([RPKCO],3))=242 Or (Right([RPKCO],3))=311 Or (Right([RPKCO],3))=911 Or (Right([RPKCO],3))=318 Or (Right([RPKCO],3))=320 Or (Right([RPKCO],3))=216 Or (Right([RPKCO],3))=217) AND ((PRODDTA_F03B11.RPPST)="A") AND ((PRODDTA_F03B11.RPDCT)="RI" Or (PRODDTA_F03B11.RPDCT)="RU" Or (PRODDTA_F03B11.RPDCT)="RM"));
``````
0
Question by:Tom Winslow

LVL 119

Accepted Solution

test this

IIf([RPCRCD]="EUR" And [CXCRCD]="EUR",(([RPAAP]/100)*[CXCRR]),
IIf (([RPCRCD]="RUP" And [CXCRCD]="RUP"),(([RPAAP]/100)*[CXCRR]) ,(([RPAAP]/100)))) AS Invoice_Amt

0

LVL 58

Expert Comment

I think you need to join your tables; then the expression becomes trivial. I made some other changes to your query (notably the In() operator for your criteria), but the central problem is that were working with a so-called "Cartesian product" of all possible combinations of rows from both tables.

(°v°)
``````SELECT

P.RPAN8 AS Customer,

P.RPAAP/100*F.CXCRR AS Invoice_Amt,

P.RPDOC AS Invoice,

ConvertJulian(P.RPDIVJ) AS Invoice_Date,

Right(P.RPKCO,3) AS Company,

P.RPDDJ,

P.RPVR01,

P.RPBCRC,

P.RPCRRM,

P.RPCRCD,

P.RPCRR,

P.RPACR

FROM

PRODDTA_F03B11 P

INNER JOIN tblF0015EURUP2USD2 F ON P.RPCRCD = F.CXCRCD

WHERE P.RPAAP<>0

AND Right(P.RPKCO,3) In (232,241,242,311,911,318,320,216,217)

AND P.RPPST="A"

AND P.RPDCT In ("RI","RU","RM")
``````
0

Author Closing Comment

Thanks!

tw
0