Need help - Update Query

[Dustin B]

I have a DB that keeps track of rates for a lane. Lane being origin to
destination. The DB will take a linked table and look for any differences
between the rates that are on file and the linked table. I have asked that
the linked table always be in the same format and have formatted my master
table in the same manner. Below are the details of the DB and tables in
question.

DB - AuditTool.mdb
Tables-
Linked table called BrazilRates
Master table that carries the rates is called tblBrazilRates
BrazilRates fields
F1-F15
tblBrazilRates fields
Company, EffectiveDate, ContainerSize, Origin, BaseRate, Alameda, Security,
AMS, Delivery, FSC, BrokerageFee, LCLMIN, RateWM, ServiceLevel TransitTime,
AccountedFor

BrazilRates fields correspond to tblBrazilRates (Field F1 in BrazilRates
contains Company information to be loaded to tblBrazilRates on down the line)
except BrazilRates does not include an AccountedFor field. I was trying to
use the accounted for field to avoid duplicate entries of rates.

Here are the queries I have it running right now.

Below reads BrazilRates and updates to tblBrazilRates -qryUpdateBrazilRates

INSERT INTO tblBrazilRates ( Forwarder, EffectiveDate, ContainerSize,
Origin, BaseRate, Alameda, Security, AMS, Delivery, FSC, BrokerageFee,
LCLMIN, RateWM, ServiceLevel, TransitTime, AccountedFor )
SELECT BrazilRates.F1, BrazilRates.F2, BrazilRates.F3, BrazilRates.F4,
BrazilRates.F5, BrazilRates.F6, BrazilRates.F7, BrazilRates.F8,
BrazilRates.F9, BrazilRates.F10, BrazilRates.F11, BrazilRates.F12,
BrazilRates.F13, BrazilRates.F14, BrazilRates.F15, tblBrazilRates.AccountedFor
FROM tblBrazilRates INNER JOIN BrazilRates ON (tblBrazilRates.ContainerSize
= BrazilRates.F3) AND (tblBrazilRates.Origin = BrazilRates.F4)
WHERE (((BrazilRates.F2)<>[tblBrazilRates]![EffectiveDate]) AND
((tblBrazilRates.AccountedFor)<>True));

Below reads tblBrazilRates and attempts to mark all lanes that are dups
accounted for. -qryUpdateBrazilAccountedFor

UPDATE tblBrazilRates AS S SET S.AccountedFor = True
WHERE (((Exists (SELECT * FROM tblBrazilRates as S2
WHERE S2.[EffectiveDate] <> S.[EffectiveDate]
AND S2.[Origin] = S.[Origin] AND
S2.[ContainerSize]=S.[ContainerSize]))<>False));


As you may notice the above does it job but then if the same lane changes
again it will not change in the DB because it is marked accounted for.

I am teaching myself how to use Access so the above may be far more than it
needs to be. Hopefully this makes sense to someone reading it. If not
please ask questions. Thank You.