Update one table based on data in another

I have one MS Access 2003 DB.  I have 2 tables tPatient and tChartLocUpd.

tPatient has many fields but I would like to update 'folderloc' (text) based on when ID in this table matches PtMR data in table tChartLocUpt when they match.

Ex data in tPatient (among other field values)

ID     folderloc
123  Blue
456 Yellow

Ex data in tChartLocUpt (only 2 fields)

Ex Data:

PtMrn  Chartloc
123     Green
456     Black

After the update tPatient would look like
Ex data:

123     Green
456     Black

Also would like to have a basic error check in that if in tChartLocUpt  there is a PtMrn that doesn't exist in tPatient (ID) then post a message

tPatient ID = Long Int (autonum)
Folderloc = Text

tChartLocUpt   PtMrn = Long Int
ChartLoc = Text

I am not very good at SQL in VBA so help is appreciated!
thandelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You can create this query in the query designer.

First create SELECT query with a JOIN between both tables with an INNER JOIN.

Then change the query to an UPDATE query.
Bill BachPresident and Btrieve GuruCommented:
Do you need one query, or will two suffice?

The first query, to detect problem data, could look as simple as this:
    SELECT PtMrn FROM tChartLocUpt WHERE PtMrn NOT IN (SELECT ID FROM tPatient)
This would generate a listing of missing values, which can be addressed first.  

The second query should handle the updates:
    UPDATE tPatient SET folderloc = (SELECT TOP 1 ChartLoc FROM tChartLocUpt WHERE tChartLocUpt.PtMrn = tPatient.ID)

Of course, as with any query, you'll want to test this on a backup of the data first, to make sure that all possible issues are handled properly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
FloderLoc does not belong in tPatient.  If you need this information, join to tChartLocUpt to get it.  Copying data from one table to another is a violation of second normal form and leads to data anomalies.  Changing the value in one table does not propagate the change to any other table so you are trying to do this manually.  The problem is you have to trap the update every place it could occur.  You might do that now but if you make changes to the app in the future, will you remember to update tPatient from there?  Would your successor?  Do some reading on normalization and the sooner you fix this design flaw, the less of a ripple effect it will have.

The basic change is to remove the duplicate column from tPatient and then change any queries that need the data to include a join to tChartLocUpt  to get it.

To do your "error" check, use the query wizard.  Create an unmatched query.

To solve this problem permanently, create a relationship between the tables and enforce referential integrity.  That will prevent future orphan records.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

thandelAuthor Commented:
I'm basically implementing it in this manner as I'm using a table to a user can enter values into a predefined update query easily.
PatHartmanCommented:
You can violate accepted practice that has existed since relational databases came into being over 40 years ago.  Just understand what you are doing when you go off the reservation and be prepared to pay the price.
thandelAuthor Commented:
No sure what that means... does it matter how the data is updated so long as the physical 0's and 1's make it over to provide the updated data?    Remember the data in tChartLocUpt  is a one time look up... once its used to update the main table tChartLocUpt's contents are deleted. We are talking about 6-12 data entries in tChartLocUpt compared to over 20,0000 entries in tPatient.    I don't see any difference between a user manually making an updated data entry one at a time vs. a query doing the work for the user based on data in another table.
PatHartmanCommented:
Yes, it matters very much when you duplicate data.  When the same piece of data is located in two separate tables, your code must be responsible for keeping that data in sync.

You didn't explain that the duplicate data was only temporary.  I probably wouldn't have chosen that technique but it will work.  However, after you run the update query, you should delete all the rows from the temp table.  Rows should only exist in the temp table until they have been applied to the permanent table.  You need to be careful how you schedule this process.  You can't have people doing data entry when you are running the update process.  If you run this process during the day, I would suggest adding an additional field to the temp table to indicate "selected".  The first query will select all rows and update the selected field.  The second query will select all "selected" rows and update the permanent table.  The third query will delete all the "selected" rows.  In this way, you can allow users to update while you update so if they add a row after the selection process, it won't get deleted because it won't be marked selected.  It will go in the next batch run.  All in all, it would have been simpler to do direct updates.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.