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

[Leyton01]

I just seem to be going around in circles trying to figure out this update query design for something I thought was simple.

I want to copy a few fields (surname, firstname, DOB) to the current record from an older record the user has identified with a button on a continuous form.
I know the new record DocID 'TargetDocID' and I know the old record 'PrevDocID'.

I am constructing the SQL statement in VBA but just unsure how I use Select option for both the old and the new record? This is a simple update from PrevDocID fields --> TargetDocID fields


UPDATE DocumentLog dl1 INNER JOIN DocumentLog dl2 On
??
SET dl1.Surname = dl2.Surname etc.

Just not clicking with me at the moment - any help is appreciated.

 

[theDBguy]

Maybe d1.TargetDocID=d2.PrevDocID?

 

[Leyton01]

Sorry I probably wasn't clear in the original post - TargetDocID and PrevDocID are values in the DocID column which represent the to and from record numbers, not separate fields.

ie the user has DocID 555 open and they select "copy from" DocID 123. These 2 values are available in vba as TargetDocID (555) and PrevDocID (123). I want to copy the name, DOB fields etc from Document record 123 and put them in Document record 555.

I want to define that I am copying the values from d1.DocID=123 to d2.DocID=555 in the SQL.

 

[arnelgp]

you may try:

UPDATE DocumentLog AS T1,
(SELECT * FROM DocumentLog WHERE DocID = PrevDocID) AS T2
SET T1.SURNAME = T2.SURNAME
WHERE (((T1.DocID)=TargetDocID));

 

[Leyton01]

This looks like it will achieve it - I will give it a go on Monday. Thanks.

I assume the separator for multiple SET statements is a comma?
SET T1.SURNAME = T2.SURNAME, T1.FIRSTNAME = T2.FIRSTNAME, etc

 

[arnelgp]

if you will do it using VBA:

Dim sSQL As String
sSQL = _
"UPDATE DocumentLog AS T1, " & _
"(SELECT * FROM DocumentLog WHERE DocID = " & PrevDocID & ") AS T2 " & _
"SET T1.SURNAME = T2.SURNAME " & _
"WHERE (((T1.DocID)=" & TargetDocID & "));"

Currentdb.Execute sSQL, dbFailOnError

 

[Mike Krailo]

This is not exactly what you asked for but in this example, there is a copy button on each line of the continuous form and it simply copies all values from the current line to some eventually hidden fields in the header of the form (fields visible for clarity). Then the button Paste Values at the top will paste all (or whatever you setup in vba) to the currently selected record. You can also create new records using whatever values are needed from the copied record as well. The code is fairly simple. There is also a normally hidden text box CurrID in the header section as well to keep track of the current record ID.

Private Sub CopyBtn_Click()
   Me.hidCustID = Me.CustID
   Me.hidCoName = Me.CoName
   Me.hidFName = Me.FName
   Me.hidLName = Me.LName
   Me.hidSalesperson = Me.Salesperson
   Me.hidST = Me.ST
End Sub

Private Sub Form_Current()
   Me.CurrID = Me.CustID
End Sub

Private Sub PasteCurrentBtn_Click()
   Dim StrSQL As String
   StrSQL = "UPDATE Customer SET Customer.CoName = '" & Me.hidCoName & "', Customer.FName = '" & Me.hidFName _
          & "', Customer.LName = '" & Me.hidLName & "', Customer.Salesperson = '" & Me.hidSalesperson _
          & "', Customer.ST = '" & Me.hidST & "'" _
          & " WHERE Customer.CustID = " & Me.CurrID
          
   If MsgBox("About to modify current CustID: " & Me.CurrID & ", shall we continue?", vbYesNo, "Copy stored record data...") = vbYes Then
      DoCmd.RunSQL (StrSQL)
      Me.Refresh
   End If
End Sub

 

[MarkK]

To copy a person's name and date of birth into a different record in the same table is a mistake. That is a fundamental data design error. If you are just helping this OP make this error, without any warning that it is not advisable, then you are not really, IMO, helping this OP.

 

[Mike Krailo]

Well now he's been warned. Maybe there is more than one name in the record, we don't know.

 

[MarkK]

Maybe there is more than one name in the record, we don't know.

More than one data point in one record is a mistake.

 

[jdraw]

Time for Leyton01 to tell us more. Sounds like a design problem, but more detail required.

 

[Mike Krailo]

I agree, it does sound like a problem. Let's wait and see what the real story is.

 

[Leyton01]

