Query with IF statements and lookups

[MichaelR]

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

 

[KARL DEWEY]

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);

 

[MichaelR]

Karl,

The formula worked like a charm!

Thanks a lot!
Michael