none
Normalizing Data in Table RRS feed

  • Question

  • Hello,

    I recently inherited a Access 2010 database that has been misused for years. I'm currently running Win7 on my PC. I need to clean up a table called DesignComments. The ID field contains text that should be in the Comments field. What I want to do is take the text from the ID field and put it at the end of the text in the Comments field of the preceding record containing a numeric value in the ID field. Separating the text with a pipe. I've included some jpeg of what I currently have and what I need:

    Currently have:

    What I need:

    Any suggestion would be helpful. There are over 25k records and i do not wish to clean this up manually. Please let me know if you have any questions.

    Thanks, Kevin

    Friday, October 6, 2017 6:14 PM

Answers

  • Can you go into more detail regarding your suggestion? I'm not sure how to set this up.

    Hi Kevin,

    I made a small Sub that does what I described in "human" language. I called it Step1, because probably you have to run additional steps (e.g. get rid  of the empty records). You have to modify the routine to match your real names.

    This all is independant of how you want to proceed with the piped Comments.

    Sub Step1()
      Dim total_set As Recordset
      Dim single_set As Recordset
      Dim cur_sql As String
      Dim lastID As Long
      
      cur_sql = "SELECT * FROM Total_tbl" _
              & " ORDER BY UniqueID"
      Set total_set = CurrentDb.OpenRecordset(cur_sql)
      
      Do While (Not total_set.EOF)
        If (IsNumeric(total_set!ID)) Then
          lastID = total_set!UniqueID
        Else
          cur_sql = "SELECT * FROM Total_tbl" _
                  & " WHERE UniqueID = " & lastID
          Set single_set = CurrentDb.OpenRecordset(cur_sql)
          single_set.Edit
          single_set!Comments = single_set!Comments & "|" & total_set!ID
          single_set.Update
          
          total_set.Edit
          total_set!ID = Null
          total_set.Update
        End If
        total_set.MoveNext
      Loop
    End Sub
    

    The code is not tested.

    Imb.

    • Marked as answer by KevinATF Monday, October 9, 2017 2:50 PM
    Saturday, October 7, 2017 8:35 PM
  • Hi Kevin,

    You could use RecordSet to Move Previous/Next to add comments to the end of the text in Comments field of the preceding recording.

    Here is the example.

    Sub Test()
    
    Dim rst As Recordset
    
    sqlString = "select * from DesignComments order by UniqueID asc"
    
    Set rst = CurrentDb.OpenRecordset(sqlString)
    
    If Not (rst.EOF And rst.BOF) Then
    
        rst.MoveFirst
    
        Do Until rst.EOF = True
    
            ID = rst!ID.Value
    
            If Not IsNumeric(ID) And Len(ID) > 0 Then
    
            'empty current ID
    
             rst.Edit
    
             rst!ID = ""
    
             rst.Update
    
            'move to previous record to add comments
    
            'check id to make sure current record need add comments(in case of rows like Row18,Row19)
    
            moveCount = 0
    
            Do Until IsNumeric(rst!ID)
    
            rst.MovePrevious
    
            moveCount = moveCount + 1
    
            Loop
    
            'add comments
    
            rst.Edit
    
            rst!Comments = rst!Comments & "|" & ID
    
            rst.Update
    
            'move to current record
    
            rst.Move moveCount
    
            End If
    
            rst.MoveNext
    
        Loop
    
    End If
    
    rst.Close
    
    Set rst = Nothing
    
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by KevinATF Monday, October 9, 2017 2:50 PM
    Monday, October 9, 2017 6:15 AM

