Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Richmond, VA
    Posts
    11

    Question Unanswered: Update one table from another...

    I have seen this asked in various ways, but I did not see anything that looked specifically like the problem I am having, so I apologize if this is a repeated question. I am also VERY new to Access, so please bear with me.

    Here is what I have:
    2 tables. Each has the same fields and format for each field. Table 1 (tblProvider) is my main table and table 2 tblCOrilian) is a table of data that I imported into Access that needs to update the information in table 1 (tblProvider). I do not have a primary key assigned for either table nor is there a relationship between the 2.

    Here is my code:

    UPDATE tblProvider, tblCorilian SET tblProvider.NPI = [tblProvider].[NPI], tblCorilian.NPI = [tblProvider].[NPI], tblProvider.CheckedOut = Yes, tblProvider.CheckedIn = Yes, tblProvider.Enumerated = Yes, tblProvider.CheckedOutBy = "Lynn Cline"
    WHERE (((tblCorilian.NPI)>"") AND ((tblProvider.ProviderLastName)=[tblCorilian].[ProviderLastName]) AND ((tblProvider.PhysicalAddress2)=[tblCorilian].[PhysicalAddress2]) AND ((tblProvider.TaxID)=[tblCorilian].[TaxID]) AND ((tblProvider.LegacyNumber)=[tblCorilian].[LegacyNumber]));

    What I am trying to do is update table 1 (tblProvider) NPI field with the data in the NPI field on table 2 (tblCorilian) based on a comparison od several fields that should match in both tables. However, when I run the update query, everything BUT the NPI field is updated correctly.

    Any help on this issue would be greatly appreciate!!

    Thanks!

  2. #2
    Join Date
    Mar 2006
    Posts
    73
    What I would try doing is linking tblCorilian into your database that contains the tblProvider.

    What this does is it takes a version of tblCorilian that can be updated, and then places that data that has been since updated into the linked copy of that database. Use the linked version of tblCorilian in your Update Query, and what this will do is everytime you update the fields in tblCorilian on a seperate database file, it will update the tblCorilian on the database file that also has tblProvider.

    All you'd have to do is double click the update query, and volia! your fields have been updated!

    I hope this helps any...if it doesn't I'll have to double check on things and make sure I'm doing it right in my werid ways heh..

  3. #3
    Join Date
    Oct 2003
    Location
    Richmond, VA
    Posts
    11
    Thanks for the quick reply. I have spent all day trying to get this to work. I will post again if that does not work.

    Thanks again!!

  4. #4
    Join Date
    Mar 2006
    Posts
    73
    Not a problem..I'm havin a bit of a problem with mine right now but I know thats one of the better ways on doing it..cause it gives ya the most up-to-date information on the table.

  5. #5
    Join Date
    Oct 2003
    Location
    Richmond, VA
    Posts
    11
    I tried "linking" them up, but no luck. The tblCorilian and tblProvider are both part of the same database. When I go in and set up my query as a SELECT query, I see the data and it looks correct, but when I change it to an Update query, it wont take the data from tblCorilian.NPI and put it in tblProvider.NPI.

    Any other thoughts or suggestions?

    Thanks!

  6. #6
    Join Date
    Oct 2003
    Location
    Richmond, VA
    Posts
    11
    Additional information: The other fields that I am trying to update are updating just fine. It is only when I try to pull in the NPI from one table and add it to another that I have a problem. Also, the SELECT part of the query seems to be running fine. I have a feeling I am just missing a parameter or I have something set up wrong in my UPDATE query.

    Any help is greatly appreciated.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have a little closer look at the part where you're supposedly trying to "update table 1 (tblProvider) NPI field with the data in the NPI field on table 2 (tblCorilian)":

    UPDATE tblProvider, tblCorilian SET tblProvider.NPI = [tblProvider].[NPI], tblCorilian.NPI = [tblProvider].[NPI], tblProvider.CheckedOut = Yes, tblProvider.CheckedIn = Yes, tblProvider.Enumerated = Yes, tblProvider.CheckedOutBy = "Lynn Cline"
    You're setting tblProvider.NPI equal to itself. Try this:


    UPDATE tblProvider, tblCorilian SET tblProvider.NPI = [tblCorilian].[NPI], tblCorilian.NPI = [tblProvider].[NPI], tblProvider.CheckedOut = Yes, tblProvider.CheckedIn = Yes, tblProvider.Enumerated = Yes, tblProvider.CheckedOutBy = "Lynn Cline"


    Also, I'm willing to bet you could use tax id as your primary keys for both tables. I've never heard of more than one entity having the same tax id...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Oct 2003
    Location
    Richmond, VA
    Posts
    11
    That did the trick Teddy, thanks.

    This is an unusual situation because providers can operate under the same Tax ID with different Legacy Numbers. It's weird, I know.

    Thanks again for the assistance.

Posting Permissions

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