Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    Junction table


    All, Using Access 2016...In my database; I have a junction table to resolve a many to many relationship....Can I add another table to this many to many without it coming to bite me later? 3 tables + 1 junction table with
    4 fields(primary key autonumber, the other 3 ID fields from each of the tables) I was curious so I created this setup and it works...Just wondering if it would be any fall out down the line. Thanks

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Well, it would help to know the Tables involved and then the one you are thinking of adding. Without that we would just be guessing.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Might regret saying this, but I'm scratching my head trying to think of a situation where 3 properly normalized tables could be all related to 1 junction
    I'm drawing a blank...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks for responding...you may be right about normalization but I've seen it once but can't remember the outcome...I'll see it thru in my database to see how far I get. See attached. All responses are welcome. Thanks in advance.
    Attached Thumbnails Attached Thumbnails RelationshipDiagram.png  

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As micron said, I don't have an example at the moment, but I think it is ok to have a junction table with more than 2 "connecting tables".
    As for your diagram and application tell us what tblCDEID represents --simple terms. It seems it is dealing with Defect, so maybe a relationship directly with Defect???

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The 2 right side tables almost appear to be lookup tables, which I wasn't considering. However, not all fields are shown so there is still the possibility that tables are not 100% normalized. In addition to what Orange said about defects, what looks suspect to me is that tblDefects has a reason field. Does the reason pertain to the defect or incident? If it is the latter, the field is in the wrong table. If you're posting to get feedback on your relationships, why not show all the fields in tables, or as much as you can fit into an image?

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Agree with micron re reason --seems to be in wrong table. As always, we need a "fuller" description from the OP to help put the various pieces into business context using simple, plain English.

    I did some research and this is the only example I could find (in my allotted time) re 3 table junction. See Fig 3.18.
    I'm sure I've run into other examples, but can't find anything.

  8. #8
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Hello all...sorry for the relationship confusion....tblIncident has three fields: IncidentID, IncidentName and IncidentCaseDescription. tblDefect three fields....DefectID, DefectName and DefectDescription. tblCDE three fields...CDE is just the companys acronym...CDEID, CDEName and CDEDescription...
    Sorry for the lack of code tags...for some reason they don't show up when using my phone vs Laptop...

  9. #9
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Also, all three: incidents can have many defects and vice versa incidents can have many CDEs and vice versa...also the user is telling me it maybe a case where a CDE may also have a Defect. This has not be verified...Thanks

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks for the update.
    Could you also give us a description of a "typical" incident and the associated "defect(s)" that might be encountered in your "business"? A scenario showing us what relates these pieces using some values from your database.

  11. #11
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Sure...the user is giving me updates now so there may be additional fields adding to the tables that contain information they want to see...starting to make sense..I'll keep u posted..

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ?? What exactly is an Incident? Defect?
    Remember, you know your environment and business. We only know what you tell us. So a complete description with examples will help you get focused responses.

  13. #13
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Ok…So I added the additional fields the users wanted. I had the subform working the way I needed but now for some reason it doesn’t work. I've attached a sample of the DB stripped of sensitive info. The company creates Incidents to track when Defects are recalled. CDEs are also tracked which are now tied to the Defect. The form is designed for the users to input Incidents and the Defects and CDEs tied to them. The user must select the Division from the combo box. The Incident data is inputted. The purpose of the subform is because of the many:many relationship between the Incident; Defect and CDE. Because there can be many Defects and CDEs; I made the DefectID and CDEID and cbo box. So, one the Division ID is selected; it will filter only the Defects and CDEs related to the Division. For some reason; the drop down for the Defectid is empty on the form. When I open the sfrm; I can get it to work. The Division is selected first; then the Defect and CDE last. I originally had two separate subforms; one to select the Defect(s) and one to select the CDEs. I couldn’t get the CDEs to work. I tried to use the tblCDE as the recordsource and cbobox based on a query in the Rowsource. I thought this was going to be simple but obviously; I’m missing something. I need to get this done so I can create reports for management to review. I appreciate any assistance.
    Attached Files Attached Files

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    slimjen,

    You have not told us in clear terms what you mean by Incident, Defect and CDE. You obviously understand your business and environment, but you'll have to "dumb it down" for the rest of us who are trying to decipher your "jargonese".
    What exactly does this really mean:
    The company creates Incidents to track when Defects are recalled.
    I will look at the sample database, hoping to glean some key points, but you description of the potential new fields for tables, use of subforms, and expected reports tend to indicate you do not have a clear requirement for the proposed database.

    When you say "for some reason it doesn’t work" --what is the it? What would we see as output if it were working properly?
    You have almost 700 posts in this forum so you have some experience with posts and threads and the problems with communication and lack of clarity.
    Good luck with your project.

  15. #15
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Ok...I'll try to explain....Divisions are depts...Each are responsible for different products that may have what the company calls Defects(products that have imperfections.) The company wants to track the number of products that have Defects that can't be repaired so they are recalled. When the Defect is recalled it is assigned an Incident which is like a catagory (which gives information about why the Defect was recalled and failed; among other pertinent info; and whether it can be resolved) A CDE is like a product tag assigned to each defect. Ex. Product A has a dent on it and it is sent back and assigned a Defect code. A CDE tag the Defect and it is put into an Incident Catagory This is tracked in the database. So, the Incident form has a Combo box to first select the Division. The subform has two combo boxes with the selections that should be filtered base on the selection of the Division. The first is the Defect and based on the selection should filter the CDE assigned to the Defect. The company says that an one Incident can have multiple Defects and one Defect can have multiple Incidents and they want to be able to input on the form. The reason for the Subform with the combo boxes is this is the only way I can think of to assign multiple Defects to an Incident. The recordsource for the Defect combo box has a criteria filtered by the division on the Incident form. When I open the a subform and run it; the box pops up to enter the division and it filters the defect but when I click it on the Incident form after the Division; I don't get any of the defects from the dropdown but do get CDEs from the dropdown. Once I get this resolved; I need to create a report showing all the Incident Categories and the Defects with its CDE tags that the user can filter by each Division. Hope I've given enough details to clarify. My wife can't understand me either Thanks

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to use a Junction table
    By gwboolean in forum Access
    Replies: 17
    Last Post: 09-15-2017, 03:06 PM
  2. Replies: 13
    Last Post: 05-10-2017, 12:43 AM
  3. Junction Table Help
    By blkane in forum Database Design
    Replies: 5
    Last Post: 10-19-2014, 11:58 AM
  4. Junction Table\
    By Akitt23 in forum Access
    Replies: 5
    Last Post: 11-15-2013, 12:22 PM
  5. Junction Table
    By mae3n in forum Database Design
    Replies: 2
    Last Post: 01-15-2011, 10:23 PM

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