[AccessD] Issues of many-many relationships

Thu May 5 07:29:48 CDT 2016

Don,

You have understood the matter correctly.  It is precisely because the 
files are not independent entities that I am hitting a wall.

To check that I am understanding your suggestion - are you saying that 
each file would have a principal person as part of its definition?  
That's, of course, what I'd do if there were a one-to-many relationship 
of Person-to-File.  Then, any additionally associated persons would be 
connected via the junction table.  Hmm - that might work well.  Thanks 
for the thought.

I would also love to be able to use my household structure for this, 
wherein each person is a member of a household.  But, the client could 
be a single individual in the household rather than the entire household.

TNF

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

On 05/04/16 7:08 PM, McGillivray, Don wrote:
> Hi Tina,
>
> I generally think of many-to-many relationships as being defined by circumstance.  That is, the records on either side of the junction exist independently of each other and are caused to relate to each other depending on a specific condition.  To take your example of the class and students, the classes and students exist independently of each other and are related only through the condition of a student choosing to be enrolled in the class.
>
> The problem you're trying to solve is different, in that, presumably, a file does not exist except in relation to a specific person or persons.  It's not just a generic entity sitting out there waiting to be joined to one or more persons by virtue of some heretofore unforeseen circumstance.  (Unless I misunderstand your problem.)
>
> So, perhaps the file record can be created as the direct child of a primary person record, and then shared with one or more other persons using the many-to-many junction.  Or maybe the person records are related to a common parent record (family, couple, group, etc.) and the file record is related to the parent record instead of individual person records.
>
> Maybe these thoughts will inspire you toward another more effective solution.
>
> Don
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tina Norris Fields
> Sent: Wednesday, May 04, 2016 3:10 PM
> To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Issues of many-many relationships
>
> 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
>