Solved

Query to perform multiple task based on text in one field

Posted on 2015-02-24
6
66 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Conditional Formatting in a Macro 4 25
Excel Copy Macro down foe each row 4 18
Excel VBA When using VLookup 6 26
Format Meeting Request through VBA 5 12
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 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

20 Experts available now in Live!

Get 1:1 Help Now