• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

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.
0
ggodwin
Asked:
ggodwin
  • 3
  • 2
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Rey Obrero (Capricorn1)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
 
ggodwinAuthor Commented:
I usually try to learn what you guys are sharing. But this one is beyound me. Very nice and executes beutifully.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now