replace old data field

[Guest]

Hello,

I have an ODBC link to my accounting database.

I have a new database i.e. old item code with new product category.

Can I have a simply query to find out the item code and replace new product
categoly?

i.e.

Accounting database from ODBC

ItemCode ProdCat
0001 Book
0002 Gift
0003 Book
0004 Book
0005 Ink
.... ...

New Database
ItemCode ProdCat
0001 Paint
0003 Light
0005 Case
.... ...

I have about 20,000 items and 3,000 of them have to replace product catelogy.
Is there any easy way to change the product catelogy?

Thanks

 

[John Vinson]

ItemCode ProdCat
0001 Book
0002 Gift
0003 Book
0004 Book
0005 Ink
... ...

New Database
ItemCode ProdCat
0001 Paint
0003 Light
0005 Case
... ...

I have about 20,000 items and 3,000 of them have to replace product catelogy.
Is there any easy way to change the product catelogy?

Do you have (in text, Excel, or some other Access table) a list of the
new categories, linked either to the ItemCode or to the old ProdCat?
If so an Update query should be straightforward.

Assuming that what you show as "New Database" exists as a table, you
can create a query joining it to your Items table by ItemCode; make it
an Update Query; and update ProdCat to

[New Database].[ProdCat]

using your actual tablename.

John W. Vinson[MVP]

 

[Guest]

It works.

Thanks

ItemCode ProdCat
0001 Book
0002 Gift
0003 Book
0004 Book
0005 Ink
... ...

New Database
ItemCode ProdCat
0001 Paint
0003 Light
0005 Case
... ...

I have about 20,000 items and 3,000 of them have to replace product catelogy.
Is there any easy way to change the product catelogy?

Do you have (in text, Excel, or some other Access table) a list of the
new categories, linked either to the ItemCode or to the old ProdCat?
If so an Update query should be straightforward.

Assuming that what you show as "New Database" exists as a table, you
can create a query joining it to your Items table by ItemCode; make it
an Update Query; and update ProdCat to

[New Database].[ProdCat]

using your actual tablename.

John W. Vinson[MVP]