Update Query

M

Mavis

Hi All,

I have the below update query but when i try to run the query, no row is
update. Not sure what is wrong. The code look correct to me. Please help me.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON (TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date) AND (TBL_Daily_Exchange_Rate.ExchangeRate =
TBLValueClaim.ValuableROE) AND (TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency)) ON (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) AND (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber)
SET TBL_Daily_Exchange_Rate.CurrencyCode = [TBLValueClaim].[ClaimCurrency],
TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
[TBLValueClaim].[Valuable_ROE_Date], TBL_Daily_Exchange_Rate.ExchangeRate =
[TBLValueClaim].[ValuableROE]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));
 
J

John W. Vinson

Hi All,

I have the below update query but when i try to run the query, no row is
update. Not sure what is wrong. The code look correct to me. Please help me.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON (TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date) AND (TBL_Daily_Exchange_Rate.ExchangeRate =
TBLValueClaim.ValuableROE) AND (TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency)) ON (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) AND (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber)
SET TBL_Daily_Exchange_Rate.CurrencyCode = [TBLValueClaim].[ClaimCurrency],
TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
[TBLValueClaim].[Valuable_ROE_Date], TBL_Daily_Exchange_Rate.ExchangeRate =
[TBLValueClaim].[ValuableROE]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));
Click to expand...

I'm a bit surprised that a three table join is updateable at all, but... if
you change the query to a SELECT query does it return any records? If you
break it up, are there in fact records with

TBL_Daily_Exchange_Rate.Exchange_Rate_Date = TBLValueClaim.Valuable_ROE_Date

or might the Exchange_Rate_Date be part of a range of dates?
 
M

Mavis

Hi John,

Thanks for your prompt reply. Actually i am very new iwth access. I not very
surehow to do that. can you advice?


John W. Vinson said:
Hi All,

I have the below update query but when i try to run the query, no row is
update. Not sure what is wrong. The code look correct to me. Please help me.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON (TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date) AND (TBL_Daily_Exchange_Rate.ExchangeRate =
TBLValueClaim.ValuableROE) AND (TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency)) ON (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) AND (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber)
SET TBL_Daily_Exchange_Rate.CurrencyCode = [TBLValueClaim].[ClaimCurrency],
TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
[TBLValueClaim].[Valuable_ROE_Date], TBL_Daily_Exchange_Rate.ExchangeRate =
[TBLValueClaim].[ValuableROE]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));
Click to expand...

I'm a bit surprised that a three table join is updateable at all, but... if
you change the query to a SELECT query does it return any records? If you
break it up, are there in fact records with

TBL_Daily_Exchange_Rate.Exchange_Rate_Date = TBLValueClaim.Valuable_ROE_Date

or might the Exchange_Rate_Date be part of a range of dates?
Click to expand...
 
M

Mavis

hi,

I had change the code of the update query but it only works once. Aftr that
when i click on my Update button it said that i had update 3 rows but when i
look at my record there isnt any change.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency) ON (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber) AND (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) SET TBLValueClaim.ValuableROE =
[TBL_Daily_Exchange_Rate].[ExchangeRate], TBLValueClaim.Valuable_ROE_Date =
[TBL_Daily_Exchange_Rate].[Exchange_Rate_Date]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));


Mavis said:
Hi John,

Thanks for your prompt reply. Actually i am very new iwth access. I not very
surehow to do that. can you advice?


John W. Vinson said:
Hi All,

I have the below update query but when i try to run the query, no row is
update. Not sure what is wrong. The code look correct to me. Please help me.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON (TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date) AND (TBL_Daily_Exchange_Rate.ExchangeRate =
TBLValueClaim.ValuableROE) AND (TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency)) ON (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) AND (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber)
SET TBL_Daily_Exchange_Rate.CurrencyCode = [TBLValueClaim].[ClaimCurrency],
TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
[TBLValueClaim].[Valuable_ROE_Date], TBL_Daily_Exchange_Rate.ExchangeRate =
[TBLValueClaim].[ValuableROE]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));
Click to expand...

I'm a bit surprised that a three table join is updateable at all, but... if
you change the query to a SELECT query does it return any records? If you
break it up, are there in fact records with

TBL_Daily_Exchange_Rate.Exchange_Rate_Date = TBLValueClaim.Valuable_ROE_Date

or might the Exchange_Rate_Date be part of a range of dates?
Click to expand...
Click to expand...
 
V

vanderghast

