Updating information in a table

[Jonathan Wells]

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

 

[Michel Walsh]

Hi,

I am really not sure about what you want to do. It seems you want to
relay the Product Id through their "name" ? if so, I would try a query
implying the "Product" table and... "Customfields linking" (???) table,
with an inner join between their "Prod Id" fields. Be sure to bring "Prod
Id" and "Name" from table "Product". Save it as query1.

Next, make another query, implying "Product" and "query1", with an inner
join on the "name". Your data "Prod Id = 2" is now linked to your data "Prod
Id=1".


It would be more robust to add an extra field "just for the purpose" you
try to accomplish, maybe?


Hoping it may help,
Vanderghast, Access MVP