Autopopulate by matching data to another table and selecting corresponding field

BillieJ

New member
Local time
Today, 18:14
Joined
Jul 22, 2013
Messages
4
I was wondering if anyone can help with with a problem I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.

I have a table called Costs within which there are 2 fields
Purchase Currency
Exchange Rate

I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency
Exchange Rate

When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.

I have tried the following and none work:


SQL Tried

1.
UPDATE Costs

SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate]

WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency



2.
UPDATE
Costs
SET
ExchangeRate = [Exchange Rate]
FROM
[Exchange Rates 13/14]
INNER JOIN
[Exchange Rates 13/14]
ON
Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
;

3.
SELECT [Exchange Rate]
FROM [Exchange Rates 13/14]
WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
;

I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) – you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate. The exchange rate is used to calculate a GBP Unit Cost which is used for other calculations and therefore several others errors occur. I will be uploading quite large amounts of data so I really need to fix this.

Or is the alternative to put this into the calculation of GBP Unit Cost – where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.

If anyone can assist at all I’d be very grateful – this seems like it should be simple but it is not (well for me anyway).

Thank you! :)
 
You'll find it a lot easier if you let Access do the work of generating the SQL by designing your update query in the design mode and then switch to the SQL view.

You'll get something along the lines of
Update Costs Inner Join [Exchange Rates] ON Costs.Currency = [Exchange Rates].Currency SET Costs.exchange rate = [Exchange Rates].ExchangeRate Where ....(your condition)
 
Thanks for your post!

I'd tried that and failed however I have just managed to do it - thanks for your tips with the SQL as it became a bit clearer being able to follow that.

The only problem is that it does not happen automatically - if I am importing data via an excel template into the table is there a way of getting the query to run automatically? It's also a bit slow and when I run the query takes a while to update.

Sorry I am an Access novice trying to create something which is a bit above my skill set! ;) I'm getting there...slowly!
 
What do you mean by "automatically"? Something has to trigger it to happen.

You could have an external timer to turn your computer on at a certain time, and have the PC's start up open the Access db and run the query "automatically".

Alternatively, automatically could mean the query runs when the database is opened, or if it is open, to run every hour without user intervention. Or to run whenever you click a button.
 
Instead of directly importing the Excel data, link to it. Then use a query that joins the Excel table to the currency table. Select all the Excel data and then the conversion factor from the currency table. Change the query to an append query to get the imported data into your regular tables.
 

Users who are viewing this thread

Back
Top Bottom