With Access, from the query designer, you can switch the update query into a
data view query and Access would show you the fields that would be updated
if you 'execute' the query (with their actual data). If you do so, do you
see records, in dataview, before you execute the update?

Vanderghast, Access MVP


Mavis said:
hi,

I had change the code of the update query but it only works once. Aftr
that
when i click on my Update button it said that i had update 3 rows but when
i
look at my record there isnt any change.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency) ON (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber) AND (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) SET TBLValueClaim.ValuableROE =
[TBL_Daily_Exchange_Rate].[ExchangeRate], TBLValueClaim.Valuable_ROE_Date
=
[TBL_Daily_Exchange_Rate].[Exchange_Rate_Date]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));


Mavis said:
Hi John,

Thanks for your prompt reply. Actually i am very new iwth access. I not
very
surehow to do that. can you advice?


John W. Vinson said:
On Thu, 21 May 2009 20:12:43 -0700, Mavis
<[email protected]>
wrote:

Hi All,

I have the below update query but when i try to run the query, no row
is
update. Not sure what is wrong. The code look correct to me. Please
help me.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON (TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date) AND
(TBL_Daily_Exchange_Rate.ExchangeRate =
TBLValueClaim.ValuableROE) AND (TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency)) ON (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) AND (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber)
SET TBL_Daily_Exchange_Rate.CurrencyCode =
[TBLValueClaim].[ClaimCurrency],
TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
[TBLValueClaim].[Valuable_ROE_Date],
TBL_Daily_Exchange_Rate.ExchangeRate =
[TBLValueClaim].[ValuableROE]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));

I'm a bit surprised that a three table join is updateable at all,
but... if
you change the query to a SELECT query does it return any records? If
you
break it up, are there in fact records with

TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date

or might the Exchange_Rate_Date be part of a range of dates?
Click to expand...
Click to expand...
Click to expand...
 
M

Mavis

HI,

Thanks for your reply. Can u tell me how to switch the update query into a
data view query? I am very new to access.



vanderghast said:
With Access, from the query designer, you can switch the update query into a
data view query and Access would show you the fields that would be updated
if you 'execute' the query (with their actual data). If you do so, do you
see records, in dataview, before you execute the update?

Vanderghast, Access MVP


Mavis said:
hi,

I had change the code of the update query but it only works once. Aftr
that
when i click on my Update button it said that i had update 3 rows but when
i
look at my record there isnt any change.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency) ON (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber) AND (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) SET TBLValueClaim.ValuableROE =
[TBL_Daily_Exchange_Rate].[ExchangeRate], TBLValueClaim.Valuable_ROE_Date
=
[TBL_Daily_Exchange_Rate].[Exchange_Rate_Date]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));


Mavis said:
Hi John,

Thanks for your prompt reply. Actually i am very new iwth access. I not
very
surehow to do that. can you advice?


:

On Thu, 21 May 2009 20:12:43 -0700, Mavis
<[email protected]>
wrote:

Hi All,

I have the below update query but when i try to run the query, no row
is
update. Not sure what is wrong. The code look correct to me. Please
help me.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON (TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date) AND
(TBL_Daily_Exchange_Rate.ExchangeRate =
TBLValueClaim.ValuableROE) AND (TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency)) ON (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) AND (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber)
SET TBL_Daily_Exchange_Rate.CurrencyCode =
[TBLValueClaim].[ClaimCurrency],
TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
[TBLValueClaim].[Valuable_ROE_Date],
TBL_Daily_Exchange_Rate.ExchangeRate =
[TBLValueClaim].[ValuableROE]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));

I'm a bit surprised that a three table join is updateable at all,
but... if
you change the query to a SELECT query does it return any records? If
you
break it up, are there in fact records with

TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date

or might the Exchange_Rate_Date be part of a range of dates?
Click to expand...
Click to expand...
Click to expand...
 
M

Mavis

Hi,

I am able to find how to switch the view to data view. I saw the vaule had
changed as per what i have entered but when i look at the table of where the
field is, the change is not made.

please advice.

Thanks!



vanderghast said:
With Access, from the query designer, you can switch the update query into a
data view query and Access would show you the fields that would be updated
if you 'execute' the query (with their actual data). If you do so, do you
see records, in dataview, before you execute the update?

Vanderghast, Access MVP


Mavis said:
hi,

