categories update in items table

I need to insert the ID from the categories and Department tables into the Items table, can someone please let me know of the correct SQL to do this ? Thank you jm

Hi Jeff In the Category table I have the ID, DepartmentID, Name, Code, fields. I have an Excell file that each item has the Code for each item that match the code in the Category table

and I can say the same for the Departments. So I have to figure a way to insert the category Id based on the code field and the Department Id based on the Categorie Code as well Thank you for taking time Jm

"Jeff" wrote:

BACKUP, BACKUP, BACKUP!

Then...

I believe the best way to do this will be to add a column in Excel to create a SQL statement for each item. For example, assuming that in your Excel file you have the item lookup code (Col A), description (Col B), category number (Col C), department number (Col D)... add a column with a formula like this (this formula goes in cell E2):

="UPDATE Item SET Category = "&C2&" WHERE ItemLookupCode "&CHAR(39)&A3&CHAR(39)

The result will look something like this:

UPDATE Item SET Category = 44 WHERE ItemLookupCode = '072845221313'

Copy this formula down next to each row. Then take the results in column E an copy them. Go into MS Query or whatever program you are using, and paste this into the Execute SQL box.

It might take some time to execute all of those UPDATE statements if you have a lot of items. But as long as all of the ILC's exist in the SQL database, you should not have a problem.

Notes:

-The ILC column in Excel should be formatted as text. Make sure you have not dropped the leading zero in ILCs. If you used SQL/ODBC to get the data into Excel, you shouldn't have a problem.

-The CHAR(39) part adds the single quote required around text fields, like the ILC. You leave this out for numeric fields like Category and Department.

-You should probably do the Department first. I am not sure how RMS will react if you enter a category with no department. I am also not sure how it will react if the category you enter does not match the department you enter. Use this at your own risk!

Good Luck!

Jason