[AccessD] Issues of many-many relationships

[Steve]

Tina,

I have attached a diagram (many-to-many.pdf)  that explains what is
needed for many to many relationships. You can add people or files
independently as needed. 

Steve




-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
Of jack drawbridge
Sent: Wednesday, May 04, 2016 12:21 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Issues of many-many relationships

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