Many to Many insert

[Guest]

I have a many to many relationship but cannot seem to insert new records,
just update. The tables are Company, Adresses, and the join table
CompanyAddresses
on an Address insert I need the CompanyAddress table to be populated with
the appropriate ID's from Company and Address as well. Can this be done with
one query? What would it be? Thanks in advance.

 

[John Vinson]

I have a many to many relationship but cannot seem to insert new records,
just update. The tables are Company, Adresses, and the join table
CompanyAddresses
on an Address insert I need the CompanyAddress table to be populated with
the appropriate ID's from Company and Address as well. Can this be done with
one query? What would it be? Thanks in advance.

The normal way to do this would be to use a Form with a Subform. When
you're entering an Address, if it's truly a many to many relationship
(i.e. the address could apply to many Companies), how would you *know*
which Company is involved? You'ld need to insert a CompanyID value
into the CompanyAddress table; how would your query ascertain which
one?

I wonder if it might be better to simply model this as a one to many
relationship: ordinarily each address would apply only to one company,
would it not?

John W. Vinson[MVP]

 

[Guest]

Other entities, such as "Employees" can share an Address record.

Still curious as to how i can make inserts into both tables address and
company_address when adding a new address for a given company using
many-to-many. Any suggestions? Thanks in advance.

Steven

 

[John Vinson]

Other entities, such as "Employees" can share an Address record.

Still curious as to how i can make inserts into both tables address and
company_address when adding a new address for a given company using
many-to-many. Any suggestions? Thanks in advance.

Typically you would use a Form based on one of the "one" sides, a
Subform based on the junction table, and a combo box on the subform
allowing you to select a desired record from the other "one" side. The
combo could have a Not In List event to open the address entry form in
case there is no existing address.

My question about 'how would you tell which company' was not a
technical question but a logical question. Let's say you have 250
companies in the company table, 300 addresses in the address table,
500 records in the junction table. You're adding a new address.

What should be put into the junction table? The AddressID you've just
added, sure; but *which of the 250 CompanyID's*?


John W. Vinson[MVP]

 

[Guest]

Thanks for your help so far. To further explain...
I've created a company form with a subform for all associated addresses. I
would like to click new (arrow w/asterisk) on the address subform to add a
new address. The underlying structure is a many-to-many. Since i am already
on the specific company, that can be the CompanyID for the joiner table. But
the autonumber from the new address field is not populating the join table.
This is esentially two inserts in one. What am i doing wrong. I get the same
error when inserting just into the underlying subform query. Thanks

 

[John Vinson]

I have a many to many relationship but cannot seem to insert new records,
just update. The tables are Company, Adresses, and the join table
CompanyAddresses
on an Address insert I need the CompanyAddress table to be populated with
the appropriate ID's from Company and Address as well. Can this be done with
one query? What would it be? Thanks in advance.

The normal way to do this would be to use a Form based on one "one"
side table - Company say - with a subform based on the junction table.
Do you really have a many to many relationship? i.e. are there
Addresses shared among multiple Companies? If so, you'll probably want
a combo box to select the address from the Address table on the
subform; the combo box would have code in its NotInList event to pop
open an Address form if the user needs to enter a new address.

John W. Vinson[MVP]