4 Replies Latest reply on Mar 15, 2012 5:18 PM by WillPeart

    Two records from the one table in a single layout. Possible?



      Two records from the one table in a single layout. Possible?



      For the purpose of example:

      I have a table called Songs and a related table called Artists. In the Songs table I have a field called Artist Name and another called Co-Artist Name. In the Artists table I have a related field called Name.

      I have a layout which displays details of a Song (from song table) and and I want to display details about the artist from Artist table. This works fine for one artist. But what if one song has an Artist and a Co-Artist? The information about both Artist and Co-artist is in the Artist table, identified by the Name field.

      In other words, I want to display two records from the one table in one layout.

      Is this possible? Does it mean I have to use a portal?


      Thank you in advance

        • 1. Re: Two records from the one table in a single layout. Possible?

          Since you are using two different fields in songs to record the Identity of the artists, a portal would require a single relationship between Songs and Artists in order to use a portal. Such a relationship could be set up, but the results will be problematic unless your real project differs from your "example". You could also define two relationships using two occurrences of the artist table to link a given Song Record to two different artists. You'd then add the fields from both table occurrences to your song layout to display info from both the Artist and Co-Artist. That still leaves you with the same problem that I see, however.

          What will you do when there are three or more artists collaborating on a song? If there is even a slight chance of more than one "co-artist", I suggest setting up a join table so that one Artist record can link to many songs and one song record can link to many artists.

          • 2. Re: Two records from the one table in a single layout. Possible?


            Thanks PhilModJunk. I have a classic many-to-many relationship it seems, and following your suggestion I am trying to build a join table. I found an explanation here, using a many-to-many relationship between students and classes:



            But this leaves a few things up in the air. The example says:

            - fields in the join table ‘will be populated when records in either the “Students” or “Classes” tables are created’.

            - If a student signed up for three classes, a student would have one record in the Students table but would have three records in the “Signups” table – one for each class he or she signed up for.


            I don’t see how? What information in either the Students table or the Classes tables allow Signups (the join table) to connect a StudentID with a ClassID or vice versa? The Signups table would have to be created manually to show each instance of StudentID linking uniquley to ClassID, no?

            • 3. Re: Two records from the one table in a single layout. Possible?

              A join table has, at minimum, two fields: each a foreign key to match to the primary key field of one of the two tables it links to facilitate the many to many relationship. Creating a link between a record in one table to a record in the other requires creating a record in the Join table with the matching values needed to link the two tables. Example. If artist "John Smith" authored "I Love you Babe", then you'd create a record in the Join table with the serial number for John Smith in one field and the serial number for "I Love you babe" in the other.

              A "basic setup" that a demo file I will share at the end of this post contains, is to place a portal to the join table on one or both of the layouts based on the main tables. A portal to the join table, when placed on the artists layout, will list all songs for which that artist helped create. The same portal on the Songs layout will list all artists that participated in authoring it. However, a list of serial numbers won't be much help so on the Artist layout, you'd add a song title field from the songs table to the portal row and an artist name field to that portal row when it is placed on the songs layout. In either case, a drop down list of Songs or Artists is used to both create the join record and to link it to the other table. When you do so, the field from that same table that lists a name or song title will display the name/title from the selected record.

              Here's the demo file that you can take a part and study. It not only demostrates this basic set up, but also demo's some more advanced interface methods for managing these links: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

              • 4. Re: Two records from the one table in a single layout. Possible?

                PhilModJunk = Legend

                I have been experimenting with join tables and portals, and now have my database (which is for the community archive in Northern Australia) doing exactly what I need it to do. The database can now describe archival items that have multiple creators as well as multiple sources of intellectual property. Fantastic!

                I am going to look closer at your demo file to get extra ideas for the interface for creating new records.

                Thanks very much