Solved

Query to perform multiple task based on text in one field

Posted on 2015-02-24
6
69 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now