Complicated query!! (1 Viewer)

mounty76

Registered User.
Local time
Today, 06:11
Joined
Sep 14, 2017
Messages
341
Hello!!

So I've two tables:

1. tblExpenses
2. tblCurrency

tblExpenses has a currency field which it looks up the value from tblCurrency, it also has an exchange rate field

tblCurrency also has an exchange rate field where I can set the exchange rates for each currency

I need a query to update the tblExpenses Exchange rates with the corrosponding exchange rate for the correct currency from tblCurrency, so it would look like:

tblExpenses

Date Currency Exchange Rate
1/1/21 EURO
2/1/21 USD
2/2/21 EC
3/2/21 EURO

tblCurrency

Currency Exchange Rate
EURO 1.15
USD 1.00
EC 2.70

How can I make an update query that finds the correct value from tblCurrency!Exchange and updates it into tblExpenses!Exchange Rate, would it have to be a lookup criteria in the update query?

Thank for your help.....banging my head on computer now!
 

mounty76

Registered User.
Local time
Today, 06:11
Joined
Sep 14, 2017
Messages
341
I should have said that the exchange rates are set at the end of the month and so cannot update them as I add new records, hence need query to run this
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:11
Joined
May 7, 2009
Messages
19,169
you should have Date also in tblCurrency.

this is the effective rate at that date/time.
 

mounty76

Registered User.
Local time
Today, 06:11
Joined
Sep 14, 2017
Messages
341
I get that, but we just use a set exchange rate for the whole month. Therefore my thinking was I just change the exchange rate for each currency in tblCurrency then use an update query to update tblExpenses (via a query with time criteria) so it then just updates the particular exchange rate for any given time frame
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:11
Joined
May 7, 2009
Messages
19,169
you need to add the date (even the first or last month date), so you will know
which records are updated (by date).
the currency table will grow. because you cannot use a Universal rate for all
your transaction, otherwise your rate are always current from year 2019 up to this year.
 

mounty76

Registered User.
Local time
Today, 06:11
Joined
Sep 14, 2017
Messages
341
I'm not sure I understand.

I have one tbl with a list of currencies in and then each month I enter an exchange rate in the column next to the currency

In the other tbl I have many fields (including date, description, budget code, etc... and currency and exchange rate)

I need an update query to run each time I click a button that will select the records from a date range I enter, then update the exchange rate that is applicable to the currency for that record, it has to get this info from the first tbl where I manually enter the exchange rate each month.

Then the main tbl will continue to grow with all expenses on and when I run the query each month it only updates the records for the time frame I specify, that way I'll have a record in the main table of the actual exchange rate applied to each record?

Sorry hope that makes sense! I know DLookup isn't ideal but I think in this case it should work as the update criteria.....I just cannot get it to return the correct values!
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:11
Joined
Sep 21, 2011
Messages
14,041
I would create a Select query in the designer, joining the two tables, make sure you have what you desire, then change it to an Update query?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:11
Joined
May 7, 2009
Messages
19,169
others will tell you, why need to update when you can tie the date with the date in tlbCurrency?
you just need to join them.
this minimized errors, as when you already updted your expense table to find out you have entered wrong currency value.
you will then need to update it again?
 

mounty76

Registered User.
Local time
Today, 06:11
Joined
Sep 14, 2017
Messages
341
I understand that but I could potentially end up with over 20 currencies over the next couple of years and so therefore I would have to autopopulate a whole new set of currencies each month. Also the main tbl uses the Currency tbl as a lookup for the list of currencies
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:11
Joined
May 7, 2009
Messages
19,169
it's you choice. make a copy of your table before updating.

Update tblExpense, tblCurrency Set tblExpense.[Exchange Rate] = tblCurrency.[Exchange Rate]
WHERE tblCurrency.[Currency] = tblExpense.[Currency] And
Month(tblCurrency.[Date])=Month(tblExpense.[Date]) And
Year(tblCurrency.[Date]) = Year(tblExpense.[Date])
 

mounty76

Registered User.
Local time
Today, 06:11
Joined
Sep 14, 2017
Messages
341
I would create a Select query in the designer, joining the two tables, make sure you have what you desire, then change it to an Update query?
Thanks for this, worked a treat....not sure where I was going wrong, but all good now, thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:11
Joined
Sep 21, 2011
Messages
14,041
I would still bear in mind what arnel is telling you. :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:11
Joined
Feb 19, 2002
Messages
42,970
If the currency rate changes today, does that affect expenses incurred three years ago? The answer is obviously no. so you have two choices. And one of them is NOT running an update query after the fact.

1. store the exchange rate in the expense record that was current at the time the expense was incurred
2. store the effective year/month in the currency table and join to the rate table using the year/month and CurrencySymbol.

In both cases, the currency table needs a date even when using option 1. You need to be able to enter new rates while the old rates are still there unless all processing stops while you update the date range table.

I know it's a lot of trouble to keep track of currency rates but the rate can vary significantly over the course of a month..
 

Users who are viewing this thread

Top Bottom