Update value, 2 tables, SQL syntax? (1 Viewer)

Susy

New member
Local time
Today, 05:01
Joined
Dec 19, 2019
Messages
25
Hi everyone,

I don't understand the SQL syntax with JOIN and would be very thankful if someone could help me!

What I am trying to do:

I have two tables, Upload_Freight and Upload_FX:
1586257762280.png

What I would like to do is to update the field FX_Value in Upload_Freight. The criteria are that the FX_Month (ex. 3), FX_Year (ex. 2020) and Currency (ex. EUR) in the tab Upload_FX match the Date_Transportation (ex. 31/03/20) and the Currency (ex. EUR) in the tab Upload_Freight. Does anyone know if this is feasible and if so, how I could do that?

Thanks and regards,
Susy
 

namliam

The Mailman - AWF VIP
Local time
Today, 05:01
Joined
Aug 11, 2003
Messages
11,543
You are not thinking accoording to a database, you do not store this seperately therefor do not need to update the field

What you want to do is simply run a query to sum all the FX_value 's per year and month and be done with it.
 

Susy

New member
Local time
Today, 05:01
Joined
Dec 19, 2019
Messages
25
You are not thinking accoording to a database, you do not store this seperately therefor do not need to update the field

What you want to do is simply run a query to sum all the FX_value 's per year and month and be done with it.
Thanks, yes, probably this is why I am not able to do it! ;) Actually, i wanted to update this field in order to run a query that translates the local currency into one single currency. In my tab Upload_FX I have the FX rates and in my tab Upload_Freight I have some amounts in local currency. By putting the FX_rate into my Upload_Freight table, I could simply calculate the amounts in one single currency. What would you do in order to achieve this?
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Yesterday, 22:01
Joined
Feb 28, 2001
Messages
16,847
The problem you will face is PERHAPS that you might have ambiguities that would cause you to mis-link the records in the _FX table. In order to make the link correct, you need uniqueness constraints so that you will not inadvertently link multiple _Freight records to a single _FX record. If you do that, then the update can occur multiple times but you only have one slot for the linkage. We don't know the order of records in a table or the order in which certain types of record-level processing occur (Access won't show us). It is safe to say that whatever order is used, the last record is the one that wins. But if other _Frieght records were also matches, you would miss them.

So what is your REAL goal - in English?
 

Susy

New member
Local time
Today, 05:01
Joined
Dec 19, 2019
Messages
25
The problem you will face is PERHAPS that you might have ambiguities that would cause you to mis-link the records in the _FX table. In order to make the link correct, you need uniqueness constraints so that you will not inadvertently link multiple _Freight records to a single _FX record. If you do that, then the update can occur multiple times but you only have one slot for the linkage. We don't know the order of records in a table or the order in which certain types of record-level processing occur (Access won't show us). It is safe to say that whatever order is used, the last record is the one that wins. But if other _Frieght records were also matches, you would miss them.

So what is your REAL goal - in English?
Mmh not sure to understand your input. I don't think that there is any ambiguity. Lets have a look at my data in the tab Upload_FX:

1586272550333.png

Then in my tab Upload_Freight, I have the Date_Transportation that gives me the FX_Month, FX_Year in the tab Upload_FX, and for the Currency, I use the corresponding field in Upolad_Freight, also called Currency. Therefore, if I manage to write a code that includes these criteria, Access should be able to return the right value of the tab Upload_FX, shouldn't it?
E.g. Upload_Freight => Date_Transporation = 20/03/20, Currency = PLN, then FX_Value = 0.9876 (source: Upload_FX).
 

bastanu

Registered User.
Local time
Yesterday, 20:01
Joined
Apr 13, 2010
Messages
372
I think You need to create a new query based on Upload_freight:

"SELECT FX_Month:Month([Date_Transportation]), FX_YEAR:Year([Date_Transportation]),FX_Value FROM Upload_freight ;"

