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

 

[Micron]

Not sure what you mean by your post title or description.
You only show a result that is the same for each SKU, so it looks like all you want is the leftmost 7 characters, but something about your post makes me think it's not that simple. If that's it, then an expression using the Left function - Left([SKU],7)- would give you D233663 from each of the SKU's you've shown.

 

[cob2020]

Thx for the reply Micron... however, as you assumed it is not that simple.... not all model numbers are 7 digits per batch... they could be longer or even shorter than 7 digits. What I need help with is on a function or a vba procedure that will allow me to first, remove the digits on the right of the SKU until they are all the same size (perhaps using len to determine which SKU has less digits and then use that number to change the others so they are all equal in size), and second compare is they are now all the same value, if not, remove another digit from the right and compare again until the values are all equal... not sure if I am being clear enough.

 

[petertenthije]

How can you tell that D233663320 and D23366332001 are linked to SKU D233663, by your described logic they might also be for SKU D2336633.

Is there a list with possible SKUs?

 

[cob2020]

Our departments generates the model number, which is an internal thing for us to organize the products in our database, from the SKUs, which are process 'per batch'. A batch can be 4 products all with different SKUs or a batch can have as many as 80 products... my team gets these batches in chunks throughout the day. Let me give more samples. Right now, we manually determine the model number from examining the SKUs per batch... which is inefficient IMO. Thus the reason for asking for some help. I don't know how to explain it better than this. I need tips on how I can accomplish this automatically in MS ACCESS either via queries or vba procedures.

tbProducts	Desired Outcome
SKU (1st Batch)	Model_Number
A290030	A290030
A290030AWP	A290030
A2900308	A290030
SKU (2nd Batch)	Model_Number
RTV3	RTV3
RTV3301	RTV3
RTV3...	RTV3
...	...
SKU (3rd Batch)	Model_Number
DX2980000000015	DX29800000000
DX29800000000APC	DX29800000000
DX298000000001WA2	DX29800000000
...	...
and so on...

 

[welshgasman]

I would find the length of a model number, then compare that to Left(SKU,LEN(modelnumber)), but as pointed out, I'm not even sure that is bulletproof, but might be a start.?

 

[cob2020]

petertenthije asked "
How can you tell that D233663320 and D23366332001 are linked to SKU D233663, by your described logic they might also be for SKU D2336633.

Is there a list with possible SKUs? "

When we examine a batch of products the model number becomes the number from those SKUs that is the common denominator in all of those SKUs... the model is entered into our database and all the SKUs in the batch are linked to that model. To answer your question, after determining that D233663 is common in all SKUs, it is then selected as the model number and entered in our database followed by all the SKUs for that batch... the database links those SKUs to the model number. D2336633 cannot be a model number because it is not common with SKU D23366300001 and SKU D2336630000 which are in that batch.

 

[petertenthije]

But using your original message as an example, how do I know that below codes are split behind the last 3 and not behind the2?

How do I identify batches?

D2336630000
D233663320	D23366332_0
D23366332001	D23366332_001
D23366300001
D2336633260	D23366332_60

 

[cob2020]

I would find the length of a model number, then compare that to Left(SKU,LEN(modelnumber)), but as pointed out, I'm not even sure that is bulletproof, but might be a start.?

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

 

[cob2020]

But using your original message as an example, how do I know that below codes are split behind the last 3 and not behind the2?

How do I identify batches?

D2336630000
D233663320	D23366332_0
D23366332001	D23366332_001
D23366300001
D2336633260	D23366332_60

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.