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

cob2020

New Member
Joined
Aug 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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_ProdstbVendorColors
vendorSKUcolorvendorcolorcodecolor
V01R29853.000whiteV01000white
V03DD957-BLblackV01002almond
V02IF9795U-295gold...
V04NBU538YyellowV02295gold
V05Y39IOGR2thunder grayV05GR2thunder gray
...V04Yyellow
...V03BLblack
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
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.


Code:
-- ----------------

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

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

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

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,827
Office Version
  1. 2019
Platform
  1. Windows
Glad to hear its helping! Nice job.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,577
Messages
5,637,198
Members
416,960
Latest member
Carbon1198

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