Learn how to a build a cloud-first strategyRegister Now

x
Solved

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

Posted on 2009-02-19
Medium Priority
259 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 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 23684685
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

ID: 23684983
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

ID: 31548898
Thanks!

tw
0

## Featured Post

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
###### Suggested Courses
Course of the Month21 days, 6 hours left to enroll