All replies

  • Kevin –

    Here’s a few suggestions

    1.        Consider putting the orphan comments in additional columns. “Approved” and “Customer opted” look like info that applies separately from “comments”.
    2.        Since you’re moving data around, it seems to me that the uniqueID doesn’t matter.
    3.        What is the different between “Color Card Available” and “Color Card Available 12” ?
    4.        Write a query to select the rows in which the “Design” is null and write a query to deal with them OR write some VBA and deal with each row one at a time (the latter showing the extent of my SQL ability).

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Saturday, October 7, 2017 1:50 AM
  • Hi Kevin,

    Judging from the green triangle, it is an Excel sheet, and not an Access table. But you can link the sheet to Access.

    In descriptive language you could do the next steps:

    - Open a recordset from the sheet, and loop through all the records.

    - When ID contains a numeric value (IsNumeric) then assign UniqueID to a variable lastID, and go the the next record.

    - IF ID is not numeric

        - open a single recordset, using: " WHERE UniqueID = " & LastID

        - add ID of the looping set to Comments of the single recordset, separated by "|"

        - close the single recordset

        - clear ID from the looping set 

    - Close the looping set

    Imb.

    Saturday, October 7, 2017 5:26 AM
  • Hi Imb,

    Thanks for your response. This is actually a table in Access. I just copied a few records and put them in an Excel spreadsheet for this post. I could have just taken a screen shot of the table (lol).

    Can you go into more detail regarding your suggestion? I'm not sure how to set this up.

    Thanks again.

    Kevin

    Saturday, October 7, 2017 3:55 PM
  • Hi Kevin,

    Pardon me for jumping in, but a properly normalized table would probably have a child table for the multiple comments rather than one field containing them (separated by a pipe symbol). I imagine you might be planning to use the pipe symbol to allow you to parse the comment field such as find out how many comments there are per record and such. If so, then I think  a separate table would be more appropriate.

    Just my 2 cents...

    Saturday, October 7, 2017 5:39 PM
  • I agree with .theDBguy about a separate table.

    I find it easy to fix something like this in Excel.  Post back which way you will go with to correct your data.

    Is the number associated with 'Color Card Available' a description or quantity?


    Build a little, test a little

    Saturday, October 7, 2017 6:33 PM
  • Can you go into more detail regarding your suggestion? I'm not sure how to set this up.

    Hi Kevin,

    I made a small Sub that does what I described in "human" language. I called it Step1, because probably you have to run additional steps (e.g. get rid  of the empty records). You have to modify the routine to match your real names.

    This all is independant of how you want to proceed with the piped Comments.

    Sub Step1()
      Dim total_set As Recordset
      Dim single_set As Recordset
      Dim cur_sql As String
      Dim lastID As Long
      
      cur_sql = "SELECT * FROM Total_tbl" _
              & " ORDER BY UniqueID"
      Set total_set = CurrentDb.OpenRecordset(cur_sql)
      
      Do While (Not total_set.EOF)
        If (IsNumeric(total_set!ID)) Then
          lastID = total_set!UniqueID
        Else
          cur_sql = "SELECT * FROM Total_tbl" _
                  & " WHERE UniqueID = " & lastID
          Set single_set = CurrentDb.OpenRecordset(cur_sql)
          single_set.Edit
          single_set!Comments = single_set!Comments & "|" & total_set!ID
          single_set.Update
          
          total_set.Edit
          total_set!ID = Null
          total_set.Update
        End If
        total_set.MoveNext
      Loop
    End Sub
    

    The code is not tested.

    Imb.

    • Marked as answer by KevinATF Monday, October 9, 2017 2:50 PM
    Saturday, October 7, 2017 8:35 PM
  • Hi Kevin,

    You could use RecordSet to Move Previous/Next to add comments to the end of the text in Comments field of the preceding recording.

    Here is the example.

    Sub Test()
    
    Dim rst As Recordset
    
    sqlString = "select * from DesignComments order by UniqueID asc"
    
    Set rst = CurrentDb.OpenRecordset(sqlString)
    
    If Not (rst.EOF And rst.BOF) Then
    
        rst.MoveFirst
    
        Do Until rst.EOF = True
    
            ID = rst!ID.Value
    
            If Not IsNumeric(ID) And Len(ID) > 0 Then
    
            'empty current ID
    
             rst.Edit
    
             rst!ID = ""
    
             rst.Update
    
            'move to previous record to add comments
    
            'check id to make sure current record need add comments(in case of rows like Row18,Row19)
    
            moveCount = 0
    
            Do Until IsNumeric(rst!ID)
    
            rst.MovePrevious
    
            moveCount = moveCount + 1
    
            Loop
    
            'add comments
    
            rst.Edit
    
            rst!Comments = rst!Comments & "|" & ID
    
            rst.Update
    
            'move to current record
    
            rst.Move moveCount
    
            End If
    
            rst.MoveNext
    
        Loop
    
    End If
    
    rst.Close
    
    Set rst = Nothing
    
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by KevinATF Monday, October 9, 2017 2:50 PM
    Monday, October 9, 2017 6:15 AM
  • Hi Terry and Imb,

    Both your suggestions worked great.

    Thank you all for your help.

    Kevin

    Monday, October 9, 2017 2:51 PM