[AccessD] Issues of many-many relationships

[Jim Dettman]

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
>>
>

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com