Query to lookup and multiply by exchange rates

[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

 

[Steve Sanford]

Hi Michael,

It's not that hard... I just hope I can explain it well enough.

I don't know your field/table names, so you will have to change my example
to the proper names.

***First, in a standard module, paste in the following function.

Warning: this is air code!!!

'-------code beg----------------------
Function Can2US(CSBDAT As String, CSFYR As Integer, CSFPR As Integer, CSBKD
As Currency) As Currency
'
'CSBDATE is in 20010101 format,
'CSFYR is the year only and
'CSFPR is the month only.
'CSBKD is sales $ Canadian

'Returns: Sales in $ US


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim TheRate As Single
Dim SQL As String

Set db = CurrentDb

SQL = "SELECT ExchangeRate"
SQL = SQL & " FROM ExchangeRatesTable"
SQL = SQL & " WHERE YearOnly = " & CSFYR & " AND MonthOnly = " & CSFPR

Set rs = db.OpenRecordset(SQL)

TheRate = rs.Fields(0)

rs.Close
Set rs = Nothing
Set db = Nothing

Can2US = CSBKD * TheRate

End Function
'-----code end ------------

** Next create a query. Add the Sales table. Drag down the fields: CSBDATE,
CSFYR, CSFPR, CSBKD and any other fields you need.

In an empty column, paste in the following: (should be one line)

Equiv: IIf([CSCO]=7 Or
[CSCO]=8,Can2US([CSBDAT],[CSFYR],[CSFPR],[CSBKD]),[CSBKD])

** Save, then run the query. Find a Canadian sales and compare the amount to
the amount in the field "Equiv". Manually do the conversion to ensure the
function is converting properly.


You might want to add parameters to limit the date range for the query; ie
for the month of 4 and the year of 2007.

Clear as mud??? How did I do???

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

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