Results 1 to 15 of 15
  1. #1
    Man_Over_Game is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    12

    Unhappy Difficulties utilizing "junction" or "Many-To-Many" table in form

    To give an idea of my experience level, I know how to do all the basic stuff in Access (I even understand some of it, too), but learned most of it from a book once that I read several years ago, and so I probably know a lot less than I think I do, so if I say something that doesn't make sense or just sounds plain stupid, please let me know. And anything you do add, PLEASE phrase it as if I truly am certifiably stupid. It will really help me understand every solution.



    My end goal is pretty simple. I have a bunch of people who we need to track in terms of physical activity, like workouts and such, per each month and per each day of said months.

    My solution, in theory, was not complicated. Have tblPersons and tblMonths, linked with a "junction" or Many-To-Many" table tblPersonsMonths, which had a child table of "tblDays". My plan was to have each combination of tblPersons + tblMonths to be unique, and have a set of days for each unique combination.

    I have set up tblPersonsMonths with a "composite key" (two primary keys) that point to my two main tables, and I also threw in an autonumber so that tblDays could have something easier to point to. I've tried to read up on whether this is an appropriate thing to do, but it's hard to find stuff on the topic that's easily understood.


    Ideally, my main form would have you select a Person, then a subform would utilize the ID from tblPersons to start the half of the tblPersonsMonths with a combobox that shows all 12 months that let you select the OTHER half (which month you want to work with).

    My problem seems to lie in the fact that my combobox in the subform isn't actually going/creating the record for that specific month. Rather, it's taking the current record and changing the month to match what the combo box is.

    So if I have:
    JunctionA composed of PersonA and MonthA (which we'll say is January), with 30 days filled out,
    and I use my combobox to swap to MonthB (February), it'll just change everything to be:
    JunctionA, PersonA, MonthB (February) , 30 days
    Where it should be:
    JunctionB, PersonA, MonthB (February) , 0 days (assuming I haven't put in any days yet)



    I understand that this may be something related to a record search macro, combined with a record creation if that record isn't found, but finding online sources for this kind of stuff is proving very difficult.

    In addition, if anyone could provide any insight of what I should watch out for in this kind of situation when I move on to tackle the tblDays, I would really appreciate it. A select few sources talk about junction tables, but NOBODY mentions anything about child tables off of Junction tables, which is....foreboding.

    A few other technical things of mention:
    My combobox is correctly providing the MonthID of whatever month it selects, it is not changing what record is selected but rather it is changing the current record's information. I am familiar with the concept of having multiple columns in a combobox but making the outputted ID invisible via 0" column size. Not trying to sound like a jerk, just thought I'd save the effort for anyone who'd try to let me know.

    [EDIT]
    One thing I forgot to mention, tblDays also uses a composite key, utilizing the "DayofMo" and "PMID" (tblPersonsMonths autonumber), so that each "Person's Month" has a Day 1 that're all unique.

    [Edit2]
    For anyone using this as a resource for their own issues, check out these links for more information on the structure of Many-To-Many relationships that I found useful. I found these partway through this submission, so my submission doesn't include info from these:
    http://www.utteraccess.com/forum/ind...wtopic=2049214
    http://www.tomjewett.com/dbdesign/db...e=manymany.php
    Last edited by Man_Over_Game; 06-29-2018 at 10:15 AM.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sounds like you have included tblMonths in the subform RecordSource and bound the combobox to field from that table instead of field of junction table.
    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
    Man_Over_Game is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    12
    Just took a look at what you suggested.

    Subform Recordsource: tblPersonsMonths

    Combobox Rowsource has just tblMonths on it, the bound column is MonthID.
    Combobox Control Source is PMonthID (the Junction table's version of MonthID)

    Assuming I understand everything correctly:
    my subform's recordsource is fine (pulling from my junction table)
    my combobox's rowsource (where it gets the list from) is accurate (showing everything I have listed on my Month table)
    my combobox is returning the proper value (the ID for the specific month, rather than names/max days, seasons, etc)
    my combobox is outputting that number to the proper location (PMonthID, or what my junction table thinks what the month ID should be).


    Unless I'm not getting something, I don't think what you suggested may be the issue, but that might be my lack of knowledge.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Then are you entering data for a new record in NewRecord row or are you editing existing record?
    Last edited by June7; 06-29-2018 at 02:29 PM.
    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
    Man_Over_Game is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    12
    I'm not sure I fully understood the question, but to answer the best I can: the combobox SHOULD either pull up the junction table record for the month I select (if there is already an existing junction record), or it should create one using the value I selected as one of the primary keys if it doesn't already exist. It SHOULD NOT edit an existing record.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Why to have separate tables for months and days? Have a table e.g. Activities with a date field. All activities are registered by date. To get monthly summary, you run an aggregate query grouped by year and month, or by query calculated field in format yyyymm. Such design will work for any month, and for any year.

  7. #7
    Man_Over_Game is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    12
    That's actually pretty genius to have a single table using a date field.

    I do like that idea, and may go that route with this database, but I do have other projects I want to do that all hinge on this one question being answered with junction tables with child.

    Assuming I don't decide to go the route of changing everything to utilize a Date field, do you know how to solve this issue?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Using combobox to serve as search tool and data entry control requires code. One way is to be UNBOUND and not used for direct data entry. Code in combobox AfterUpdate event to search for existing record and if found go to it and if not go to new record row and input the selected value to initiate new record.

    Otherwise, use it as a normal data entry BOUND control and code in combobox BeforeUpdate event to validate input on new record row. Search for existing record and if found abort new record and go to existing.
    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.

  9. #9
    Man_Over_Game is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    12
    Thanks, June. That pretty much solidified what I thought was going to be the case. Any suggestions on where to go from here? Or a resource you'd recommend to find that kind of information?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I probably edited my previous post after you read it. Review again. Referenced events as well as searching records are common topics. Do research and when you have trouble with specific code post for analysis. No specific resource.
    Last edited by June7; 06-29-2018 at 09:23 PM.
    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.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've been watching this thread to see what progress you were making. Yesterday I threw together an example dB.
    You seem to be as a loss as to where to go from where you are at. It would be helpful to see your dB, but .......

    I don't use compound PK fields; instead I use compound indexes. See Microsoft Access Tables: Primary Key Tips and Techniques

    If I understand correctly, you only want one type of exercise per month, per day, per person. What happens if the person stays with you more than 1 year and does the same exercise as the previous year in the same month and the same day?
    Maybe you should just have a field for a date (mm/dd/yyyy).

    I did use 1 table for the months and days as June suggested.

    Remember, this is just an example. There is no error handling, no buttons, no menu, no reports.
    Maybe seeing this will help you solidify your requirements.

    Good luck with your project......
    Attached Files Attached Files

  12. #12
    Man_Over_Game is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    12
    Thank ssanfu.

    Both of your solutions are completely valid, and actually easier than what I'm trying to implement, and I appreciate that.ArviLaanemets did come up with the same solution you did with the Dates, and someone did suggest just ditching the child table.

    Even if it's not the most efficient solution to the workout issue, I'm still trying to find a solution to have a functioning child table to a junction table. There are so few examples of it, and most suggestions I've gotten from people come to the conclusion of saying "Don't". But a lot of my Access projects utilize a junction + child table relationship, and I keep hitting this brick wall.

    I do want to say, though, the database you sent me was very fleshed out. I'm very impressed at how much work you put into it, and I'll definitely study it for reference material. Everything looks well done and professional! Thank you.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm not sure what you mean by "child" table. Do you mean you want at table for months and a table for days of the month?
    If so, in the junction table, you would have a field for the month and you need to add a field for the days.

    Do you have a dB of what you are trying to do? Or a drawing on paper you would post?

  14. #14
    Man_Over_Game is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    12
    Quote Originally Posted by ssanfu View Post
    I'm not sure what you mean by "child" table. Do you mean you want at table for months and a table for days of the month?
    If so, in the junction table, you would have a field for the month and you need to add a field for the days.

    Do you have a dB of what you are trying to do? Or a drawing on paper you would post?
    Sorry for being so long since I posted.

    The goal was to have each Person have a unique set of Dates, each Date have a unique list of booleans to track which excersizes were completed on that date, and each Date was composed of Months and Day, each composing a unique identifier for each Day.

    So, for example, I could pull up Month record 8 (August), Day record 29, for Man_Over_Game, and it'll show me the exercizes I did that day.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Man_Over_Game View Post
    , each Date have a unique list of booleans
    If you mean having a checkbox field in a table for each exercise, this is a really bad design. Let's say you finished your dB and you have been using it for 4 months.

    Now you want to add an exercise "Tire Flipping". To add "Tire Flipping", you would have to modify the table to add another check box (yes/no) field, modify the saved queries, modify the forms, modify the report and modify any code. And this would happen every time you wanted to add an exercise.

    If you were using a design like my example in post #11, all you would have to do to add a new exercise is to add a new record to the exercise table.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-07-2016, 12:22 PM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  4. Replies: 1
    Last Post: 02-05-2015, 05:41 PM
  5. Replies: 1
    Last Post: 09-03-2014, 03:27 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