Updating information in a table

[Guest]

Hi I'm desperately trying to workout how I can fix this. Here is my problem.
I have a product table which has a lot of products in it with custom fields.
The custom fields are stored in a seperate table. I copied and pasted all the
products in the product table so I could sort them by 'brand' and 'product
type'. It a messy solution and a lot of redundant data but its the only way I
can work around the ecommerce program which only assigns a product to one
folder.

Now in the custom fields table none of the custom fields are linked to the
duplicated products because of their new product ids. Now rather than
reassigning all the custom fields which will take forever is there any way I
can do a query to match them up again.

Here is an example of my situation.

Here are my tables:

Product table
prod Id: 1 name: protein bar

Customfields linking table
CustomfieldID: 1 productID:1

Customfield
Customfield ID: 1 Customfield description: Flavour Valuelist: Choc, Caramel

then I copy the records in the product table
i.e. prod Id: 1 name: protein bar
prod Id: 2 name: protein bar

And of course that second one now doesn't have the custom field attached. So
is there a query that can append this data into the product table.

Thanks in advance for your help

 

[Guest]

Try this:
Create a table that matches the product table, but include an extra field
that will store the product id from the original table.

then you can use a query to append the data from the first table into the
second one (instead of using copy and paste).

then to get back to the custom fields, join on the old product id instead of
the new one.

J.