[AccessD] Issues of many-many relationships

[Stuart McLachlan]

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
>