updating a many-to-many relationship 1

[ferrisj]

I have a table with information on Facilities, and I have a Table of Management companies. I have joined them with a junction table for a many-to-many relationship.

SELECT
FROM [Facility Name] INNER JOIN ([Management Company] INNER JOIN [Management Company Junction] ON [Management Company].MC_ID = [Management Company Junction].[Management REF]) ON [Facility Name].[Facility ID] = [Management Company Junction].[Report REF];

Now here is where the problems lie. The facility table is already populated with facility information, and so if Management Table.

I have a third table not related to anything, that what incorrectly created which has the data i need. the table is designed like this:

Facility Mgt Comp 1 Mgt Comp 2 Mgt Comp3 ETC
========== ============ ============ =========== ====


There are 10 separate fields for Management companies. How properly deconstruct this table to setup the proper many-to-many relationship I am trying to create.

 

[PHV]

Take a look at UNION query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Well kudos to you for taking the effort to mormalize your database.

The Union query would work as per PHV. Another approach, a little slower, is to use some coding. Here is a sample using DAO record sets.

'Sample code to open 4 recordsets, one to read and two to write
Dim rstW1 as DAO.Recordset, rstW2 as DAO.Recordset, rstW as DAO.Recordset
Dim dbs as DAO.Database, rstR as DAO.Recordset

Set dbs = CurrentDB()
Set rstR = dbs.OpenRecordset("YourOldTable")
Set rstW1 = dbs.OpenRecordset("[Management Company Junction]")
Set rstW2 = dbs.OpenRecordset("([Management Company]")
Set rstW3 = dbs.OpenRecordset("([Management Company]")

With rstR

   .MoveFirst
   Do While Not .EOF

      rstW1.AddNew   'update junction tab;e
         rstW1![Management REF] = !DoYouHave1MatchingField
         rstW1![Report REF] = !DoYouHave2MatchingField
      rstW1.Update

       rstW2.AddNew  'update management company
         rstW2![Mathcing1Field] = !DoYouHave1MatchingField
         rstW2![Matching2Field] = !DoYouHave2MatchingField
      rstW2.Update

      rstW3.AddNew    'update facility name
         rstW3![Mathcing1Field] = !DoYouHave1MatchingField
         rstW3![Matching2Field] = !DoYouHave2MatchingField
      rstW3.Update     
      
      .MoveNext
   Loop

End With

rstR.Close
rstW1.Close
rstW2.Close
dbs.Close

Set rstR = Nothing
Set rstW1 = Nothing
Set rstW2 = Nothing
Set dbs = Nothing

This code loops through the original table, and then allows you copy relavent data to their respective tables.

Richard