I had change the code of the update query but it only works once. Aftr
that
when i click on my Update button it said that i had update 3 rows but when
i
look at my record there isnt any change.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency) ON (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber) AND (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) SET TBLValueClaim.ValuableROE =
[TBL_Daily_Exchange_Rate].[ExchangeRate], TBLValueClaim.Valuable_ROE_Date
=
[TBL_Daily_Exchange_Rate].[Exchange_Rate_Date]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));


Mavis said:
Hi John,

Thanks for your prompt reply. Actually i am very new iwth access. I not
very
surehow to do that. can you advice?


:

On Thu, 21 May 2009 20:12:43 -0700, Mavis
<[email protected]>
wrote:

Hi All,

I have the below update query but when i try to run the query, no row
is
update. Not sure what is wrong. The code look correct to me. Please
help me.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON (TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date) AND
(TBL_Daily_Exchange_Rate.ExchangeRate =
TBLValueClaim.ValuableROE) AND (TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency)) ON (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) AND (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber)
SET TBL_Daily_Exchange_Rate.CurrencyCode =
[TBLValueClaim].[ClaimCurrency],
TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
[TBLValueClaim].[Valuable_ROE_Date],
TBL_Daily_Exchange_Rate.ExchangeRate =
[TBLValueClaim].[ValuableROE]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));

I'm a bit surprised that a three table join is updateable at all,
but... if
you change the query to a SELECT query does it return any records? If
you
break it up, are there in fact records with

TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date

or might the Exchange_Rate_Date be part of a range of dates?
Click to expand...
Click to expand...
Click to expand...
 
V

vanderghast

When you see the data in data view of an update query, that is BEFORE the
update query is done. You have to execute (run) the update query for the
update to be made.

Note that if a (one) given record appear MULTIPLE time, it will be update
multiple time, but would keep ONLY the last modification. A little bit like:

x=4
x=5
x=2


will only remember x=2, after the execution of the sequence.



Vanderghast, Access MVP




Mavis said:
Hi,

I am able to find how to switch the view to data view. I saw the vaule had
changed as per what i have entered but when i look at the table of where
the
field is, the change is not made.

please advice.

Thanks!



vanderghast said:
With Access, from the query designer, you can switch the update query
into a
data view query and Access would show you the fields that would be
updated
if you 'execute' the query (with their actual data). If you do so, do you
see records, in dataview, before you execute the update?

Vanderghast, Access MVP


Mavis said:
hi,

I had change the code of the update query but it only works once. Aftr
that
when i click on my Update button it said that i had update 3 rows but
when
i
look at my record there isnt any change.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency) ON (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber) AND (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) SET TBLValueClaim.ValuableROE =
[TBL_Daily_Exchange_Rate].[ExchangeRate],
TBLValueClaim.Valuable_ROE_Date
=
[TBL_Daily_Exchange_Rate].[Exchange_Rate_Date]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));


:

Hi John,

Thanks for your prompt reply. Actually i am very new iwth access. I
not
very
surehow to do that. can you advice?


:

On Thu, 21 May 2009 20:12:43 -0700, Mavis
<[email protected]>
wrote:

Hi All,

I have the below update query but when i try to run the query, no
row
is
update. Not sure what is wrong. The code look correct to me.
Please
help me.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER
JOIN
TBLValueClaim ON (TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date) AND
(TBL_Daily_Exchange_Rate.ExchangeRate =
TBLValueClaim.ValuableROE) AND
(TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency)) ON (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) AND (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber)
SET TBL_Daily_Exchange_Rate.CurrencyCode =
[TBLValueClaim].[ClaimCurrency],
TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
[TBLValueClaim].[Valuable_ROE_Date],
TBL_Daily_Exchange_Rate.ExchangeRate =
[TBLValueClaim].[ValuableROE]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));

I'm a bit surprised that a three table join is updateable at all,
but... if
you change the query to a SELECT query does it return any records?
If
you
break it up, are there in fact records with

TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date

or might the Exchange_Rate_Date be part of a range of dates?
Click to expand...
Click to expand...
Click to expand...
 
M

Mavis

vanderghast said:
When you see the data in data view of an update query, that is BEFORE the
update query is done. You have to execute (run) the update query for the
update to be made.

Note that if a (one) given record appear MULTIPLE time, it will be update
multiple time, but would keep ONLY the last modification. A little bit like:

x=4
x=5
x=2


will only remember x=2, after the execution of the sequence.



Vanderghast, Access MVP




Mavis said:
Hi,

I am able to find how to switch the view to data view. I saw the vaule had
changed as per what i have entered but when i look at the table of where
the
field is, the change is not made.

please advice.

Thanks!



vanderghast said:
With Access, from the query designer, you can switch the update query
into a
data view query and Access would show you the fields that would be
updated
if you 'execute' the query (with their actual data). If you do so, do you
see records, in dataview, before you execute the update?

Vanderghast, Access MVP


hi,

I had change the code of the update query but it only works once. Aftr
that
when i click on my Update button it said that i had update 3 rows but
when
i
look at my record there isnt any change.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency) ON (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber) AND (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) SET TBLValueClaim.ValuableROE =
[TBL_Daily_Exchange_Rate].[ExchangeRate],
TBLValueClaim.Valuable_ROE_Date
=
[TBL_Daily_Exchange_Rate].[Exchange_Rate_Date]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));


:

Hi John,

Thanks for your prompt reply. Actually i am very new iwth access. I
not
very
surehow to do that. can you advice?


:

On Thu, 21 May 2009 20:12:43 -0700, Mavis
<[email protected]>
wrote:

Hi All,

I have the below update query but when i try to run the query, no
row
is
update. Not sure what is wrong. The code look correct to me.
Please
help me.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER
JOIN
TBLValueClaim ON (TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date) AND
(TBL_Daily_Exchange_Rate.ExchangeRate =
TBLValueClaim.ValuableROE) AND
(TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency)) ON (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) AND (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber)
SET TBL_Daily_Exchange_Rate.CurrencyCode =
[TBLValueClaim].[ClaimCurrency],
TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
[TBLValueClaim].[Valuable_ROE_Date],
TBL_Daily_Exchange_Rate.ExchangeRate =
[TBLValueClaim].[ValuableROE]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));

I'm a bit surprised that a three table join is updateable at all,
but... if
you change the query to a SELECT query does it return any records?
If
you
break it up, are there in fact records with

TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date

or might the Exchange_Rate_Date be part of a range of dates?
Click to expand...
Click to expand...
Click to expand...
 
M

Mavis

Hi,

I had view the vaule of the dataview before and after update query is done.
The change did change. for example:

exhcnage rate: 100 it change to 1000
exchange rate date : 25/05/2009 it change to 19/05/2009.

This change happen in the update query data view but it does not make change
to the table.


vanderghast said:
When you see the data in data view of an update query, that is BEFORE the
update query is done. You have to execute (run) the update query for the
update to be made.

Note that if a (one) given record appear MULTIPLE time, it will be update
multiple time, but would keep ONLY the last modification. A little bit like:

x=4
x=5
x=2


will only remember x=2, after the execution of the sequence.



Vanderghast, Access MVP




Mavis said:
Hi,

I am able to find how to switch the view to data view. I saw the vaule had
changed as per what i have entered but when i look at the table of where
the
field is, the change is not made.

please advice.

Thanks!



vanderghast said:
With Access, from the query designer, you can switch the update query
into a
data view query and Access would show you the fields that would be
updated
if you 'execute' the query (with their actual data). If you do so, do you
see records, in dataview, before you execute the update?

Vanderghast, Access MVP


hi,

I had change the code of the update query but it only works once. Aftr
that
when i click on my Update button it said that i had update 3 rows but
when
i
look at my record there isnt any change.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency) ON (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber) AND (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) SET TBLValueClaim.ValuableROE =
[TBL_Daily_Exchange_Rate].[ExchangeRate],
TBLValueClaim.Valuable_ROE_Date
=
[TBL_Daily_Exchange_Rate].[Exchange_Rate_Date]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));


:

Hi John,

Thanks for your prompt reply. Actually i am very new iwth access. I
not
very
surehow to do that. can you advice?


:

On Thu, 21 May 2009 20:12:43 -0700, Mavis
<[email protected]>
wrote:

Hi All,

I have the below update query but when i try to run the query, no
row
is
update. Not sure what is wrong. The code look correct to me.
Please
help me.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER
JOIN
TBLValueClaim ON (TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date) AND
(TBL_Daily_Exchange_Rate.ExchangeRate =
TBLValueClaim.ValuableROE) AND
(TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency)) ON (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) AND (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber)
SET TBL_Daily_Exchange_Rate.CurrencyCode =
[TBLValueClaim].[ClaimCurrency],
TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
[TBLValueClaim].[Valuable_ROE_Date],
TBL_Daily_Exchange_Rate.ExchangeRate =
[TBLValueClaim].[ValuableROE]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));

I'm a bit surprised that a three table join is updateable at all,
but... if
you change the query to a SELECT query does it return any records?
If
you
break it up, are there in fact records with

TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date

or might the Exchange_Rate_Date be part of a range of dates?
Click to expand...
Click to expand...
Click to expand...
 
V

vanderghast

If you are running the query from the user interface, an error may have
occured, but if you have turned off the warnings, you may not be aware of
it.


In the Debug Immediate window (Ctrl G), type:

DoCmd.SetWarnings true

end follow it by an enter ( to execute the statement).

Now, re-run the update query. If you get an error message, that may explain
why the data is NOT updated into the original table.


Otherwise, I fail to see why the update won't work, without supplying an
error message, if there are records visible in the dataview as being updated
AND you get the Access message:




Mirosoft Office Access
----------------------------------------------------------------------

You are about to update N rows(s)

! Once you click Yes, you can't use the Undo command to reverse
the changes.
Are you sure you want to update these records?

[Yes] [No]





If you don't get any message when you run the query, it is probably because
the SetWarnings is off.




Vanderghast, Access MVP




Mavis said:
Hi,

I had view the vaule of the dataview before and after update query is
done.
The change did change. for example:

exhcnage rate: 100 it change to 1000
exchange rate date : 25/05/2009 it change to 19/05/2009.

This change happen in the update query data view but it does not make
change
to the table.


vanderghast said:
When you see the data in data view of an update query, that is BEFORE the
update query is done. You have to execute (run) the update query for the
update to be made.

Note that if a (one) given record appear MULTIPLE time, it will be update
multiple time, but would keep ONLY the last modification. A little bit
like:

x=4
x=5
x=2


will only remember x=2, after the execution of the sequence.



Vanderghast, Access MVP




Mavis said:
Hi,

I am able to find how to switch the view to data view. I saw the vaule
had
changed as per what i have entered but when i look at the table of
where
the
field is, the change is not made.

please advice.

Thanks!



:

With Access, from the query designer, you can switch the update query
into a
data view query and Access would show you the fields that would be
updated
if you 'execute' the query (with their actual data). If you do so, do
you
see records, in dataview, before you execute the update?

Vanderghast, Access MVP


hi,

I had change the code of the update query but it only works once.
Aftr
that
when i click on my Update button it said that i had update 3 rows
but
when
i
look at my record there isnt any change.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER JOIN
TBLValueClaim ON TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency) ON (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber) AND (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) SET TBLValueClaim.ValuableROE =
[TBL_Daily_Exchange_Rate].[ExchangeRate],
TBLValueClaim.Valuable_ROE_Date
=
[TBL_Daily_Exchange_Rate].[Exchange_Rate_Date]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));


:

Hi John,

Thanks for your prompt reply. Actually i am very new iwth access. I
not
very
surehow to do that. can you advice?


:

On Thu, 21 May 2009 20:12:43 -0700, Mavis
<[email protected]>
wrote:

Hi All,

I have the below update query but when i try to run the query,
no
row
is
update. Not sure what is wrong. The code look correct to me.
Please
help me.

UPDATE TBLCargoClaims INNER JOIN (TBL_Daily_Exchange_Rate INNER
JOIN
TBLValueClaim ON (TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date) AND
(TBL_Daily_Exchange_Rate.ExchangeRate =
TBLValueClaim.ValuableROE) AND
(TBL_Daily_Exchange_Rate.CurrencyCode =
TBLValueClaim.ClaimCurrency)) ON (TBLCargoClaims.BillofLading =
TBLValueClaim.BillOfLading) AND (TBLCargoClaims.ID =
TBLValueClaim.ClaimNumber)
SET TBL_Daily_Exchange_Rate.CurrencyCode =
[TBLValueClaim].[ClaimCurrency],
TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
[TBLValueClaim].[Valuable_ROE_Date],
TBL_Daily_Exchange_Rate.ExchangeRate =
[TBLValueClaim].[ValuableROE]
WHERE (((TBLCargoClaims.ClaimStatus)<>"Closed"));

I'm a bit surprised that a three table join is updateable at all,
but... if
you change the query to a SELECT query does it return any
records?
If
you
break it up, are there in fact records with

TBL_Daily_Exchange_Rate.Exchange_Rate_Date =
TBLValueClaim.Valuable_ROE_Date

or might the Exchange_Rate_Date be part of a range of dates?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

2 condition 2
Parameter from Form 6
Pass-Through query used to update an access table 2
SUM in a UNION query 2
Update query wont work? 1
update query with iff 3
update query help 2
Update Query 4

Top