[AccessD] Issues of many-many relationships

Tina Norris Fields tinanfields at torchlake.com
Thu May 5 07:16:31 CDT 2016


Stuart,

Yes, that is what I've seen so far, but, I'm failing to see how to do 
that in a way that will not inconvenience the clerical person who will 
actually be doing the data entry.  I've somehow been really dense as I 
look at this puzzle.

TNF

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

On 05/04/16 6:18 PM, Stuart McLachlan wrote:
> 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
>>
>



More information about the AccessD mailing list