vlookup type thing - help - no little re: MS Access

[DonkeyOte]

Hi - now I could do this v simply in Excel etc but in my wisdom I have decided to create a simple db program for one of my Management Accountants - only problem being that I can't do it

In short I have a form in which a user will "log" a sale - and denote a sale currency. Now, I then need 3 sale amounts to be converted into 3 currencies...

eg I have sale amount 1, sale amount2 and sale amount 3 denoted by currency GBP. I then have other columns such as sale amount1 - GBP, sale amount 2 - GBP, sale amount 3 - GBP, sale amount 1 - USD, sale amount 2 - USD etc...

So what I want is to get fields:

Sale Amount 1 GBP
Sale Amount 2 GBP
Sale Amount 3 GBP
Sale Amount 1 USD

etc

to auto populate by looking up the denoted currency and retrieving an Exch Rate - to which Sale Amount 1 is multiplied etc....

I have created a table called EXCH which has 4 rows and 4 columns - this is effectively a matrix - ie GBP, USD & EUR on the horizontal and vertical axis' - with erates filling up the cells...

So for say SALE AMOUNT 1 which is denoted as USD - I want to fill SALE AMOUNT 1 GBP by taking SALE AMOUNT 1 looking up USD and going across to GBP to get the ERATE...

I have no doubt this is v SIMPLE but I am lost...I only use Access to store data and retrieve via SQL so I am a bit "new"... Should I be looking to create a SQL query to do this or can I somehow create calculated fields within the table setup?

God that makes no sense. Back to Excel...

 

[Russell Hauf]

Hi Lasw10,

Let's try to get to the bottom of this. So are you saying that you want to have this on a form? And you enter one thing then have the value come up in another currency (by say selecting a different currency from a combobox)?

I think what I would do is to set up a table something like this (forgive my lack of knowledge about the spelling and rates of foreign currencies):

Book1
	A	B	C	D
1	Convert	DESCRIPTION	ERATE
2	USD2GBP	DOLLARSTOPOUNDS	0.75
3	GPB2USD	POUNDSTODOLLARS	1.3333333
4	USD2EUR	DOLLARSTOEUROS	0.5
5	EUR2USD	EUROSTODOLLARS	2
Sheet1

Then you'd have to hook up the values to the exchange rates. Take a look at DLOOKUP in Help. A matrix won't really work in Access...

Hope this helps - let us know if it does not.

-Russell

 

[DonkeyOte]

Thanks Russell - I opted to build events for each field on the form - doing the calcs through VB with the rates set as variables.

Thanks though - appreciated!

Luke