I have taken a junction table approach to record shared addresses, linking addresses to people many-to-many to represent the fact that one address can belong to many people, and one person can have many addresses. But my problem with it is that when it comes to recording a new address as belonging to a particular contact, there are now more stages to the process than there would be if the relationship between people and addresses were one to many.
If I'm in a form whose record source is tblPeople, I can't have a subform into which I enter the new address, I can only have a subform that pulls up pre-entered addresses through a combo box drop down list. So I have to 1. check the address isn't on a LONG drop down list; then 2. click on a button taking me to a SEPARATE address form in which I enter the address; and then 3. return to my original person form and pull up the address I just entered to attach it to the person with the focus.
Whereas with a one-to-many relationship I could enter a new address directly into the subform on the person form.
I can convert the relationship between people and addresses to one-to-many, if on the one side of an address table I include the pkID key for a table in which relationships between people are stored. So the "one" thing I was joining to the address/adresses was "Frank and Lisa". but in terms of pulling up the correct information on an individual's form, I don't think I can make this work. I don't think I could link the main form to the subform so that it recognised that not only Franks records, but also records listed for "Frank and Lisa" should be displayed, even though the form's focus is Frank. It would have to trace back from the primary key in the people-to-people junction table, to the foreign keys that are linked together in it. <!--IBF.ATTACHMENT_2071547--><!-- THE POST -->
If I'm in a form whose record source is tblPeople, I can't have a subform into which I enter the new address, I can only have a subform that pulls up pre-entered addresses through a combo box drop down list. So I have to 1. check the address isn't on a LONG drop down list; then 2. click on a button taking me to a SEPARATE address form in which I enter the address; and then 3. return to my original person form and pull up the address I just entered to attach it to the person with the focus.
Whereas with a one-to-many relationship I could enter a new address directly into the subform on the person form.
I can convert the relationship between people and addresses to one-to-many, if on the one side of an address table I include the pkID key for a table in which relationships between people are stored. So the "one" thing I was joining to the address/adresses was "Frank and Lisa". but in terms of pulling up the correct information on an individual's form, I don't think I can make this work. I don't think I could link the main form to the subform so that it recognised that not only Franks records, but also records listed for "Frank and Lisa" should be displayed, even though the form's focus is Frank. It would have to trace back from the primary key in the people-to-people junction table, to the foreign keys that are linked together in it. <!--IBF.ATTACHMENT_2071547--><!-- THE POST -->
