Update Query help

[clark123]

I have 2 tables: 1) sortedswitchtbl 2) NewCarrier
sortedswitchtbl has following fields
Carrier ID |Calling|Called|Date|Time|Duration|Charge|
Call Status|Amount|Match
NewCarrier has following fields
Call Ref|CalledNumber| Date|Time|Duration|Charge|TotalCost
Tables are joined with Called=CalledNumber

I want to find records in NewCarrier table(A) that does not have corresponding records in sortedswitchtbl (B)
If matching record is found in sortedswitchtbl, update Match field in sortedswitchtbl.
This is not working. It is inserting empty rows in sortedswitchtbl table.
Query is:

UPDATE NewCarrier LEFT JOIN sortedswitchtbl ON (NewCarrier.Time = sortedswitchtbl.Time) AND (NewCarrier.Date = sortedswitchtbl.Date) AND (NewCarrier.CalledNumber = sortedswitchtbl.Called) SET sortedswitchtbl.[Match] = [Match]*"2"
WHERE (((sortedswitchtbl.Called) Is Null));

 

[tlbroadbent]

Your explanation cofuses me. These statemetns seem contradictory.

"I want to find records in NewCarrier table(A) that does not have corresponding records in sortedswitchtbl (B)."

"If matching record is found in sortedswitchtbl, update Match field in sortedswitchtbl."

Your query is set to find records in NewCarrier without matching records in sortedswitchtbl. Then you want to update sortedswitchtbl. The only way Access can update the table is to add records because nothing matches based on your criteria. You need to decide if you want to update sortedswitchtbl with data from NewCarrier or add records from NewCarrier. If you want to add records, you need to provide all the columns that should be inserted into sortedswitchtbl. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.