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
3
Medium Priority
?
259 Views
Last Modified: 2012-05-06
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"));

Open in new window

0
Comment
Question by:Tom Winslow
3 Comments
 
LVL 120

Accepted Solution

by:
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

by:harfang
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")

Open in new window

0
 

Author Closing Comment

by:Tom Winslow
ID: 31548898
Thanks!

tw
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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…

810 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