[AccessD] Issues of many-many relationships

Fri May 6 07:49:51 CDT 2016

Thanks, Jim.  I think I like #2 also.  I can see some very interesting 
ways to use that one.

TNF

Tina Norris Fields
tinanfields-at-torchlake-dot-com
231-322-2787

On 05/05/16 8:41 AM, Jim Dettman wrote:
> This might help a bit (or may not<g>) and I may be saying things you already
> know or has been said as I'm late to the discussion, but here goes.
>
> In Access, you can't directly model a many to many relationship in forms.
>
> The best you can do is handle each side of the relationship, which is a one
> to many with a main/subform combo.
>
> Main form is bound to the one (say persons) and the subform handles the many
> (person-file).  A combo control in the subform allows you to choose the one
> from the other side of the many to many (a file).
>
> You can also look at it from the other side of the M-M relationship:
>
> Main form is bound to the one (file) and the subform handles the many
> (person-file).  A combo control in the subform then allows you to choose the
> person.
>
> For data entry, you can:
>
> 1. Allow the user to switch back and forth between these two forms to add
> new records.
>
> 2. Have one or the other (or even both), but when using the combo in the
> subform, use the not-in-list event to pop-up a form to add a new record (in
> the first case above, add a new file to be chosen in the combo), requery the
> combo, and your off and running.
>
> 3. Use a un-bound main form, then a subform for each of the forms above,
> each of which will have a subform in it for the many.
>
>   The last I've found is very confusing for users.
>
>   I like #2 the best.  I like to provide both combinations of the
> main/subform's and then let them choose which side of the M-M they want to
> work from.  Depending on the task or the process they are walking through,
> it may be one or the other at different times.
>
> HTH,
> Jim.
>
>
>
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Tina Norris Fields
> Sent: Thursday, May 05, 2016 08:17 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Issues of many-many relationships
>
> Stuart,
>
> Yes, that is what I've seen so far, but, I'm failing to see how to do
> that in a way that will not inconvenience the clerical person who will
> actually be doing the data entry.  I've somehow been really dense as I
> look at this puzzle.
>
> TNF
>
> Tina Norris Fields
> tinanfields-at-torchlake-dot-com
> 231-322-2787
>
> On 05/04/16 6:18 PM, Stuart McLachlan wrote:
>> You need to do it in two steps:
>> 1. Create the new file
>> 2. Associate it with the person.
>>
>> Write a simple VBA function to do it.
>>
>> On 4 May 2016 at 18:09, Tina Norris Fields wrote:
>>
>>> Yes, that's been my experience.  But, I want to be able to create a new
>>> file to be associated with that person, and that isn't working because
>>> the intermediate table requires a matching entry in the primary table.
>>>
>>> TNF
>>>
>>> Tina Norris Fields
>>> tinanfields-at-torchlake-dot-com
>>> 231-322-2787
>>>
>>> On 05/04/16 3:20 PM, jack drawbridge wrote:
>>>> Tina,
>>>>
>>>> I think you first have to add the File to the tblFile, then you could
>>>> associate that file with Person X.
>>>> Similarly if there is a new Person, you would add the person to
> tblPerson
>>>> and then associate that Person with a selected File.
>>>> Just typing while thinking...
>>>> good luck.
>>>>
>>>> On Wed, May 4, 2016 at 3:07 PM, Tina Norris Fields <
>>>> tinanfields at torchlake.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> This should be easy, I'm sure, and I should know how to do it, but I'm
>>>>> having trouble with it.
>>>>>
>>>>> The examples I regularly find that deal with the many-many relationship
>>>>> are a bunch like this:
>>>>>
>>>>> Students --> Student-Class <-- Classes
>>>>>
>>>>> Where students may take several classes and classes may have several
>>>>> students, so, we create a details or junction table, containing the FK
> for
>>>>> student and FK for class - giving us one record for each combination of
>>>>> student-and-class.  Great!  But, this depends on existing data in both
> the
>>>>> Students table and the Classes table.
>>>>>
>>>>> Now, I'm dealing with a slightly different case.  I have a table of
>>>>> Persons and a table of their Files.  Each Person could have several
> Files,
>>>>> and each File could apply to several Persons.  (As an example, one File
>>>>> could be estate planning for a married couple, so the file applies to
> two
>>>>> individuals.)  Clearly, this is a many-many relationship, requiring a
>>>>> junction table.
>>>>>
>>>>> Persons --> Person-File <-- Files
>>>>>
>>>>> No problem creating a query to display the files pertaining to any one
>>>>> person. But, from here, how to create a new File for an existing
> Person?
>>>>> The Person-File table cannot have a new entry, because it requires an
>>>>> existing matching entry in each of the tables, Persons and Files.
>>>>>
>>>>> So, what the heck am I missing?  What am I doing wrong?  Any help
> getting
>>>>> my brain around this is appreciated.
>>>>>
>>>>> Thanks
>>>>> TNF
>>>>>
>>>>> --
>>>>> Tina Norris Fields
>>>>> tinanfields-at-torchlake-dot-com
>>>>> 231-322-2787
>>>>>
>>>>> --
>>>>> AccessD mailing list
>>>>> AccessD at databaseadvisors.com
>>>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>>> Website: http://www.databaseadvisors.com
>>>>>
>>> -- 
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>>>