Now in a new update query you join this to Upload_FX table on FX_Month and FX_Year fields and in the Update row for the FX_Value in Upload_freight you simply put [Upload_FX]![FX_Value].

Cheers,
Vlad
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Yesterday, 22:01
Joined
Feb 28, 2001
Messages
16,847
Your ambiguity (at least from what you described) is that "DateTransported" appears from your description to be a full date, but you are "blocking off" a month and year. What happens in your table when you have two entries on different days of the same month? That is the ambiguity that I see based on the explanation.
 

Susy

New member
Local time
Today, 05:01
Joined
Dec 19, 2019
Messages
25
Your ambiguity (at least from what you described) is that "DateTransported" appears from your description to be a full date, but you are "blocking off" a month and year. What happens in your table when you have two entries on different days of the same month? That is the ambiguity that I see based on the explanation.
Mhhm ok , I see what you mean. But couldn't I translate the Date_Transportation into a numerical month and only use this info for the FX_Value?
 

Susy

New member
Local time
Today, 05:01
Joined
Dec 19, 2019
Messages
25
I think You need to create a new query based on Upload_freight:

"SELECT FX_Month:Month([Date_Transportation]), FX_YEAR:Year([Date_Transportation]),FX_Value FROM Upload_freight ;"

Now in a new update query you join this to Upload_FX table on FX_Month and FX_Year fields and in the Update row for the FX_Value in Upload_freight you simply put [Upload_FX]![FX_Value].

Cheers,
Vlad
Hi Vlad,

Thanks for your input. I used your SQL statement and there was a syntax error (missing operator) in query expression 'FX_Month:Month([Date_Transportation]). Any advice?

Moreover, as this query uses two tabs, I am wondering if I shouldn't specify the tab I am referring to:
SELECT Upload_FX.FX_Month:Month(Upload_Freight.Date_Transportation), Upload_FX.FX_Year:Year(Upload_Freight.Date_Transportation), Upload_FX.FX_Value FROM Upload_Freight;
What do you think?
 

The_Doc_Man

Happy Retired Curmudgeon
Local time
Yesterday, 22:01
Joined
Feb 28, 2001
Messages
16,847
But couldn't I translate the Date_Transportation into a numerical month and only use this info for the FX_Value?
Absolutely - but it still wouldn't be unique if you had multiple dates in the same month. And where you have a single matching slot, you have overloaded the relationship such that only the last item wins. AND NOTE that with Access or in fact any other generally "standard" SQL, the exact order of entries in a table is not guaranteed, so "last" is an ambiguous term.
 

bastanu

Registered User.
Local time
Yesterday, 20:01
Joined
Apr 13, 2010
Messages
372
Hi Susy,
The first query is only based on one table Upload_Freight and its purpose is to get you the month and year of the Date_Transportation along with the field to be updated. Save it with a new name (i.e. qryUpload_FreightForUpdate). Now create a new (update) query in which you bring qryUpload_FreightForUpdate and Upload_FX, join them by FX_Month and FX_Year and update the FX_Value in qryUpload_FreightForUpdate to [Upload_FX]![FX_Value].
Make sure you make a backup copy of your back-end before you try it.

Cheers,
Vlad
 

Susy

New member
Local time
Today, 05:01
Joined
Dec 19, 2019
Messages
25
Hi Susy,
The first query is only based on one table Upload_Freight and its purpose is to get you the month and year of the Date_Transportation along with the field to be updated. Save it with a new name (i.e. qryUpload_FreightForUpdate). Now create a new (update) query in which you bring qryUpload_FreightForUpdate and Upload_FX, join them by FX_Month and FX_Year and update the FX_Value in qryUpload_FreightForUpdate to [Upload_FX]![FX_Value].
Make sure you make a backup copy of your back-end before you try it.

Cheers,
Vlad
Thanks a lot Vlad for your help. I managed to update the table!
 

bastanu

Registered User.
Local time
Yesterday, 20:01
Joined
Apr 13, 2010
Messages
372
Glad to hear Susy, stay safe!

Cheers,
Vlad
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom