How to I strip a number and then make sure its the same for all rows in the table? in MS ACCESS

[cob2020]

On a daily basis I process a batch of products. Each of the products in the batch always belongs to a product model. I would like to stop doing this manually and program Access to do if for me. Basically the product model is on the left most part of the product ID or SKU. I am presenting the following table so that there is a visual representation of my data and what I want to accomplish. I have a table with the product IDs or SKUs and I want to generate or identify the model number from the SKU. Basically I'm stripping the SKU from left to right until all digits within the SKU are equal. How do I do this in vba or via queries?

tbProducts	Outcome
SKU	Model_Number
D2336630000	D233663
D233663320	D233663
D23366332001	D233663
D23366300001	D233663
D2336633260	D233663

 

[welshgasman]

Yeah Welshgasman... I think that would be the start. Find len (lenght) for all the SKUs and then whichever has the less number of digits... use that number to trim the others in the batch... then the second part of the process would be some type of query or procedure to compare all the trimmed SKUs, if all are equal then we got the model number, if not then remote one more digit from all SKUs from the right and compare again until they are all equal...

No, I would think that the ModelNumber drives the Logic? You have A290030 and RTV3 as an example.?
If I undertsand correctly the length of the ModelNumber determines what you compare in the SKU?

You can test it out with a simple select and put the amended SKU alongside the SKU with the ModelNumber?

 

[cob2020]

Let me put it this way... the model number does not exit. We generate it on the fly taking the left most part of the product SKUs we get per batch. Eventually, we use that generic model number to link the products.

 

[petertenthije]

there is another product attribute called collection or family, which identifies the batch, but we cannot utilize it

I don’t think it is possible to do what you want without a unique identifier for the batch.

 

[welshgasman]

Let me put it this way... the model number does not exit. We generate it on the fly taking the left most part of the product SKUs we get per batch. Eventually, we use that generic model number to link the products.

Well unless you can explain how you do it manually, you are out of luck.?
Computers are stupid and only do what you tell them.? You can see a mistyped entry and know what it should be.? To do that via a program wouldtake a fair bit of logic.?

TBH this seems backwards to me. A model number would exist and then all the variations of it would have different SKUs, for Blue, Green, XL,XXl etc.

I'll step away now, as this is beyond what little knowledge I have.

 

[Micron]

Based on a quick read, I suspect that you'd have to get the data value that is the shortest length - n . You'd then start at the beginning of the recordset and extract only Left([data_field],n) from every record. However, as I write this I again think I've missed something as that seems too simple, but here's my logic: what ever the shortest value is, nothing else in the field records that has anything beyond that could be a match so don't consider those values as candidates for the number you need. Since I can't both type this post and go back to look at the data you last provided, I'll leave it at that for now. What I've suggested shouldn't be that difficult, but again, I may have missed something vital.

Interesting - try to put the letter n within parentheses () and you get a thumbs down emoji so I had to edit my post.

 

[Legacy 456155]

I came to the same conclusion as Micron though I still don't know if I've grasped what you are after. Here is something to work with, perhaps.
You could optimize this by at least beginning with the shortest length. However, if 80 items are the max in a batch, then optimizing might be a waste of time.

Sub CreateModelNumber()
    Dim ret As String, p1 As Integer, p2 As Integer
  
    p2 = CurrentDb.OpenRecordset("SELECT TOP 1 LEN(SKU) AS LenSKU FROM tblProducts ORDER BY Len(SKU) DESC")!LenSKU
    With CurrentDb.OpenRecordset("SELECT SKU FROM tblProducts")
        Do Until .EOF
            p1 = p1 + 1
            If p1 > p2 Then Exit Do
            ret = Left(!SKU, p1)
            .MoveNext
            Do Until .EOF
                If Left(!SKU, p1) <> ret Then GoTo FoundIt
                .MoveNext
            Loop
            .MoveFirst
        Loop
    End With
FoundIt:
    ret = Left(ret, Len(ret) - 1)
    CurrentDb.Execute "UPDATE tblProducts SET Model_Number = """ & ret & """"
End Sub

 

[petertenthije]

I know where you are going with your questions... if there is a way to identify the batch, then we can use that as a model number... best answer I can give you is yes... there is another product attribute called collection or family, which identifies the batch, but we cannot utilize it and as per company policy and for legal reasons... therefore, we are required to generate our own internal model number. Hope this answers your question.

So if I understand you correctly, you have the batch details, but you are not allowed to use the batch details as model_number?

Are you allowed to view the batch numbers and to add them into Acces? Even if only in a temporary table that will be cleared after use? Or can you somehow modify the source file that every batch is replaced with a unique number?

With the batch numbers I believe it might be possible to work back to extract the model_names from the SKU codes.

Failing that, instead of trying to make a solution in access, can the source file be changed? You said the source file combined model number and “suffixes” to form the SKU. Maybe you could change the output and throw in a period or an underscore between the model and the “suffix”.