sql trouble

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.

.

Relevant Pages

  • Re: Help with foreign keys
    ... one that uses a local Access 2007 database. ... UserID varCharNOT NULL FOREIGN KEY REFERENCES Users, ... So, if we have Customers, and Invoices tables, a left join would give us: ... REGARDLESS if the child records ...
    (microsoft.public.access.gettingstarted)
  • Re: Mailing Address vs. Courier Address
    ... Database Design for Mere Mortals, ... Where does the Courier Address fields go--I suspect ... CustomerID - Foreign Key joined to tblCustomers Long Integer ... and when you start a mailing, you will be using Mailing or Both as the ...
    (microsoft.public.access.gettingstarted)
  • How DTS failed?
    ... I sugest you take off the primary key / foreign key ... such as using the copy database wizard, ... production server to the ... >On the test server, created an empty database which is ...
    (microsoft.public.sqlserver.server)
  • Re: Mixed up with Relationships..help!
    ... The database you're describing ... The CONSTRAINT statement establishes a Primary Key or a Foreign Key, ... "REFERENCES" table and column. ... QuestionaireAnswers: QuestionaireAnswersID, StudentID, 2005, ...
    (microsoft.public.access.gettingstarted)
  • Re: Mailing Address vs. Courier Address
    ... could you please transfer all your database knowledge to me in an hour ... Where does the Courier Address fields go--I suspect ... CustomerID - Foreign Key joined to tblCustomers Long Integer ... and when you start a mailing, you will be using Mailing or Both as the ...
    (microsoft.public.access.gettingstarted)
Loading