Need a query or VBA code to identify and parse some values in final query

[cob2020]

Hi Forum. I just signed up. Looking for a solution to the following problem:

I have a partially completed query called 'qNew_Prods' and a static table 'tbVendorColors'. I need to populate a field called 'color' for my final query using the tbVendorColors table. 95% of our vendors are consistent in including a color code 'at the end' of the product id or SKU. The other 5% I'm ignoring as they just generate their products IDs or SKUs randomly and have no consistency or pattern.

I have included a sample of the query and table for more clarity. A couple of things to emphasize; 1) The vendor is identifiable from the SKU and that narrows down the selection of colors in the tbVendorColors table; 2) The end of the SKU always have the color code of the product; 3) as you can see from the samples, the color codes vary from 1 digit to 3 digits in either numbers, letters or combination of both and they dont always have a delimiter such as the dash or period ("-","."). I have been experimenting with some queries, but they only cover a small percentage and we always end up doing this task manually, which is a pain when we received thousands of products for more than 80 vendors. Any help/assistance will be greatly appreciated.

qNew_Prods				tbVendorColors
vendor	SKU	color		vendor	colorcode	color
V01	R29853.000	white		V01	000	white
V03	DD957-BL	black		V01	002	almond
V02	IF9795U-295	gold		...
V04	NBU538Y	yellow		V02	295	gold
V05	Y39IOGR2	thunder gray		V05	GR2	thunder gray
...				V04	Y	yellow
...				V03	BL	black

 

[xenou]

Proceeding stepwise here's an idea (there might be better ones, such as creating a UDF function to handle this, for instance).

• Create a New Temp table that has all the possible colors/skus that could be matched up
• match up colors and skus using a best guess approach (if the last digits of the sku match the color, then call it a match ... without seeing the data I cannot guarantee there are not cases where there are more than one match or bad matches -- the latter would be random I guess and the former might happen if color codes sort of "overlap" for the same vendor)
• update back to the product table from the Temp table.

-- ----------------

DROP TABLE NEWTABLE

-- ----------------

select distinct t1.vendor, t1.SKU, t2.colorcode, t2.color, '' as new_color
into NEWTABLE
from
    qNew_Prods t1
    inner join tbVendorColors t2
    on t1.vendor = t2.vendor
where
    nz(t2.colorcode, '') <> ''
    and nz(t1.SKU, '') <> ''
    and nz(t1.color, '') = ''

-- ----------------

update NEWTABLE
set new_color = color
where right(SKU, len(colorcode)) = colorcode

-- ----------------

update qNew_Prods t1
inner join NEWTABLE t2
on t1.vendor = t2.vendor
and t1.SKU = t2.SKU
set t1.color = t2.new_color
where
    nz(t2.new_color, '') <> ''
    and nz(t1.color, '') = ''

caveats:

• because of how Access works, the above four queries must go in four different query objects.
• In real life I'd probably have a permanent temp table, so the first query would be Delete instead of Drop and the second would be insert into instead of select ... into
• the big problem ... the second query is basically a cartesian product so it can get big if you have a lot of vendors and colors that are still unmatched. For instance if you have 1000 vendors with an average of 8 colors, that's 8000 rows. I think it should remain manageable unless you really have a lot of vendors.
• you can review the table results in the temp table (after the first update) to see how the matching is going, before you transfer the actual changes to the prod table (this is a plus I hope).
• I'm just going to assume if a color is already assigned to a sku then you don't need any work done for that sku - it's already settled.

 

[cob2020]

Thanks so much for taking the time... I'm going to work on this right now and report back when I have it working. I've been super busy today.

 

[cob2020]

Thx Xenou... this is w/o a doubt a good place to start. I set up the 3 queries and they worked as designed... so kudos. I had the same idea about using the right and len functions, but didnt have time to figure out how to actually do it.

We usually process relatively small files daily and only for 1 to 10 vendors per file so the results for the second table although large will not get as large as in the thousands.

I agree, It is difficult to provide an exact solution that can account for every single situation w/o a data set (sorry about that)... in any case, will continue to test scenarios and see how it goes. By the way, colorcodes are unique within the same vendor. However, in some instances some might be very similar or as you say overlap... as an example, the other day I processed a product which had a SKU that ended with 2PC... that particular vendor has several colorcodes, but two of those are PC and 2PC so they will both get matches... I havent found the one I used or another case that will allow me to test for this instance. Thx so much though.... I really appreciate your time and help.

 

[cob2020]

So for practicality and to squash the instances where a colorcode overlaps within the same vendor, I added a new column to the tbVendorColors table with the len of the colorcode. Then duplicated the queries provided to create 3 temp color tables; 1st for colorcodes w/3 digits; 2nd for colorcodes w/2 digits and last for colorcodes w/1 digit... that way the last table gets updated first with colorcodes that are 3 digits and in the case of 2PC and PC, the latter won't affect the updated table as the color is no longer empty. Last, I placed all these queries on a macro and disabled the prompts and it's flying. Thx again Xenou.

 

[xenou]

Glad to hear its helping! Nice job.