Hi all....
I have this situation where i need to move data in an MS ACCESS
database from one table to the other using an sql script, and i am
having trouble dong that.
The existing database has the following:

table: SHAPE
SHAPE_ID         primary key (counter)
SHAPE_NAME       text
SHAPE_COLOR      text
...and some other columns

table: ELEMENT
ELEMENT_ID     primary key (counter)
ELEMENT_SHAPE_ID   long, foreign key
ELEMENT_TYPE   long
...and some other columns

the relation is one shape to many elements (SHAPE_ID to
ELEMENT_SHAPE_ID)
Now i want to move the COLOR from the SHAPE table to the ELEMENT table,
but i want to make the color into a new table...and add more attributes
to it...So i created a new table called ATTRIBUTE which will host the
color and other attributes as follows:

table: ATTRIBUTE
ATTRIBUTE_ID        primary key (counter)
ATTRIBUTE_COLOR     text
ATTRIBUTE_DENSITY   double
....and some more columns...

and i made it related to the element table by a one-to-many relation,
so the element table becomes like this:

table: ELEMENT
ELEMENT_ID
ELEMENT_ATTRIBUTE_ID foreign key

Now the trouble begins. I want to move the existing data to the new
schema. is there a way to move the color of the shapes to the attribute
table (by creating a new attribute for every color) and then
associating this new attribute to every element of that shape?
I want to be able to do that by running SQL statements only.

I hope someone will be able to give me some help.

thanks
anonC.