1 Reply Latest reply on Oct 15, 2010 1:04 PM by philmodjunk

    Many to Many relationship



      Many to Many relationship


      I have a database where I want to input information from invoices and then match as many or as few stores to that invoice as I want. So what I have is a table called invoice and a table called stores and I have another table called match. so in the match layout I have a portal for invoice and a portal for stores. I then have a serial number that is generated on all the three tables so they all share a common serial number. My problem is when I go to report my data. When I base my report on invoice, it shows all the invoice line items, but only the first row on the stores. When I base my report on stores, it shows all the stores but only the first line of invoice items, what am I doing wrong? I want it to report all the information from all the invoice items and all the fields related to that invoice.

        • 1. Re: Many to Many relationship

          For a typical many to many relationship, your tables would be related like this:


          Invoices::InvoiceID = Match::InvoiceID

          Stores::StoreID = Match::StoreID

          Invoices::InvoiceID and Stores::StoreID are auto-entered serial numbers.

          It's not a bad idea to have a serial number for a MatchID, but it's not needed for these relationships.

          I usually place a portal to the "many" table on each of the "one" layouts and then put fields from the 2nd "one" table into the portal as need to supply the details (such as a store's name or location) I need.

          Here's a demo file that matches "contracts" to "companies" that you might want to check over:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html