Two tables, multiple relationships?

[jg70124]

I have a table of orders. Some of the fields are:
- Period Code
- Quantity
- Sold Currency (the local currency where the item is sold)
- Sale price in local currency
- Cost Currency (the local currency where the item was made)
- Manufacturing cost in local currency

Then I have a table of exchange rates, whose fields are:
- Period Code
- Currency
- Conversion to Euro

I would like to create a query showing all orders with sales and cost
in Euros. But its possible two have two different currencies for each
order (one for cost, one for sales). I've tried create the query with
two instances of the Exchange Rate table, and also with one instance
of the Exchange Rate table but with two sets of relationships, but in
each case the query hangs.

What is the most efficient way to design the query and/or
relationships?

 

[John Spencer]

You could try using subqueries

SELECT O.*
, (SELECT First([Conversion to Euro]
FROM ExchangeTable as E
WHERE E.[Period Code] = O.[Period Code]
AND E.Currency = O.[Sold Currency]) * [Sale Price in Local Currency] as EuroSale
, , (SELECT First([Conversion to Euro]
FROM ExchangeTable as E
WHERE E.[Period Code] = O.[Period Code]
AND E.Currency = O.[Cost Currency]) * [Manufacturing Cost in Local Currency]
as EuroCost
FROM ORDERSTable as O

Or use the DLookup Function
DLookUp("[Conversion to Euro]","ExchangeTable","[Period Code]='" & [Period
Code] & "' and Currency='" & [Sold Currency] &"'")

You should be able to do that all in a query, but I posted the prior solutions
first, since you said that the query was hanging. A query would look like the
following. I assume that you have indexes on the fields used in the joins.
If not, add them to increase the speed of all these solutions.

SELECT O.*
, E1.[Conversion to Euro] as SoldConversion
, E2.[Conversion to Euro] as CostConversion
FROM 9ORDERSTable as O LEFT JOIN ExchangeTable as E1
On E1.[Period Code] = O.[Period Code]
AND E1.Currency = O.[Sold Currency])
LEFT JOIN ExchangeTable as E2
On E2.[Period Code] = O.[Period Code]
AND E2.Currency = O.[Cost Currency]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County