Create Many to Many Relationship from Single Table

[MagnusFox]

I need to break a single table apart into two additional tables to result in a many to many relationship with the original table. I hope you can assist with an SQL update/append command or something to do the dirty work for me.

TABLE MENU
Menu_ID
Menu_Title
HTML_Content

Here are my thoughts for the final table structures:
TABLE MENU
Menu_ID
Menu_Title
HTML_Content

TABLE MENUCONTENT
ContentID
MENU_ID
PageOrder

TABLE CONTENT
ContentID
HTML_Content

So basically I want to move the filed HTML_Content from the table MENU to the new table CONTENT, while maintaining the relationship between the Menu_ID and the new ContentID. When the two keys are added to MENUCONTENT, I need to set the filed PageOrder = 1.

Please note: I have created the additional tables so no need to so that, unless you think it is easier for me to delete them and use a make new command with your solution.

As always with you wonderful Tek-Tippers, thanks in advance for your time and solutions.

 

[johnnymac43]

First, some assumptions:

1. You will initially create 2 new tables, each having a 1:1 relationship with table Menu, and with each other.

2. Menu_ID is an autonumber field (which may have missing numbers in its series).

3. ContentID will be an autonumber.

If this is so, the safest way to do it would be to proceed as follows:

> Run a Make Table query to put the fields Menu_ID and HTML_Content in table Content.

> Add the field ContentID (autonumber) to table Content.

> Run a Make Table query on table Content to create table MenuContent.

> Remove the field Menu_ID from table Content.

HTH

John

 

[MagnusFox]

Well thanks John.

I spent some more time looking at this and decided there really was no one step solution, so I used ASP to solve it for me. John's solution is the best if you are going to stay within Access. If you know how to use ASP and can connect to your DB, then this should make it quite quick:

<%
Set rsMenu = db.Execute(&quot;SELECT Menu_ID, Menu_Body FROM Menu&quot
While Not rsMenu.EOF
'Split out the content into the Content table.
Set rsContent = Server.CreateObject(&quot;ADODB.Recordset&quot
rsContent.Open &quot;SELECT * FROM Content WHERE 0=1&quot;, db, adOpenKeyset, adLockPessimistic, adCmdText
rsContent.AddNew
rsContent(&quot;HTML_Content&quot = rsMenu(&quot;Menu_Body&quot
rsContent.Update
objContentID = rsContent(&quot;ContentID&quot
rsContent.Close

'Add the relationships into the MenuContent table.
Set rsMenuContent = db.Execute(&quot;INSERT INTO MenuContent(MenuID, ContentID, ContentOrder) VALUES(&quot; & rsMenu(&quot;Menu_ID&quot & &quot;, &quot; & objContentID & &quot;, 1)&quot
rsMenu.MoveNext
Wend
%>