Sequential Record Update

Question

Can a SQL script be written to sequentially go through all the records in a table and, based on certian criteria, copy text data from a specific field and concatinate the text, in the order found, into a field of one record in another table?

Donald Quigley · Answer

Jefferson, Please understand that I have not tried any of your suggestions becuse the code I already had worked, as I have tried to explain to you. Dim numvalue As Integer numvalue = rs.IDER.Value : SQL = "UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & """ & strvalue & """ WHERE tblCensus4.[IDER]=" & numvalue Don

Jefferson S. Motta · Answer

Did you try this: THIS WORKS: "UPDATE tblCensus4 SET tblCensus4.[Desc] = '" & tblCensus4.[Desc] & Chr(13) & Chr(10) & "Ida Sant''Anna Louise Wagner" & "' WHERE tblCensus4.[IDER]=168"

Jefferson S. Motta · Answer

Sorry, I did not understand irony. Does it helped you?

Donald Quigley · Answer

Jefferson, Yes it works now as it has always been working with the code you say is "not valid" ! Can we please just agree to disagree? Don

Jefferson S. Motta · Answer

Does it works now?

Donald Quigley · Answer

Jefferson, I'm not sure how to respond to you. All I know is that the code as written, and provided to be by a knowledgeable VBA programmer, does work properly and does update my table the way I wanted it to. I changed the data to include a record with a single quote, as shown below, but there were no changes in the results. And then I searched the tables again and found many example of " ' " in the name fields (O'Connor, O'Heron, etc), also with no errors. Maybe there are two ways to arrive at the same result? Don ImmediateView UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & "Ida Louise Wagner" WHERE tblCensus4.[IDER]=168 UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & "June Barbara Quigley" WHERE tblCensus4.[IDER]=168 UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & " I da St' Louise Wagner " WHERE tblCensus4.[IDER]=179 UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & "Francis Albert (Frank) Quigley" WHERE tblCensus4.[IDER]=179 UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & "June Barbara Quigley" WHERE tblCensus4.[IDER]=179 Results in tblCensus4.[Desc] Household Members: Francis Eugene (Frank) Quigley Ida St' Louise Wagner Francis Albert (Frank) Quigley Ida St' Louise Wagner Francis Albert (Frank) Quigley June Barbara Quigley

Jefferson S. Motta · Answer

Hello, sorry to tell but you don't know how SQL works you are not getting error with VBA, but not knowing the SQL language. to execute successfully your query it's must looks like: "UPDATE tblCensus4 SET tblCensus4.[Desc] = '" & tblCensus4.[Desc] & Chr(13) & Chr(10) & "Ida Louise Wagner" & "' WHERE tblCensus4.[IDER]=168" You must have the new value to update a column text surrounding by " ' ". Example: "UPDATE myTable SET nameCOlumn = 'My Value Column';" the "My Value Column" must begin and ends with "'". And for safety you need add a replace for column name like : Replace(valueFromAnyWhere, "'", "''"); Explanation: if a name contais a "'" it will generate error THIS GENERATES AN ERROR: "UPDATE tblCensus4 SET tblCensus4.[Desc] = '" & tblCensus4.[Desc] & Chr(13) & Chr(10) & "Ida Sant'Anna Louise Wagner" & "' WHERE tblCensus4.[IDER]=168" THIS WORKS: "UPDATE tblCensus4 SET tblCensus4.[Desc] = '" & tblCensus4.[Desc] & Chr(13) & Chr(10) & "Ida Sant''Anna Louise Wagner" & "' WHERE tblCensus4.[IDER]=168"

Donald Quigley · Answer

Jefferson - I am not an experienced VBA programmer. All I know is that the code works. Here are the first several passes though the recordset from Debug.Print SQL; Var: UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & "Ida Louise Wagner" WHERE tblCensus4.[IDER]=168 UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & "June Barbara Quigley" WHERE tblCensus4.[IDER]=168 UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & "Ida Louise Wagner" WHERE tblCensus4.[IDER]=179 UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & "Francis Albert (Frank) Quigley" WHERE tblCensus4.[IDER]=179 UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & "June Barbara Quigley" WHERE tblCensus4.[IDER]=179 UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & "Robert Weston (Bob) Quigley" WHERE tblCensus4.[IDER]=179 UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & "Theresa Margaret Anderson" WHERE tblCensus4.[IDER]=393

Jefferson S. Motta · Answer

THIS IS NOT A VALID SQL STAMENT: SQL = "UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & " "" & strvalue & "" " WHERE tblCensus4.[IDER]=" & numvalue YOU NEED to UPDATE THE [Desc] FIELD WITH A VALID SQL EXPRESSION , SO YOU NEED USE " ' " IN THE VALUE CONTENT TO USE THE SET . tblCensus4.[ Desc ] = CONCAT(tblCensus4.[ Desc ], '" & Chr(13) & Chr(10) & strvalue & "' ) PLEASE, DEBUG THE CODE FROM THE FIRST LINE AND SHOW ME THE VALUE FROM SQL VAR. * SORRY THE CAPS. Best, Jeff

Donald Quigley · Answer

Jefferson - I got the code in black to work based on a lot of help from Stephan Landau in the Microsoft Accss Facebook group: Dim strvalue As String : SQL = "UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & strvalue & " WHERE " & CStr(rs.[IDER]) & " = tblCensus4.[IDER];" Dim numvalue As Integer numvalue = rs.IDER.Value : SQL = "UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & """ & strvalue & """ WHERE tblCensus4.[IDER]=" & numvalue I appreciate you taking the time to help me as well.

Jefferson S. Motta · Answer

Ups, now I see: we are not respecting the SQL statement, try this and tell me please: SQL = "UPDATE tblCensus4 SET tblCensus4.[Desc] = CONCAT(tblCensus4.[Desc], '" & Chr(13) & Chr(10) & strvalue & "') WHERE " & CStr(rs[IDER]) & " = tblCensus4.[IDER];"

Donald Quigley · Answer

Jefferson - That line of code gives me a syntax error, with or w/o a period between rs and [IDER]. The column name is IDER in tblCensus4 and it is an Autonummuber data type. In tblCensus1Sorted the column name is IDER and it is an Number data type

Jefferson S. Motta · Answer

SQL = "UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & strvalue & " WHERE " & CStr(rs[IDER]) & " = tblCensus4.[IDER];" I don't know what is your column name, so you need replace IDER by your column name.

Donald Quigley · Answer

I'm sorry, Jefferson, but where would that code be inserted? .. and shouldn't there be a period between rs and [IDER]?

Jefferson S. Motta · Answer

Hi, the error is a runtime error from VBA. you need convert to string your var "SQL", like " & CStr(rs[IDER])) & "

Salman Beg · Answer

Type mismatch error means I think the problem with datatype. Check datatype. Thanks

Donald Quigley · Answer

Jefferson - That did not work. I get a Run-time error '13': Type mismatch.

Jefferson S. Motta · Answer

Hello, you are missing the clausula where: WHERE " + rs[IDER] + " = tblCensus4.[IDER];" Public Sub Recordset() Dim db Dim rs Dim strvalue As String Dim SQL As String Set db = CurrentDb Set rs = db.OpenRecordSet( "tblCensus1Sorted" ) If Not (rs.EOF) Then Do While Not rs.EOF strvalue = rs.GivenName.Value & " " & rs.Surname.Value 'View strvalue in Immediate Window Debug.Print strvalue SQL = "UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & strvalue WHERE " + rs[IDER] + " = tblCensus4.[IDER];" DoCmd.RunSQL SQL rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing End If

Donald Quigley · Answer

IDER refers to a common field name in the two tables. The problem is that the first table has multiple records with the same IDER value and the second table has only one record for that IDER value. I'm trying to concatenate fields from the first table for each IDER value into the second one for that IDER value. The attached code will get all the right values from the first table, but it throws them all into every record in the second table; ie, with no regard to the IDER values.

Jefferson S. Motta · Answer

You can use too: SELECT CAST(column1 as nvarchar) + ',' + CAST(column2 as nvarchar)+ ',' + CAST(column3 as nvarchar) AS YOUR_NAME_COLUMN What you means for: IDER?

Donald Quigley · Answer

Here is my problem. I need to concatenate two fields from multiple records into a single field, separated by CR&LF, and copy them into one field of one record in a related table (common "IDER"). I thought this would do it, but I keep getting this text box: Enter Parameter Value? strvalue Code Public Sub Recordset() Dim db Dim rs Dim strvalue As String Dim SQL As String Set db = CurrentDb Set rs = db.OpenRecordSet( "tblCensus1Sorted" ) If Not (rs.EOF) Then Do While Not rs.EOF strvalue = rs.GivenName.Value & " " & rs.Surname.Value 'View strvalue in Immediate Window Debug.Print strvalue SQL = "UPDATE tblCensus4 SET tblCensus4.[Desc] = tblCensus4.[Desc] & Chr(13) & Chr(10) & strvalue;" DoCmd.RunSQL SQL rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing End If As you can probably tell, I know only enough about VBA to get myself in trouble. Any help would be appreciated.

Donald Quigley · Answer

Th CONCAT part is understood. But how to get the 3 records into a one record field (based on the IDER) is the issue.

Jefferson S. Motta · Answer

select CONCAT(GivenName, ' ' + SurName) as myTextData From MyTABLE WHERE [YOUR_CRITERIA] ORDER BY [YOUR_fieldId]

Donald Quigley · Answer

Suraj Kumar · Answer

What is your expected output? Please provide that also.

Donald Quigley · Answer

I want to concatenate text items like these into one field in another table.

Suraj Kumar · Answer

You can provide more details like the table structure/schema, sample data, and expected output for an answer.