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

cob2020

New Member
Joined
Aug 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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?

tbProductsOutcome
SKUModel_Number
D2336630000D233663
D233663320D233663
D23366332001D233663
D23366300001D233663
D2336633260D233663
 
Last edited by a moderator:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
880
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?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

cob2020

New Member
Joined
Aug 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
880
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 
L

Legacy 456155

Guest
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.

VBA Code:
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

Board Regular
Joined
Sep 25, 2012
Messages
241
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”.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,972
Messages
5,621,905
Members
415,865
Latest member
pxmike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top