Normalizing Data in Table

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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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.

Answer 7

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 [e-mail].

Answer 8

Hi Terry and Imb,

Both your suggestions worked great.

Thank you all for your help.

Kevin