[AccessD] Issues of many-many relationships

[McGillivray, Don]

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