Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Location
    Clearwater, Florida
    Posts
    3

    Question Unanswered: converting access to sqlserver 7

    I have a query which fixes area codes. it runs:
    UPDATE (nocall INNER JOIN pildta ON (nocall.SUFF = pildta.suff) AND (nocall.PREF = pildta.preff) AND (nocall.AREA = pildta.area)) INNER JOIN ZipCodeUS ON pildta.Zip = ZipCodeUS.ZIP SET nocall.AREA = [a/c]
    WHERE (((IIf([nocall]![area]<>[a/c],1,0))=1));

    I managed to replace the IIF with a CASE for displaying the unmatched tables like this:
    SELECT dbo.nocall.AREA, dbo.pildta.Zip, dbo.ZipCodeUS.[A/C], case when [a/c]=[nocall].[area] then 1 else 0 end as match
    FROM dbo.pildta INNER JOIN
    dbo.ZipCodeUS ON dbo.pildta.Zip = dbo.ZipCodeUS.ZIP INNER JOIN
    dbo.nocall ON dbo.pildta.area = dbo.nocall.AREA AND dbo.pildta.preff = dbo.nocall.PREF AND dbo.pildta.suff = dbo.nocall.SUFF

    How can I make this an update query based on [match] having a value of 1?

  2. #2
    Join Date
    May 2002
    Posts
    13
    How about moving the [a/c]=[nocall].[area] from a CASE clause to a WHERE clause (which should limit the results to records that would return a 1 in your CASE clause), and then making that query a subquery to your UPDATE command? E.g., UPDATE table SET column = value WHERE tableprimarykey IN (subquery).

  3. #3
    Join Date
    May 2002
    Location
    Clearwater, Florida
    Posts
    3

    Question

    ok. What am I doing wrong. I'm now trying this:
    UPDATE nocall
    SET AREA = [a/c] IN (SELECT ZipCodeUS.[A/C] FROM ZipCodeUS INNER JOIN pildta ON ZipCodeUS.ZIP = pildta.Zip INNER JOIN nocall ON pildta.area = nocall.AREA AND pildta.preff = nocall.PREF AND pildta.suff = nocall.SUFF)
    WHERE [ctr]=[ctr] IN (SELECT nocall.AREA, ZipCodeUS.[A/C], nocall.ctr FROM ZipCodeUS INNER JOIN pildta ON ZipCodeUS.ZIP = pildta.Zip INNER JOIN nocall ON pildta.area = nocall.AREA AND pildta.preff = nocall.PREF AND pildta.suff = nocall.SUFF AND ZipCodeUS.[A/C] <> nocall.AREA)

    When i try to save, I get incorrect syntax near the keyword IN, incorrect syntax near the word WHERE

  4. #4
    Join Date
    May 2002
    Posts
    13
    Well, the first error in the query in your last post is "SET AREA = [a/c] IN (..." This would have to be like "SET AREA = [a/c] WHERE AREA IN (..."

    I'm not sure I understand what the query is to accomplish well enough to give you complete rewrite, so tell me if this is correct:

    Your goal is to replace values in NoCall.Area with values from ZipCodeUS.[A/C] when and only when:

    1. NoCall.Area does not already equal ZipCodeUS.[A/C]
    AND
    2. ZipCodeUS has a matching record in Pildta based on .Zip
    AND
    3. NoCall has a record that matches Pildta's Area, Preff, and Suff

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •