If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query with IF statements and lookups
Hi,
I'm trying to write a query and I'm not sure if it's even possible. I have two tables - one with sales data for the whole company and the other with monthly exchange rates for Canadian $ to US $. The problem with the sales table is that the Canada sales are mixed in with the US sales so I need to convert the Canadian sales to their US equivalent to be able to make an apples to apples comparison. There are three columns in the sales table that contain the date when the sale was made. CSBDATE is in 20010101 format, CSFYR is the year only and CSFPR is the month only. Is it possible for me to write a query that will do the following: IF [CSCO]=7 Or [CSCO]=8 THEN the Sale was made in Canada, ELSE, the sale was made in US or International. IF the sale was made in Canada THEN GET month and year of sale and find exchange rate for that month and year in exchange rate table. Lastly, multiply the CSBKD$ (sales $) that are in the sales table by the exchange rate. IF the sale was made in the US or International THEN return the CSBKD$ as is. Thank you in advance for any help. Michael |
#2
|
|||
|
|||
Query with IF statements and lookups
Here is how I laid out the tables and query --
Exch_Rate -- CSFYR CSFPR Rate 2008 1 1.1 2008 2 0.96 MichaelR CSBDATE CSFYR CSFPR CSCO CSBKD$ 20080101 2008 1 7 100 20080101 2008 1 8 110 20080201 2008 2 3 100 SELECT MichaelR.CSBDATE, IIf([CSCO] Between 7 And 8,[CSBKD$]*[Rate],[CSBKD$]) AS [Sale Value], MichaelR.[CSBKD$] FROM MichaelR LEFT JOIN Exch_Rate ON (MichaelR.CSFPR = Exch_Rate.CSFPR) AND (MichaelR.CSFYR = Exch_Rate.CSFYR); -- KARL DEWEY Build a little - Test a little "MichaelR" wrote: Hi, I'm trying to write a query and I'm not sure if it's even possible. I have two tables - one with sales data for the whole company and the other with monthly exchange rates for Canadian $ to US $. The problem with the sales table is that the Canada sales are mixed in with the US sales so I need to convert the Canadian sales to their US equivalent to be able to make an apples to apples comparison. There are three columns in the sales table that contain the date when the sale was made. CSBDATE is in 20010101 format, CSFYR is the year only and CSFPR is the month only. Is it possible for me to write a query that will do the following: IF [CSCO]=7 Or [CSCO]=8 THEN the Sale was made in Canada, ELSE, the sale was made in US or International. IF the sale was made in Canada THEN GET month and year of sale and find exchange rate for that month and year in exchange rate table. Lastly, multiply the CSBKD$ (sales $) that are in the sales table by the exchange rate. IF the sale was made in the US or International THEN return the CSBKD$ as is. Thank you in advance for any help. Michael |
#3
|
|||
|
|||
Query with IF statements and lookups
Karl,
The formula worked like a charm! Thanks a lot! Michael |
Thread Tools | |
Display Modes | |
|
|