Results 1 to 11 of 11
  1. #1
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1

    Unanswered: Help Stripping Out Prefix's & Suffix's

    Hi

    The database I am using is version 2003

    I am currently going through a process of removing duplicate part numbers from our product table, circa 1.8 million

    Example 1 in the attached

    From the example above, I have used a simple Replace(Replace([PROD_REF],"-",""),".","") function within an append query using all the symbols allowed, and as can be seen from StrippedProdRef column, it is the same product reference, same description, and same supplier. We can have the same part reference across different product groups see below.

    Example 2 in the attached

    The issue I do have at present is this some product References are either Prefixed or Suffixed with brands, and considering there are circa 60 Prefix/Suffix, this is just too many for the simple Replace function to handle

    In the made up example below, I need to be able to remove the Prefix and Suffix (FESTO/SCM) to reveal the same part no

    Example 3 in the attached

    Could you give me some guidance on how to do this more efficiently, hope this makes sense

    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Do you know VBA? If so then you can create a function in Access using the Split function to strip out the pieces you don't want. Anyway you do it you will need to compile a list of text to remove. Here is a quick start. Copy this into a new module and call it from a query or the Immediate window to see how it works.

    Code:
    Public Function StandardizedName(strName As String)
    
        Dim strPieces() As String
        Dim strPiece As String
        Dim lngCount As Long
        
        strPieces = Split(strName, "-")
    
        For lngCount = 0 To UBound(strPieces())
        
            strPiece = strPieces(lngCount)
            
            Select Case strPieces(lngCount)
            
                Case "SCM", "FESTO"
                
                    strPiece = ""
                    
            End Select
            
            StandardizedName = StandardizedName & strPiece
            
        Next lngCount
            
    End Function

  3. #3
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Thanks for the reply, just starting to get my head around VB, i will give it a go

    Thanks

  4. #4
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Hi the code above works brilliantly, thanks DCKunkle,

    But as ever there is always the inevitable but , I have discovered since my first request for help, that we have circa 400 prefix'/suffix's , and I dont want to particularly type them all into the code. unless I have to

    I have stored the prefix/suffix's in table called "BrandPrefixes" field "Prefix", so where DCKunkle has put (Case "SCM", "FESTO") in the code I need to call the stored prefix'x

    Can you help

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so how do you know what is a prefix and what isn't?
    how do you know where the product number starts?
    can you develop clear unambiguous rules which identify which is which

    If you cannot develop such a stratgey then you have to do it manually.
    But as ever there is always the inevitable but , I have discovered since my first request for help, that we have circa 400 prefix'/suffix's , and I dont want to particularly type them all into the code. unless I have to
    sometimes thats just the way it is.
    however you could stuff those 400 prefixes into a table and then run a query
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Hi Healdem

    Quote Originally Posted by healdem View Post
    so how do you know what is a prefix and what isn't?
    From the product creation team, I have recieved a file containing the prefix/suffix, stored in a table called "BrandPrefixes"

    Quote Originally Posted by healdem View Post
    how do you know where the product number starts?
    They are all in the same format, except the number of prefix characters

    e.g ABCD-PartNo, AB-PartNo, QWERTY-PartNo, but some PartNo may Start XY-PartNo- but this is a non branded PartNo

    Quote Originally Posted by healdem View Post
    can you develop clear unambiguous rules which identify which is which?
    You got me on that one

    DCKunkle code looks ath ABCD (Part) of the Part No, knows the seperator is the "-", and removes the ABCD-, and so on for the two suppliers in the code.

    I just wondered if it was possible to pass the circa 400 prefix's, through the code, but it looks like its gona have to be

    Quote Originally Posted by healdem View Post
    sometimes thats just the way it is.


    Mark

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so it should be possible to find what records start with a prefix
    have a table with the prefixes in
    then do a query which uses a where clause that matches the pefix

    eg
    where myproductnumber like myprefixes.prefix & "%"

    ..that query will identify what parts are used by what prefix

    then modify your query into an update query

    update mytable set mypartnumber = replace(mypartnumber,myprefixes.prefix,"")
    where myproductnumber like myprefixes.prefix & "%"
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1
    Like your thinking Healdem

    I'll give it a go

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    should mention
    if you are going down this route
    make sure you develop your query on the backup of the table.. just in case
    do another backup immediately prior to trunning the query
    then run the query
    check the values are correct
    then release the changes to the live DB
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I have been busy the last couple of days, but here is some modifed code that will check in a table called tblPrefix for prefixes. It checks the field Prefix to see if the piece of the whole part number is a prefix or not. If it is it removes it from the final result. Here is the code:

    Code:
    Public Function StandardizedName(strName As String)
    
        Dim strPieces() As String
        Dim strPiece As String
        Dim lngCount As Long
        
        strPieces = Split(strName, "-")
    
        For lngCount = 0 To UBound(strPieces())
        
            strPiece = strPieces(lngCount)
                        
            If IsPrefix(strPiece) Then strPiece = ""
                    
            StandardizedName = StandardizedName & strPiece
            
        Next lngCount
            
    End Function
    
    Public Function IsPrefix(strPiece As String) As Boolean
    
        Dim rstPrefixes As New ADODB.Recordset
        
        rstPrefixes.Open "tblPrefix", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        
        With rstPrefixes
        
            .Find "Prefix='" & strPiece & "'"
            If Not .EOF Then IsPrefix = True
            .Close
            
        End With
        
        'Cleanup
        Set rstPrefixes = Nothing
    
    End Function
    You will probably need to modify it. Change the following lines if necessary:

    Code:
    rstPrefixes.Open "tblPrefix", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    .Find "Prefix='" & strPiece & "'"

  11. #11
    Join Date
    May 2005
    Posts
    126
    Provided Answers: 1

    Talking

    Hi

    Sorry in the delay in getting back to you, a bit busy

    Just like to say thank you to DCKunkle & Healdem for your support and guidance, both solutions worked

    Last edited by MarkWhyte; 10-06-10 at 07:06.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •