How to update currency exchange rates for multiple records without having to amend each record

[Kenny84]

I have a table that lists, amongst others fields, an employees base salary, the currency that is it paid in, the exchange rate to US Dollars and finally the US Equivalent Salary. Being based in Europe, I support various employees across the region who get paid via different currencies, however being a US owned business, all costs get converted into dollars.

Note: I have a calculation running between the current base salary and the US exchange rate to determine the USD Equivalent Salary.

I have a field that contains the exchange rates that I plan to update monthly, however with almost 500 employee records, I dont want to have to go through line by line updating them individually.

Is there a way that I can update the various currencies quickly to avoid this?



For example:

Name	Job Title	Current Base Salary	Currency in contract	US Exchange Rate	USD Equivalent Salary
Joe Bloggs	Manager	80,364.35	British Sterling	1.5438	124,066.48
Jane Smith	Director	90,376.33	Euro	1.119571	106,730.73
Peter Jones	Supervisor	75,934.82	Swiss Franc	1.048256	79,599.13
Sarah Cole	Senior Manager	405,000	Polish Zloty	0.269796	109,267.38

Next month, when the exchange rates change, the US Exchange Rate values will change which will impact the USD Equivalent Salary.

I am fairly new to Access so have quite limited knowledge about queries design and macros.

Thanks

[orange]

You can get exchange rates from https://www.ecb.europa.eu/stats/exch.../index.en.html

I strongly suggest you work through this tutorial before jumping into your project. An hour spent on the tutorial will help you more than a month or two of trial and error. Learn the concepts of database design before getting too involved with Access or any database software.

[rpeare]

the table you show should be storing a FK (foreign key) to a table with the exchange rates (assuming you have one rate per currency and do not track it over time) and eliminate the US exchange rate field from the table, that would be stored on your newly created exchange rate table

so for instance your tables would become

RateID	BaseCurrency	USRate
1	British Sterling	1.5438
2	Euro	1.119571
3	Swiss Franc	1.048256
4	Polish Zloty	.269796

EmployeeID	EmployeeName	CurrentBaseSalary	RateID
1	Joe Bloggs	80364.35	1
2	Jane Smith	90376.33	2
3	Peter Jones	75934.82	3
4	Sarah Cole	405000	4

Then you only have to update the exchange rate table with the new values and every calculation you do from there will link the RATEID on the employee table to the RATEID on the exchange rate table to figure the US rate.

[Kenny84]

Thanks rpeare.

So just to confirm, I do have an seperate underlying table with the exchange rates listed out and then the exchange rate field in the main table uses a lookup so the user can select the appropriate currency for the individual. I think I understand that you are saying to delete the Exchange rate column and just have a calculation linked directly to the underlying table, but then how would you build that so it knows to look at te right currency?

[rpeare]

if you're storing the foreign key to the currency conversion table you can just adopt the description on any form/report/query by linking to the table and including the currency description field from the foreign table.

[Kenny84]

Sorry im lost.....
I have now replicated the Currency table you outlined above but Im not sure how to build in a the calculation so that it knows to link the contractual currency, to the underlying table to pull out the exchange rate to multiply their salary into USD equivalent?

[rpeare]

Simple Example

Kenny84.zip