Query to perform multiple task based on text in one field

This one seems complicated to me.
My company has different locations in different countries and each group has similar procedures and jobs. The customers we service are the same. However, each customer has a different format of their data. Like our customers we also have slightly different ways to manage the data.

However, the task of the "Corporate" side is to consolidate all of these  locations under the same Customer Corporate umbrella. Therefore, we must compile the data and report it as ONE.

My Canada location does not use the Customer primarykey as their primary key. Therefore, there are a few cases where they have multiple Customer primary keys in the same record. They do this so they don't have to enter duplicates manually.

But in the Corporate side, we use the customer primary key as ours. I need to locate and separate these into multiple records as they should be.

Table: DSCNSKPIUpdate
Field: Reject

This Reject field may or may not have multiple groups of text that start with "R0" and each one is separated with "/".

Example:
Reject can have the following
R0100166
R0100152
R0100016
R010075,R010076,
R010212,R028001,R011111
R010333
R011190

For those records that have multiple values in (reject) I need to create a duplicate record for all fields (15 different fields need to be copied) in that record set. However, I need those duplicate records to be separate by the multiple (Reject) values.
ggodwinAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
use theses codes

Sub UpdateAppendRecords()
Dim rs As DAO.Recordset, j As Integer, varArr() As String, rs1 As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("DSCNSKPIUPDATE2")
Set rs1 = CurrentDb.OpenRecordset("DSCNSKPIUPDATE2")

Do Until rs.EOF
    If InStr(rs!Reject, ",") Then
        varArr = Split(rs!Reject, ",")
        rs.Edit
        rs!Reject = varArr(0)
        rs.Update
       
        For j = 1 To UBound(varArr)
            
            If varArr(j) & "" <> "" Then
                With rs1
                    .AddNew
    
                    !Description = rs!Description
                    !Complaint = rs!Complaint
                    !Customer = rs!Customer
                    !PartName = rs!PartName
                    !Occurence = rs!Occurence
                    !Reject = varArr(j)
                    !DensoPlant = rs!DensoPlant
                    !DateCode = rs!DateCode
                    !ProblemDesc = rs!ProblemDesc
                    !Finding = rs!Finding
                    !DSCNinspect = rs!DSCNinspect
                    !Interim = rs!Interim
                    !ReportDue = rs!ReportDue
                    !DateShip = rs!DateShip
                    !Tracking = rs!Tracking
                    !Rank = rs!Rank
                    !DateCMRec = rs!DateCMRec
                    !SortReq = rs!SortReq
                    !Root = rs!Root
                    !CMDesc = rs!CMDesc
                    !Category = rs!Category
                    !CMDate = rs!CMDate
                    !Responsability = rs!Responsability
                    !SKPIDispute = rs!SKPIDispute
                    !RootCauseCat = rs!RootCauseCat
                    !IssueStatus = rs!IssueStatus
              
                   .Update
                End With
            End If
         Next
    End If
rs.MoveNext
Loop
rs.Close
rs1.Close
End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
you will need VBA codes to accomplish this.. test this
Sub UpdateAppendRecords()
Dim rs As DAO.Recordset, j As Integer, rejArr() As String
Set rs = CurrentDb.OpenRecordset("DSCNSKPIUpdate")

Do Until rs.EOF
    If InStr(rs!Reject, ",") Then
        rejArr = Split(rs!Reject, ",")
        rs.Edit
        rs!Reject = rejArr(0)
        rs.Update
        
        For j = 1 To UBound(rejArr)
            rs.AddNew
            rs!Reject = rejArr(j)
            
            ' add the other fields here
            ' rs!FieldName=rs!FieldName
            
             rs.Update
         Next
    End If
rs.MoveNext
Loop
rs.Close
End Sub

Open in new window

0
 
bonjour-autCommented:
Line 4 in your data will give problems with stadard split method as there is a semicolon at the end.
If this is a real possibillity, you may try a slightly different approach, where all numbers are concatenated to a single string and split then to a second table. assuming source data in tbl1 and target data in tbl2 the code would something lik the following:
Public Function seperate_id()

   Dim rs As DAO.Recordset
   Dim collector As String
   Dim mysql As String
   Dim myarray
   Dim i As Integer
   
   collector = ""
   Set rs = CurrentDb.OpenRecordset("select * from tbl1")
   If rs.RecordCount > 0 Then
     rs.MoveLast
     rs.MoveFirst
     Do While Not rs.EOF
       collector = collector & rs.Fields(0)
       If Right(collector, 1) <> "," Then collector = collector & ","
       rs.MoveNext
     Loop
     collector = Left(collector, Len(collector) - 1)
     mysql = "delete * from tbl2"
     DoCmd.SetWarnings False
     DoCmd.RunSQL mysql
     myarray = Split(collector, ",")
     For i = 0 To UBound(myarray)
       mysql = "insert into tbl2(field1) values('" & myarray(i) & "')"
       DoCmd.RunSQL mysql
     Next i
   End If
   
End Function

Open in new window

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Rey Obrero (Capricorn1)Commented:
<Line 4 in your data will give problems with stadard split method>
that can be easily remedied with

For j = 1 To UBound(rejArr)
if rejArr(j) & ""<>"" then
    rs.addnew
    rs!Reject = rejArr(j)
    rs.update
end if
0
 
ggodwinAuthor Commented:
Rey,
I'm planning to use this on the same table we worked on earlier. What do I need to do to get it work?  Where does that line 4 code in the above post go? Do i put it in with there original code?
0
 
ggodwinAuthor Commented:
I usually try to learn what you guys are sharing. But this one is beyound me. Very nice and executes beutifully.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.