CODEWITHSUNDEEP
sqlms-accessms-access-2007
KSM
KSM

Reputation: 262

Update table result

I have two tables. In the UniqueZips table there is a code field assigned,
my goal is to match the fields of each window from both tables and assign the code from the Uniquezips table to the Route tbl but the code below doesn't work any suggestions? 

UPDATE [Route tbl], UniqueZips
SET [Route tbl].CODE = [UniqueZips]![CODE]
WHERE (([Route tbl]![WINDOW 1]=[UniqueZips]![WINDOW 1] &
     [Route tbl]![WINDOW 2]=[UniqueZips]![WINDOW 2] &
     [Route tbl]![WINDOW 3]=[UniqueZips]![WINDOW 3] & 
     [Route tbl]![WINDOW 4]=[UniqueZips]![WINDOW 4] & 
     [Route tbl]![WINDOW 5]=[UniqueZips]![WINDOW 5]));

I've also tired this 

  UPDATE UniqueZips  
  INNER JOIN [Route tbl] ON (UniqueZips.[WINDOW 3] = [Route tbl].[WINDOW 3])
  AND (UniqueZips.[WINDOW 5] = [Route tbl].[WINDOW 5]) 
  AND (UniqueZips.[WINDOW 4] = [Route tbl].[WINDOW 4]) 
  AND (UniqueZips.[WINDOW 2] = [Route tbl].[WINDOW 2])
  AND (UniqueZips.[WINDOW 1] = [Route tbl].[WINDOW 1]) 
  SET [Route tbl].CODE = [UniqueZips]![CODE]
  WHERE ((([Route tbl]![WINDOW 1]=[UniqueZips]![WINDOW 1]
        & [Route tbl]![WINDOW 2]= [UniqueZips]![WINDOW 2] 
        & [Route tbl]![WINDOW 3]=[UniqueZips]![WINDOW 3]
        & [Route tbl]![WINDOW 4]=[UniqueZips]![WINDOW 4] 
        & [Route tbl]![WINDOW 5])=[UniqueZips]![WINDOW 5]));

okay so before i was getting no results but now ive tried this 

      UPDATE [Route tbl], UniqueZips 
      SET [Route tbl].CODE = [UniqueZips]![CODE]
      WHERE (((StrComp([Route tbl]![WINDOW 1],[UniqueZips]![WINDOW 1]) & 
            StrComp([Route tbl]![WINDOW 2],[UniqueZips]![WINDOW 2]) & 
            StrComp([Route tbl]![WINDOW 3],[UniqueZips]![WINDOW 3]) & 
            StrComp([Route tbl]![WINDOW 4],[UniqueZips]![WINDOW 4]) &
            StrComp([Route tbl]![WINDOW 5],[UniqueZips]![WINDOW 5]))="0"));

this however matches them but incorrectly, any help with all this mess ?

Upvotes: 1

Views: 85

Answers (1)

Stephen Turner
Stephen Turner

Reputation: 7314

Pleas don't kick yourself too hard...

I think the first two will work perfectly once you replace the string concatenation operator & with the keyword AND.

Upvotes: 2

Related Questions