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!!
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..
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.
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.