2 Replies Latest reply on Jan 18, 2011 10:43 PM by FentonJones

    Relationship Between Film Rolls



      Relationship Between Film Rolls & Photo Sets


      I've created a database in a single file with two tables, Film Rolls and Photo Sets.  Each record in the Film Rolls table has a unique Film Roll I.D.  Each record in the Photo Sets table has a unique Photo Set name, and is related to one or more Film Roll I.D.'s.  Also, more than one Photo Set record may be related to the same Film Roll I.D.  I'm manually entering the related Film Roll I.D.'s into fields in the Photo Sets table, Film Roll ID1, Fill Roll ID2, Fill Roll ID3, etc., using whatever number of fields is required by the number of related Film Roll I.D.'s.

      I'm trying to create a portal in a Film Roll layout showing the Photo Set name(s) related to that Film Roll.  I'm thinking the approach should be using the Film Rolls table as the starting table, and the Photo Sets table as the ending table.  The unique Film Roll I.D. in each Film Rolls record needs to be set up to be compared with all of Film Roll ID1, Fill Roll ID2, Fill Roll ID3, etc., in each Photo Set record.  Then the portal should display the Photo Set name(s) of the records where a match occurs.   It seems I need an "OR" operand when setting up the relationship.  But I don't see an "OR" operand.

      Perhaps visualizing the relationship would be easier if I explain it this way.  Each Film Roll record represents the physical roll of film that was in the camera.  Each Photo Set represents a particular time and place.  The same roll of film may have been shot at more than one time and place.  Also, at a particular time and place, I may have put a new roll of film in the camera.

      Perhaps there is a better approach to setting up the relationship.

        • 1. Re: Relationship Between Film Rolls & Photo Sets

          Okay, I've worked on this some more, and I think I know what I have to do . . .

          Set up more than one relationship, one each for Film Roll I.D.'s relationship with Film Roll ID1, Film Roll ID2, Film Roll ID3, etc.

          Display the Edit Relationship dialog box for each relationship, and select "Allow creation of records in this table via the relationship" every time.

          Create multiple portals on the Film Rolls layout, one for each relationship.  This will allow listing the related Photo Set names.

          Now it becomes only a design issue, how to neatly display the multiple portals on the layout.

          . . . unless someone can recommend a better approach.

          • 2. Re: Relationship Between Film Rolls & Photo Sets

            This sounds very much like a classic "many-to-many" situation; which requires 2 "entity" tables, with a "join" table between them, having an ID field from each of the entity tables. So a Photo set can be at multiple location/times, and that location/time can have multiple photo sets, independent of each other.