How do you join on variable field names? Simple—by redesigning your exchange rate table.
Instead of this --
Month USD GBP CHF Jun 2005 1.223 1.937 1.115 Jul 2005 1.210 1.895 1.075 Aug 2005 1.242 2.014 1.228
You should have this --
Month Curr Rate Jun 2005 USD 1.223 Jun 2005 GBP 1.937 Jun 2005 CHF 1.115 Jul 2005 USD 1.210 Jul 2005 GBP 1.895 Jul 2005 CHF 1.075 Aug 2005 USD 1.242 Aug 2005 GBP 2.014 Aug 2005 CHF 1.228
This structure is immensely easier to fit a brand new currency into. With your existing table, you have to add a new column, and furthermore, you will undoubtedly need to modify every query in the application.
With the normalized table design, you can accommodate a new currency easily. But even if you weren't planning to add any new currencies—and can you really say you will never need to do that?—the query to join to the customer records will be a lot easier to write, too. Try it, and let me know if you have any problems.
This was first published in September 2005