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