Concatenating fields to create new ItemLookupCode code

Dear all,

I'm after a more... elegant solution to a problem that I have at the moment. I need to change the data in our ItemLookupCode field to be made up of information in two other fields, namely: SupplierCode + ReorderNumber.

So, an example would be this:

Existing ILC:

20016

Proposed ILC:

HASMR21573

This would be derived from:

SupplierCode = HAS + ReorderNumber = MR21573

One of the problems that I was wondering about is how one deals with items that have more than one supplier as the ReorderNumber number can be different depending on the supplier. If it came down to it I would be happy to default to the primary supplier. Instances of multiple suppliers in our database are in the minority.

Any help would be appreciated.

Ryan

=======================

Dear Jeff,

Thanks for the reply. Our problem is that we have 20,679 unique items in our database (we are a toy business dealing in thousands of new lines a year). Every year, suppliers provide us with spreadsheets with their items for sale. The reason we would be changing the ILCs is so that we can quickly bring up an item without having to scan it or quickly bring up an item having already created the core information, at which point we simply add an alias.

We, in any given day, can receive up to 8-12 (sometimes 15) pallets of stock.

Our problem stems from the fact that we are dealing with legacy data from the old pos which used a six digit number as the ILC. Unfortunately, we were not able to convert to SupplierCode + ReorderNumber when we switched.

I hope this all makes sense. None of this would be a problem if in the dbo.item there was reference to SupplierCode and ReorderNumber. I understand, of course, why there isn't but I am not a whiz with Excel or Access when it comes to relationships and the like!

Regards,

Ryan

Jeff @ Check Po> Ryan,

Ryan, Would a script that added an alias for each item that is the current ILC + Supplier.ReorderNumber work for you?

Ryan,

Try this:

From Admin, run the following queries and export the results as CSV files:

SELECT ID, ItemLookupcode FROM Item SELECT ID, Code, SupplierName FROM Supplier SELECT ItemID, ReorderNumber FROM SupplierList

*CAUTION* if you have leading zeros in any ILC or Reorder Number fields, you will need to open the CSV files with a text editor (Notepad) or word processor before moving the data to MS Excel. If you don't have any leading zeros to worry about, you can simply open the .csv files with Excel.

You can use the VLOOKUP function to associate the ReorderNumber to the ItemID and SupplierID, and again to associate the SupplierID to the Supplier Code. This scenario assumes that the Supplier Code is the same as the three-character prefix you want. If you want to use the first three letters of the Supplier Name, no problem. Just another step.

Then use the CONCATENATE function or text formula to combine the new Supplier prefix and ReorderNumber as your new ILC.

Next, create a text formula that results in the following query statement: UPDATE Item SET ItemLookupCode = R1C1 WHERE ID = R1C1

R1C1 = the actual cell that contains the data.

I can e-mail you a sample spreadsheet if you'd like.

Tom

Dear Terrible et al,

Thank you very much for pointing me in the right direction. I extracted the relevant fields and was able to concatenate the appropriate info as a result of your suggestion of using the VLOOKUP function. Once I had defined the appropriate ranges, I was able to VLOOKUP all the relevant data and contacentate successfully. I had not used VLOOKUP before and as I'm crap at SQL I was not able to create the appropriate query that would have joined all of the relevant data, so VLOOKUP was perfect.

Cheers.

Terrible Tom wrote: