Results 1 to 3 of 3
  1. #1
    craigugoretz is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2015
    Posts
    9

    Many to many cross reference table as a subform?

    Lets assume that I have three tables: Media, Author, and Media/author_xref:


    There is a many to many relationship between Media and Author.
    Media/author_xref is the cross reference table.

    Media
    -------
    ID
    Title - text

    Author
    -------
    ID
    Name - text

    Media/author_xref
    ---------
    ID
    Author - foreign key to Author.ID
    Media - foreign key to Media.ID

    I know that I could create a form based on the Media table with a multivalued lookup field to the Author table, but my client has a problem with having to scan through too many items in combo boxes. She wants only single valued combo boxes because she can type the first few characters of an item and have the combo box jump to items starting with those characters.

    Instead, I am wondering about having the Media/author_xref table as a subform in a form based on the Media table. In this subform I would just have the ID and Author fields of the table visible, and the Media field would be filled in automatically for each record. The user would select an author from a combo box (looking up Name from the Author table) for each record. In this way a Media can have multiple Authors, and an Author can create multiple Media.

    Here is a diagram:
    Media form:
    ID: xxxxxxxxxx
    Title: ***text field***

    Media/author_xref subform:
    ID: xxxxxxxxxxxxx
    Author: ***combo box displaying all Author.Name***

    Any insight regarding how to accomplish this would be appreciated.

    Thanks kindly,
    Craig

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    That does sound like a conventional form/subform arrangement. Basic Access functionality. http://www.access-programmers.com/ma...-concepts.aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    craigugoretz is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2015
    Posts
    9
    Thank you for your help -- I got it to work!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. cross table
    By gad10 in forum Queries
    Replies: 3
    Last Post: 12-28-2013, 06:31 AM
  2. Replies: 4
    Last Post: 08-13-2012, 04:39 PM
  3. Replies: 5
    Last Post: 08-09-2012, 12:49 PM
  4. Cross reference
    By Anne in forum Access
    Replies: 9
    Last Post: 09-23-2011, 08:01 AM
  5. Replies: 1
    Last Post: 06-18-2011, 10:00 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums