Results 1 to 3 of 3

Thread: Many-to-many from one table

  1. #1

    Many-to-many from one table



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi


    I need some help. Let's say I have a list of PEOPLE that traveled to some LOCATIONS. I've got a table in Excel - it's structured like this picture:
    Click image for larger version. 

Name:	1.png 
Views:	15 
Size:	5.0 KB 
ID:	3267

    I want to transfer this to my database, but before I do that I need to create a "many-to-many" table. How should I do that?
    I also created two tables:


    PEOPLE >> with attributes: ID and PERSON_NAME
    LOCATIONS >> with attributes: ID and LOCATION_NAME

    The ID's are unique for each entry.
    I need to create a third table called (e.g.) "people_locations" where I'll put the ID of the person in the first column and the ID of the location on the second column.


    So for example, in this third table (which would actually represent a many-to-many relationship) the entries (for the above picture) should look something like this:
    Click image for larger version. 

Name:	2.png 
Views:	7 
Size:	2.6 KB 
ID:	3268


    Any ideas how to do this?

  2. #2
    Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013 Pro Plus.
    No formulas, no VBA macro.
    If you get this far (show me!), we can tackle the ID part.
    http://www.mediafire.com/view/tis3p8.../03_08_15.xlsx

  3. #3
    Quote Originally Posted by Herbds7 View Post
    Excel 2010 with free Power Query Add-In.
    Compatible with Office 2013 Pro Plus.
    No formulas, no VBA macro.
    If you get this far (show me!), we can tackle the ID part.
    Actually, I managed to do it via VBA macro code below, but, thank You anyway

    Code:
    Sub many_to_many()
    
        Dim DataTable As Range
        Dim OutputRange As Range
        Dim RowOutput As Long
        Dim r As Long, c As Long
            
        On Error Resume Next
        Set DataTable = ActiveCell.CurrentRegion
        If DataTable.Count = 1 Or DataTable.Rows.Count < 3 Then
            MsgBox "Select cell in wanted table", vbCritical
            Exit Sub
        End If
        DataTable.Select
        
        Dim WS As Worksheet
        Set WS = Sheets.Add(After:=Worksheets(Worksheets.Count))
        
        Set OutputRange = Application.InputBox(prompt:="Select starting cell for output.", Type:=8)
    
        RowOutput = 2
        For r = 2 To DataTable.Rows.Count
            For c = 2 To DataTable.Columns.Count
    
                If DataTable.Cells(r, c) = "x" Then
                    OutputRange.Cells(RowOutput, 1) = DataTable.Cells(r, 1)
                    OutputRange.Cells(RowOutput, 2) = DataTable.Cells(1, c)
                    RowOutput = RowOutput + 1
                End If
                
            Next c
        Next r
        
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •