Update Query based on multiple values (1 Viewer)

mounty76

Registered User.
Local time
Yesterday, 19:53
Joined
Sep 14, 2017
Messages
221
Hello All,

Sorry for another question!!

I have three tables

1. Date range
2. Info
3. Exchange rates

I need to create an update query that does the following:

1. Selects all records in the Info table based on the date range in the date range table - I have done this
2. Then updates all the exchange rates in the Info table based on the exchange rates table....EG

Exchange Rates Tbl

ID Currency Exchange
1 USD 1.00
2 EURO 1.16
3 GBP 1.40
4 EC 2.10

So the query needs to update the records in the info table with the correct exchange rate that corrosponds to exchange rate table....hope that makes sense! The info and currency tables are linked
 

Ranman256

Well-known member
Local time
Yesterday, 22:53
Joined
Apr 9, 2015
Messages
4,088
use the image as an example for yours. Get the exchange rate via the date table range.
then update the info page.
 

Attachments

  • use dates in rate table.png
    use dates in rate table.png
    108.6 KB · Views: 210

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:53
Joined
Oct 29, 2018
Messages
18,742
And there are no date information in the exchange rates table?
 

mounty76

Registered User.
Local time
Yesterday, 19:53
Joined
Sep 14, 2017
Messages
221
And there are no date information in the exchange rates table?
No I tried to keep the exchange rate table simple, it was primarily just as a lookup tbl for the info tbl to get the currency from
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:53
Joined
Oct 29, 2018
Messages
18,742
No I tried to keep the exchange rate table simple, it was primarily just as a lookup tbl for the info tbl to get the currency from
So, are you saying you want to update the exchange rates for all the records within the date range to whatever is in the exchange rate table without any regards about keeping a historical data of previous exchange rates?
 

mounty76

Registered User.
Local time
Yesterday, 19:53
Joined
Sep 14, 2017
Messages
221
The historical data for exchange rates would be in the info table as you'll only be updating the records in the date range.

That said I have decided to modify it, in that I'll have an exchange rate table, therefore each month you enter a date range and a exchange rate for each currency?

Before I change things what would you suggest is the best way to go about it!!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:53
Joined
Oct 29, 2018
Messages
18,742
The historical data for exchange rates would be in the info table as you'll only be updating the records in the date range.

That said I have decided to modify it, in that I'll have an exchange rate table, therefore each month you enter a date range and a exchange rate for each currency?

Before I change things what would you suggest is the best way to go about it!!
Okay, so if you're going to have a properly structured exchange rate table with effective date ranges, then you really don't need to store anything from the exchange rate table into the info table.

Code:
tblRates
ID    Rate   Start     End
1     1.5    1/1/2021  1/31/2021
2     2.0    2/1/2021  2/28/2021
etc.

tblInfo
InfoID   Date
1        1/5/2021
2        1/20/2021
3        2/17/2021

You should be able to create a query to produce the following.

Code:
InfoID    Date        Rate
1         1/5/2021    1.5
2         1/20/2021   1.5
3         2/17/2021   2.0
 

mounty76

Registered User.
Local time
Yesterday, 19:53
Joined
Sep 14, 2017
Messages
221
OK makes sense, to note however that I've an update query that multiplies the cost price by the exchange rate to then produce a USD price, this updates the info table.

I presume I'll need to change this so it:

1. Looks at the date in the info table and date ranges in exchange rate table
2. Multiplies the cost price against the corrosponding exchange rate that is valid for the date of the expense?

Hmm getting complicated!! haha
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:53
Joined
Oct 29, 2018
Messages
18,742
OK makes sense, to note however that I've an update query that multiplies the cost price by the exchange rate to then produce a USD price, this updates the info table.

I presume I'll need to change this so it:

1. Looks at the date in the info table and date ranges in exchange rate table
2. Multiplies the cost price against the corrosponding exchange rate that is valid for the date of the expense?

Hmm getting complicated!! haha
Hi. Thanks for the additional information. Unfortunately, you had the previous problem and now this other process, which you have to constantly maintain, because you probably did not have the correct table structure. If you use the structure I outlined, you won't need to update the table to store the cost price based on the exchange rate. In essence, that value is a calculate value, which is not recommended to be stored in a table anyway. Instead, you should be able to produce a query to calculate the cost price, just like the query to display the appropriate exchange rate per record.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:53
Joined
Feb 19, 2002
Messages
35,834
OK makes sense, to note however that I've an update query that multiplies the cost price by the exchange rate to then produce a USD price, this updates the info table.
This is a flawed design. Rather than running an update query prior to doing whatever you want to do, you should be joining to the rate table and doing the calculation in the select query. The actual rate doesn't need to be updated.

Another option (and the one I typically use) is to store the effective rate at the time the transaction is entered. This rate should NOT be changeable after the fact so the field should always be locked to prevent a user from accidentally changing it.
 

Users who are viewing this thread

Top Bottom