[AccessD] Issues of many-many relationships

Tina Norris Fields tinanfields at torchlake.com
Fri May 6 07:51:38 CDT 2016


Thank you, Don.  To quote our friend Rocky - a good program is one that 
works.  So, until I do know what the best approach is, I'll settle for 
one that works.  Thanks again.

TNF

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

On 05/05/16 11:42 AM, McGillivray, Don wrote:
> Hi Tina,
>
> Yes, you have understood my suggestion.  Treat the file record as the many side of a one-to-many relationship by including a foreign key pointing to the primary person.  Then on your form, allow a selected file to be shared with one or more other persons using the junction table to resolve the relationship(s).  Unless you also create a junction record for the primary person, this will complicate queries, as the relationship to the primary person will be expressed differently from the way the other relationships are expressed.
>
> I don't know if that's the best approach, but it may be a start toward something that works.
>
> Don
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tina Norris Fields
> Sent: Thursday, May 05, 2016 5:30 AM
> To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Issues of many-many relationships
>
> 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
>>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list