1 Reply Latest reply on Nov 30, 2012 12:55 PM by beverly

    Query Design Question

    lkeyes

      Hi.... a little embarrassed here that I don't know the answer to this off the top of my head.

       

      I've got a table full of physician names. I have a second table full of physician names that include two extra fields, NPI code, and their practice. I need to assign the NPI and Practice to any of the physicians in the first table that I can, using the second table as a lookup.

       

       

      I've got two tables:

       

      Last, First, NPI Practice

      Jones Jeremy <empty> <empty>

       

      Last, First, NPI Practice

      Jones, Jeremy 1020323, "Community Health"

       

       

      I want to move through every record in the first table; grab the name and then do a search on the second table for the name. if found, I want to insert teh NPI# and Practice into the record into the first table

       

      I think the effect is something like a VLOOKUP in Excel... (which, if I get really stuck....I may have to fall back to...)

       

      or as a nexted SQL statement, something like:

       

      INSERT INTO table1.NPI, table1.practice VALUES(table2.NPI, table2.practice)

      WHERE (SELECT * FROM table2 WHERE table2.last=table1.last)

       

       

      Even though this is a one-off, I'd be happy to see it scripted....in particular, I always seem to have to loop through a table to perform some operation on every record.

       

      Any ideas? Thanks.

        • 1. Re: Query Design Question
          beverly

          It's not foolproof, because names are not always unique. But I often create a temporary relationship between the two tables, matching the fields you deem necessary.

           

          Then in the table with the missing data, you can use a Replace in the field to "set" the related field info based on the temporary relationship.

           

          If you need to Lookup this information often, you'd do we'll to also have an ID field (auto-enter) that would be your real relationship.

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          1 of 1 people found this helpful