Forum Discussion

GlennHopkins's avatar
GlennHopkins
Qrew Trainee
2 months ago
Solved

Many to Many too Many...

I've searched through the QB discussions and couldn't find a good example of creating a many to many relationship where I can select records from the same table. I have the relationship working from one table to another and visa versa but I'm stuck now when trying to pull a record from the same table (see attached).

Here's an example of what I'm trying to do...

I have one table named Guests and a second table called NonGuests. Both have a relationship setup to a third table called, "Related Guests". The Guests will have either family members, friends, etc... who are NonGuests. But, there are Guests who have family, friends, etc... that are also Guests.

The issue I'm having is, when I create a Related Guest I need to pull a record from the same table because there are Guests who are related to other Guests. Am I overlooking the obvious?

Any suggestions would be great as from what I've read you shouldn't create relationships with "Guests with many Guests".

Thanks!

Relationships
  • ReneeHansen1's avatar
    ReneeHansen1
    2 months ago

    Hi!
    So I think what you'll need is (what I learned to call it) a "Join" table. In order to create a many-to-many, you are not having it go from one to the other and vice versa. 
    You have a table in between that one goes into and then the other goes into, so the "Join" table is the "child" of Table A (Parent Table), and a "child" of Table B (Parent Table).
    Then you'll have the related field from parent table A, and the Related field from Parent table B on your form, or reports, and you can select data from each and have it "joined" together in one table.

    Hope that helps!

2 Replies

Sort By
  • ReneeHansen1's avatar
    ReneeHansen1
    Qrew Cadet
    2 months ago

    Hi!
    So I think what you'll need is (what I learned to call it) a "Join" table. In order to create a many-to-many, you are not having it go from one to the other and vice versa. 
    You have a table in between that one goes into and then the other goes into, so the "Join" table is the "child" of Table A (Parent Table), and a "child" of Table B (Parent Table).
    Then you'll have the related field from parent table A, and the Related field from Parent table B on your form, or reports, and you can select data from each and have it "joined" together in one table.

    Hope that helps!

    • GlennHopkins's avatar
      GlennHopkins
      Qrew Trainee
      2 months ago

      Thanks Renee! That's pretty much what I ended up doing. I created 2 new fields that referenced the "Record ID" of each guest (using formula - numeric). Then I used those to build my "join" table while referencing the new fields. It ended up with exactly what I needed. I'll mark your response as the solution. Thanks again!