Solved Update query - from one record to another in same table (1 Viewer)

[Leyton01]

Have you read post #14?

Sorry was just replying to other comments as well.

The current set up is the admin will drag in the file they are processing, the database then opens that file and they can start copying the demographics from the document. If this document is part of a set that has the same (or similar) demographics they can press a button which launches a pop up form that has the details from the last 10 documents entered into the database. The admin then selects the matching record, certain demographics are inserted in the current document record which matches the old document. The current DocID is passed to the pop up form through the openargs parameter of the OpenForms method.

So I know the current DocID (passed in the parameters), I know the Old DocID from which they are copying the data (the admin selects this one using a button) and I know the fields which need to be copied (first name, surname, DOB, suburb etc) but was unsure of the SQL to achieve this. Looks like the SQL method in your answer (and arnelgp's) would achieve the result. Unfortunately the thread diverged into a discussion around database design.

Thank you for your suggestion - I have to do some testing but it looks to be one of the best options.

 

[Leyton01]

Is your project the "middle-man" in a data transfer and validation / verification process?

Yes we very much are the bridge between 2 imperfect, incompatible systems. The database is about recording what we do processing the documents as much as the content/details of the individuals.

 

[The_Doc_Man]

Been there, done that, wore out that T-shirt. I won't bug you because you are getting help here. I just hope that my question helped others to see your place in the big picture. I used to do this kind of stuff with the U.S. Navy all the time. The machine I ran talked to 18 different Naval command offices such as BUPERS (personnel), BUMED (medical), NSIPS (regular Navy personnel), and 15 other monetary, travel, and benefits agencies. So I fully understand being the middle-man and having to translate from one system to another.

 

[June7]

Thank you for your suggestion - I have to do some testing but it looks to be one of the best options.

Okay, but do not run an SQL update action on a record if that record is already in edit mode on form. That is trying to modify a record from two sources at the same time. And a new record via form data entry must first be committed to table before query can act on it.

 

[Leyton01]

Okay, but do not run an SQL update action on a record if that record is already in edit mode on form. That is trying to modify a record from two sources at the same time.

The SQL is going to be run from the pop up form (likely modal). On close, it will refresh/requery the current record in the main form to display the updated information. No field should have focus on the main form as the last thing the user did was press the button to launch the pop up.

 

[jdraw]

@Leyton01
??Is this a continuation of a project you posted about in this 2019 thread?
I have a few more questions related to your post and process(es)? I have questions and comments similar to Mike, but don't want to jump to any conclusion without some detail.

You said you were interested in the document(s)--not the individual. But you collect DOB and some demographics which I interpret as properties of an individual. You have 3 document types which may be provided by the individual. And you want to/currently duplicate the demographic info on each document type for the "individual". It sounds like Individual is an entity within project scope, but you know details and we don't. Must an individual supply 3 documents, 1 of each type, or is there some criteria that determines this?

In overview your set up is along this line:
-you receive documents from or pertaining to individuals
-you record some basic demographic info to uniquely identify the source of the document(s)
-all documents have a unique identifier based on info from the individual and a document type
-you /your org processes these documents in some unspecified manner ????
-processed documents are fed to other, separate systems/processes based on document type
-output from these other systems is used for ????
-there is no analysis of gathered and processed information (no history; trending etc)

What is it that you do to the documents (value added)?? Validation/verification/matching??
A simple description in plain English (no database jargon) could be helpful to readers.

Is there some ultimate use of the data that has been gathered and processed through multiple systems??
Again some brief description of the purpose of the whole exercise may help with design approaches.

What hardware/software for the complex systems that post process your documents?
Why Access?

Encounters and Locations of interest to your org?

Note: I am not trying to breach any confidentiality. I just want you to provide a clear and complete description of the problem/issue or opportunity that you face.

Thanks for clarifying. Seems it is not basic design you are concerned with, but a few adjustments /optimizations to 1 or 2 parts of the existing processes.

Is there some presort or collation done with incoming documents?

 

[Mike Krailo]

Yes, and no. Yes, because at that point in time the demographics may all match. No, because we have to copy the information as it is entered on the document "as is". Some are legal documents and if one of the three has a different bit of information eg "Jo" instead of "Joanne" we have to complete that information as displayed. I am trying to speed up entry for our admin who receive multiple documents on the same email/fax and have either exactly matching information, which most do, or at least close enough for the copy to save time (adjust the first name to "Jo" for that single document if all the other demographics are the same). We are not assuming the person is the same, this is a glorified "auto-fill" for documents that contain similar information.

Understood. It's sometimes hard to imagine what's really happening but you know your business. Hope the changes you make to your system will accomplish what you want for your document admins.

 

[Leyton01]

Thank you all for your help - I was able to achieve the copy using the following code:

strSQLDemographics = "UPDATE tblDocumentLog AS T1, " & _
"(SELECT * FROM tblDocumentLog WHERE DocID = " & Me.DocID & ") AS T2 " & _
"SET T1.Surname = T2.Surname, T1.Firstname = T2.Firstname, T1.MiddleName = T2.MiddleName, T1.DOB = T2.DOB, T1.Gender = T2.Gender,  T1.SecondaryHHS = T2.SecondaryHHS, T1.MHHHS = T2.MHHHS, T1.MHFacility = T2.MHFacility, T1.URN = T2.URN,  " & _
"WHERE (((T1.DocID)=" & Me.txtDocID & "));"

Works well and the users have fed back it is a time saver already.