Many to Many

[dominicg123]

I have a database with contact that have actions related to them. My problem is that a contact can have many actions and an action can be related one or two contact. Is that a many to many relationship?

 

[dhookom]

Yes, that is a many to many. It is implemented with a junction table. The fields in the junction table would include at least the primary key values from the contacts and actions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dominicg123]

I have a contact form with an action sub-form. My action sub-form is link with the contactid field. I want to add a combo box called secondary contact. Then I want the action to be displayed in both contact.This is where I am gettin lost.Sorry for my pour English...

 

[dhookom]

First you need to tell us if your first question was answered. It sounds like this is a different question. It also sounds like you have two contact fields in the same table which is generally considered bad practice.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dominicgingras]

Yes my question was answered but my second post is refering to the same problem. I know that to have two contact field is "bad practice" that why I am trying to find an alternate solution.

 

[dhookom]

You should have two contact records rather than two contacts in a single record.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dominicg123]

Sorry I dont understand!!

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dominicg123]

Thank you PHV.
I look at your document and I created the table necessary for a many to many relation. My action table and my contact table are link with a table that contain both tables primary key. My contact form contain a subform that show the actions related to a contact id. My problem is that I am trying to figure out how to make a form to edit all this. This is where I am scratching my head

 

[dhookom]

Can't you edit "all this" in your form and subform?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Josua]

I have a question that is very similar to this situation, just a different question about it. I have exactly what this person has, with the form and subform in order to do a many to many relationship, and I can edit and create new entries in each of the one-side tables, but the intermediary table is not updated after I close the form. Are my relationships messed up? or maybe something with the referential integrity?

I have had similar problems with the relationship between tables allowing for entry into one to update the other, which I feel is the core of this system. Thanks in advance for any help!
Jacob

 

[dhookom]

It might help if you described
- your tables with primary and foreign keys
- your form/subform record sources
- the Link Master/Child properties

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Josua]

Off of my test db, which I use to try to figure things out, and keep it very simple, I have 3 tables.

Task Orders, Vouchers, and intermediate table TaskOrder_Voucher

(created due to the fact that it is a many-to-many relationship between Task Orders and Vouchers)

Task Orders has a composite PK of Task Order and Region, Vouchers has a simple PK of just Voucher Num. The Intermediate table uses a composite PK of Task Order, Voucher and Region.

I have the relationships set up so that all the primary keys are used as the foreign keys as well. (Task Orders.Task Order <--> TaskOrder_Voucher.Task Order, etc) Both relationships are a one-to-many, with the many being on the intermediate table's side.

My Form record source is the Task Order Table, and my subform info is a query based on the relationship of the Voucher table and the TaskOrder_Voucher table.

Both Child and Master Properties say Task Order;Region.

------

What is frustrating the most, is this is even simpler than that. It is as easy as that I have 2 tables that when I enter info on one table, and that record is linked to the other table, I want the info to appear there as well. I am either missing something easy or doing something very much wrong.

Thanks for any help
Jacob

 

[SQLSister]

First, never use a PK that has a possiblity of being changed. If you have to, then set up the tables so that they make the change automatically when they are updated. Look in BOL for cascading update to see how.

"NOTHING is more important in a database than integrity." ESquared

 

[SQLSister]

OOps, sorry thought I was inthe SQL Server forum. HOwever, there should be a way to do this Access as well.

"NOTHING is more important in a database than integrity." ESquared

 

[Josua]

Just for clarity, I'm not worried about changing the PK, I just want to add more. But no new record is made in the related table.

 

[dhookom]

I don't understand which table your record is added to and which table you expect to see the same value added to. Normally you first add records to the Task Orders and Vouchers tables so you can then create your records in the junction table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Josua]

I have a setup almost exactly like this page demonstrates:

http://www.databaseanswers.org/tutorial4_db_schema/tutorial_slide_5.htm

Just to keep using this example, say I create a form that is based off the Customer_Order table, and then inside I have a subform, based of the product table. I can enter info into the Customer_Order portion of the form, it works fine, but when I attempt to enter info into the subform, it gives me an error that says "Cannot create record" but then allows me to put in info.

When I look at the tables, I have info in the Customer_Order table, and I have info in the Product table, but nothing in the intermediate Customer_Order_Product table, which means the two tables have no linkage.
Does this make sense?

 

[PHV]

I think the subform should be bound to the Customer_Order_Product table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Josua]

I tried to make that work, but this is the error I get:

The object doesn't contain the Automation object 'order_id'

I have no idea what that means. I even tried making Order_id one of the items that linked to the subform, but that didn't work either.