Solved

Query to perform multiple task based on text in one field

Posted on 2015-02-24
6
75 Views
Last Modified: 2016-02-11
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
Comment
Question by:ggodwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40628428
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
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 40631333
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40631385
<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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:ggodwin
ID: 40632221
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40632251
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
 

Author Closing Comment

by:ggodwin
ID: 40633210
I usually try to learn what you guys are sharing. But this one is beyound me. Very nice and executes beutifully.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question