Truncate and Convert 1 Row value to Multiple rows and Vise-Versa

[Ramya_mudambi]

Dear all,
In the attached Database (Access 2007), there are 2 tables - "Source" and "Expected_Output"

1: Table "Source" contains values CONCATENATED in a single row with concatenation string ' OR name = '
With limited knowledge in Access, i tried to perform text-column trying to see if some delimiter could be removed, it messed-up the expected output.

In the attached DB, table "Expected_Output" contains the required output.

2. DB should have the ability to: the values that we obtained in the "Expected_Output" should be converted from multiple rows to single row with concatenations as in the "Source" table
Once again i tried using the ' OR name = ' in the query for concatenation and failed again.

PLEASE someone kindly help me

Many thanks,
Ramya

 

[bob fitz]

Can you post a copy in A2003 mdb format.

 

[Ramya_mudambi]

Hi Bob,
Please find attached the DB in Access 2003 MDB format.

Kindly help

 

[Ramya_mudambi]

Hi Bob,
Just writing to see if the file opened in the expected file format.

 

[JHB]

Create a new form, put a button on it and place the below code in the button's on click event.

  Dim dbs As dao.Database, rst As dao.Recordset, rstInsert As dao.Recordset, strSplit() As String, Response

  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("SELECT Source.ADgroups FROM Source")
  Response = MsgBox("Empty table 'Expected_Output'?", vbYesNo)
  If Response = vbYes Then
    dbs.Execute ("Delete * from Expected_Output")
  End If
  Set rstInsert = dbs.OpenRecordset("Expected_Output")
  
  strSplit() = Split(rst![ADgroups], "'")
  
  For x = 0 To UBound(strSplit) - 1
    If InStr(1, strSplit(x), "=") = 0 Then
      rstInsert.AddNew
      rstInsert![ADGroupNames] = strSplit(x)
      rstInsert.Update
    End If
  Next x

 

[Ramya_mudambi]

Hi JHB,
In the attached DB, I added the code on the Button's click event, But the code doesn't seem to trigger.

Can you please advise on what could be missing.

 

[JHB]

Replace the line declaring (Dim) line with this:

  Dim dbs As dao.Database, rst As dao.Recordset, rstInsert As dao.Recordset, strSplit() As String, Response, x As Integer

 

[Ramya_mudambi]

Thankr JHB, the code worked as anticipated.

On another button click, I'm trying to convert values in "expected_output" to "source" table format. ' OR name ' is getting concatenated to all the rows and I'm unable to put the values in a single cell

 

[namliam]

Why do it this complicated way, why not try the looping of the recordset like I suggested in you other thread?

http://www.access-programmers.co.uk/forums/showthread.php?t=259147

 

[Ramya_mudambi]

Success, I was able to loop through the record set. Found, I was looping incorrectly.
Hurray !! Learnt using record set and code is working