Results 1 to 7 of 7
  1. #1
    William Royce is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    3

    Junction table creates spurious "one-side" records

    I am stymied. The problem probably has a simple fix, but nothing I can do (or find online) comes close to solution.



    My DB has a basic many-to-many relationship. It correlates scenes and roles in a theatrical performance. Each scene has many roles, and most roles are in multiple scenes, so I have a many-to-many set of tables: tblRoles, tblScenes and a junction table lnkRolesScenes. Each of the "one" tables has just an ID (i.e., RoleID) and a description (RoleName). The junction table contains only the foreign key IDs, which constitute its composite key. There is a query (qryRolesScenes) that joins (correct word?) all three, and a form (frmRolesScenes, which turns out to be irrelevant to the problem) to address the query.

    The problem: updating the junction table spontaneously adds new records to the other two tables. For example, if I enter a valid RoleID in lnkRolesScenes.RoleID, things work right. But if something is entered in the RoleName field, it creates a new record in tblRoles, with a new RoleID and the new data in in RoleName. My understanding is that this should not be possible. Clearly, my understanding is wrong.

    Like I said, it's probably a simple fix -- but I can't find it anywhere.

    Got any ideas?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't interact with table/query, use form. If you include lookup table in form RecordSource in order to display related info, don't allow edits to those table fields.

    Tried to replicate issue and can't. If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    William Royce is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    3
    I've tried it from several iterations of forms, but there is always this surprise creation of [sub-table?] records. It was simply easier to see the effect when entering directly into the query. Here's the attachment: RolesAndScenes.zip

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I finally figured out how to replicate this in my db. I was not use autonumber field as primary key. Yes, what you experience is possible. This is why you should use a form for data entry and then either don't include the lookup tables in RecordSource or set controls bound to those fields as Locked Yes and TabStop No.
    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.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I use a main form/sub form arraignment when using a junction table (many-to-many relationship). This requires two forms for data entry. In your case, a form/sub form for Scenes/Roles and a form/sub form for Roles/Scenes.
    There also should be an entry form for each of the single tables.
    In the junction table, I set a compound INDEX to keep from having duplicate entries.

    I didn't know how you wanted to see the Scene fields - is it necessary to see the SceneNumber, ActNumber and ProductionID when selecting the scenes for a specific role?

    There were a LOT of duplicate entries - I eliminated them.
    I am not fond of forms in datasheet view, but I left yours there.
    Attached Files Attached Files

  6. #6
    William Royce is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    3
    I took the original basic arrangement from a video tutorial on Many-to-Many relationships. Mostly, it was pretty useful, but had a few anomalies to be overcome. This is the one that stopped me cold. Duplicate entries and other poor designs you note are partly a result of successive attempts to get at the problem, using this version as a test bed. Lots of "what if I try THIS?" with varying degrees of enlightenment. As near as I can tell, the source of this problem lay in the frmRolesScenes.RoleName (etc) textbox being (1) a textbox instead of a combobox, and (2) its control source being the tblRoles.RoleName. This allowed creation of entries to tblRoles from a place that was unexpected. I think. [I'm sure there are more subtle errors in there, but these two seem to be key.]

    Also, as you suggest, making the junction table index into a compound index has had good effect.

    Thanks for your assistance. I'll be back. [Now, how to make this apparent bug into a feature . . . ].

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    You have an important table missing - something like tblPlays: PlayID, PlayName, ...;

    Then rename tblRoles as tblPlayRoles: PlayRoleID, PlayID, RoleName;

    And rename tblScenes as tblPlayScenes: PlaySceneID, PlayID, SceneName, SceneNumber, ...;

    (You don't have same role or same scene in different plays!)

    Now, when you want to register play scenes where a role is/was played, you need a table tblRolesScenes: RoleSceneID, PlayRoleID, PlaySceneID

    Create a single form based on tblPlays (fPlays), and add a tab control with pages for play roles and play scenes;
    Create 2 continuous forms based on tblPlayRoles and tblPlayScenes, and add them as subforms linked by PlayID respectively to matching tab pages in fPlays form;
    Add 2 invisible unbound text boxes into form fPlays. For both subforms source form, create an OnCurrent event, which writes current PlayRoleID/PlaySceneID into matching unbound text box in fPlays;
    Create 2 continuous forms based on tblRolesScenes - In one form only visible control (a combo box) linked to PlayRoleID displays role info, in other form only visible control (a combo again) linked to PlaySceneID displays scene info;
    Add those forms to tab pages - one with role info to page with play scenes subform, another with scene info to page with play roles subform (NB! As subforms of fPlay!). Link those subforms with matching unbound controls in fPlays;
    (Probably you need AfterUpdate events for last 2 subforms to refresh another one on another tab page to keep them in synch.
    Create

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

Similar Threads

  1. Replies: 4
    Last Post: 09-10-2019, 01:33 PM
  2. Replies: 14
    Last Post: 08-29-2019, 04:52 PM
  3. Replies: 4
    Last Post: 08-09-2019, 06:38 PM
  4. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  5. Replies: 2
    Last Post: 01-23-2017, 07:06 PM

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