We process documents for individuals and upload them into another storage system. The documents are all given their own ID, this is the primary key and how all the processing is linked. For us, the document is the unique information we are dealing with and not the individual. We collect some demographics directly from the document, including the name and DOB (they are handwritten so this has to be collected manually). The system we upload them to is a complex and dynamic record which does not operate on the individual's name either but a combination of encounters and locations. It does not make sense for us to create individuals as a separate table and and try to match their information historically. It is a point-in-time collection of demographics for that document at time of processing, nothing more.

We sometimes get 3 types of documents from the same individual. We know that, at that point in time, the demographics are the same and rather than repeat the entry for each document type (all of which go to separate other systems depending on their content) it is easier for staff to duplicate it. Sometimes the demographics are incomplete which is also why we treat the document as the primary key and do not attempt to match the individual's information, the consistency of information which is provided on these forms is not reliable. We have no control over the documents themselves, their content or the system to which we transfer them.

"To copy a person's name and date of birth into a different record in the same table is a mistake. That is a fundamental data design error."
To make sweeping statements on assumptions is the true mistake.

 

[June7]

Exactly how many fields do you want to 'copy'? If form is bound to destination table and focus is on record you want to update (the 'current' record you reference), there is no need for an UPDATE sql. Use unbound combobox to select source record and reference combobox columns to populate the 'current' record fields. Or open a recordset of source data and reference its fields to populate 'current' record.

However, if you have an unbound form with 2 comboboxes to select source record and destination record, an UPDATE sql could be like:

UPDATE table, (SELECT * FROM table WHERE DocID=[source DocID]) AS Q
SET table.field1=Q.field1, table.field2=Q.field2
WHERE DocID=[dest DocID]

Another approach for repetitive data entry on bound form is VBA to set the DefaultValue property of a control when new input is entered. Set DefaultValue to that new input and it will carry forward to subsequent records until a new input is typed/selected.

 

[Mike Krailo]

Thank you for that explanation Leyton01, I still think you can benefit from using a PersonID and a table for that.

We sometimes get 3 types of documents from the same individual.

That's a One to Many relationship right there.

So you are breaking the rules intentionally and prefer to not enter customer or person data into it's own table and simply link the PersonID to each of the related documents. You can certainly do that, it just breaks the rules of normalization. What I don't understand is if you have to enter in the data once anyway, why not enter it into an normalized table? I understand that you won't be using the person in that table once, but in spite of that fact it still would be better to use the proper table with a PersonID anyway. You don't really lose anything by doing it that way. What you gain is having main form of Person's that link automatically to a sub form of Documents for that individual. That's a pretty big benefit in my eye's and there is no need to copy any data in that case.

As far as how you actually have to upload or enter data into another system that you say is complicated, that makes me wonder what Access is actually doing for you to begin with. It sounds like a lot of manual inputting of data to another system anyways or are you trying to import the data from Access into that other system?

 

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

 

[Leyton01]

That's a One to Many relationship right there.

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.

 

[Leyton01]

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.

The simple overview (and why I was being a bit vague) is that we are dealing with health documents, some of which are legal, checking them, matching and then making them available in a health system. It is a lot of manual work because we are dealing with manually completed paper forms (this is how our state works), done by individuals with no training/knowledge and fitting them into a regimented health database which runs on encounters, not just individuals.

The value add we provide is the checking of the documents to make sure they meet criteria and feeding back if they do not. It is also the translation of that information from the paper, public domain into a digital, corporate domain. The database we run is more around recording the way we process a document (did a receipt email go out, what are the problems identified, has the document been QA'd in the target system etc) rather than recording the individuals information as a source of truth. The demographics we do record allows us to develop deidentified reports on the types, ages, locations of people sending in docs which is important for our resource focus. This info is not necessarily available in the target system as not all docs make it there if they do not meet criteria. So for us, the document is king - it is what we are actually processing. The individual is a data field associated with that document and we cannot assume they are definitely linked.

Yes this is the same project from a few years ago. At that time the database was rewritten to improve the design to better match the demand. Originally it was designed to cope with 1 document a day, now that can be 100. The DocumentID was made the centre of the database, tables and relationships were recreated accordingly and, for instance, the problem list was completely changed from multiple tables with multiple issues listed for each document to a single issue list and lookup reference table. The improvement was significant and better followed good design rules but the individual demographics and the way we use that information, does not suit this kind of design.

 

[The_Doc_Man]

I'm going to ask a dumb question but it might help put things in perspective.

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

EDIT: You posted your reply while I was still typing.

 

[June7]

Have you read post #14?