If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Updating all tables in a many to many relationship
Hi,
I am using Access 2003. I have 3 tables, joined in a many to many relationship. i.e. Table 1 - Link Table (2) - Table 3 I have added the relationships in the 'Relationships' window, and used the wizard to create a form based on the 3 tables, showing data from Table 1, and its related data from table 3. When I update or add data to the form, data is updated/added to tables 1 and 3, but the link table does not update. Since many to many relationships are common, this must be a common problem, but I cant seem to find the answer either in the help/tutorials or on the web. If someone could point me in the right direction I would be very obliged. Many thanks, Gary. |
#2
|
|||
|
|||
Updating all tables in a many to many relationship
Hello,
I have tested the issue on my side and it works fine. When defining a Many-To-Many Relationships, did you select the Referential Integrity? Enforce referential integrity Cascade Update Related Fields Cascade Delete Related Records For your reference, I tested it by performing the following steps: 1. Create three tables which have a Many-To-Many Relationships: table_one link_table table_two id--------------t1id |--id stuff t2id-------- | stuff 2. Check the Enforce referential integrity: Cascade Update Related Fields Cascade Delete Related Records 3. Follow the form wizard to create a new form. Select three tables and select "Linked forms" when creating the form. The following article is for your reference: 304466 Defining relationships between tables in a Microsoft Access database http://support.microsoft.com/?id=304466 I hope the information is helpful. Sophie Guo Microsoft Online Partner Support Get Secure! - www.microsoft.com/security ================================================== === When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. |
#3
|
|||
|
|||
Updating all tables in a many to many relationship
Have you received any help with this? I am having the same problems.
"Gary Bond" wrote: Hi, I am using Access 2003. I have 3 tables, joined in a many to many relationship. i.e. Table 1 - Link Table (2) - Table 3 I have added the relationships in the 'Relationships' window, and used the wizard to create a form based on the 3 tables, showing data from Table 1, and its related data from table 3. When I update or add data to the form, data is updated/added to tables 1 and 3, but the link table does not update. Since many to many relationships are common, this must be a common problem, but I cant seem to find the answer either in the help/tutorials or on the web. If someone could point me in the right direction I would be very obliged. Many thanks, Gary. |
#4
|
|||
|
|||
Updating all tables in a many to many relationship
Sophie - thank you for replying and many apologies for the lateness of this
post, (work has been a bit hectic, sorry!) Anyhow, I tried what you suggested and sure enough it works. Many thanks for this. I wonder, is it possible to make a form/subform arrangement work, rather than linked forms. This would be more intuitive for my users. For instance: Table A contains Recipes, (Autonumber primary key, and (say) a Name field, and mixing/cooking instruction field. Table B called, say, RecipeIngredient, contains foreign keys of both the other tables, and an amount in grams of the ingredient. (Its the link table for the many to many) Table C contains Ingredients, with an Autonumber primary key, a Name field, and maybe optional dietary information on this ingredient. So, linking the tables just like you suggest, could you make a form/subform, that allowed users to input new Recipes, and input the ingredients for that recipe on the subform, (and maybe add new ones if not present in the database), and also input the amount of that ingredient for this particular recipe, and have all the tables update properly? I have tried to do this, but I still run into problems. Hope that is clear, and again, many thanks in advance for any help/tips, regards, Gary "Sophie Guo [MSFT]" wrote: Hello, I have tested the issue on my side and it works fine. When defining a Many-To-Many Relationships, did you select the Referential Integrity? Enforce referential integrity Cascade Update Related Fields Cascade Delete Related Records For your reference, I tested it by performing the following steps: 1. Create three tables which have a Many-To-Many Relationships: table_one link_table table_two id--------------t1id |--id stuff t2id-------- | stuff 2. Check the Enforce referential integrity: Cascade Update Related Fields Cascade Delete Related Records 3. Follow the form wizard to create a new form. Select three tables and select "Linked forms" when creating the form. The following article is for your reference: 304466 Defining relationships between tables in a Microsoft Access database http://support.microsoft.com/?id=304466 I hope the information is helpful. Sophie Guo Microsoft Online Partner Support Get Secure! - www.microsoft.com/security ================================================== === When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. |
#5
|
|||
|
|||
Updating all tables in a many to many relationship
Hi Elizabeth,
'Sophie' replied to my original post, and I have just asked her a further question, (see original post), but I can always keep you updated if you like. My email is (replace the "at" for the @ symbol) jn976rfgc"at"tesco.net. regards, Gary "Elizabeth" wrote: Have you received any help with this? I am having the same problems. "Gary Bond" wrote: Hi, I am using Access 2003. I have 3 tables, joined in a many to many relationship. i.e. Table 1 - Link Table (2) - Table 3 I have added the relationships in the 'Relationships' window, and used the wizard to create a form based on the 3 tables, showing data from Table 1, and its related data from table 3. When I update or add data to the form, data is updated/added to tables 1 and 3, but the link table does not update. Since many to many relationships are common, this must be a common problem, but I cant seem to find the answer either in the help/tutorials or on the web. If someone could point me in the right direction I would be very obliged. Many thanks, Gary. |
#6
|
|||
|
|||
Updating all tables in a many to many relationship
Hello Gary,
I test it again and it works fine: In the form wizard, select the three tables, then select "Form with subforms" option. Other options are the default. It create two forms: one main form and one subforms. When openning the main form and entering some data, we need to enter the data in the table one and the table two first, then enter data in the link table because we have defined constraints on it. You may test it on your side. I hope the information is helpful. Sophie Guo Microsoft Online Partner Support Get Secure! - www.microsoft.com/security ================================================== === When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. |
#7
|
|||
|
|||
Updating all tables in a many to many relationship
Sophie,
Again many thanks for the efforts to answer my questions, and sorry again for the late reply, (I still have too much work, so I can't complain really grin ) Anyhow, I will try this now I have a moment to myself. I think my main problem was not realising that you need to get data into the two 'main' tables before you can set up the information in the link table, due to the comstraints. I will give it a go later this evening. Many thanks for all the help, regards, Gary "Sophie Guo [MSFT]" wrote: Hello Gary, I test it again and it works fine: In the form wizard, select the three tables, then select "Form with subforms" option. Other options are the default. It create two forms: one main form and one subforms. When openning the main form and entering some data, we need to enter the data in the table one and the table two first, then enter data in the link table because we have defined constraints on it. You may test it on your side. I hope the information is helpful. Sophie Guo Microsoft Online Partner Support Get Secure! - www.microsoft.com/security ================================================== === When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. |
#8
|
|||
|
|||
Updating all tables in a many to many relationship
Hello,
You are welcome. If anything is unclear, get in touch. Sophie Guo Microsoft Online Partner Support Get Secure! - www.microsoft.com/security ================================================== === When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
relationship amongst 5 product tables | gg | Database Design | 1 | September 13th, 2005 08:20 AM |
Updating ODBC tables from Access Query | cg084 | Running & Setting Up Queries | 1 | August 30th, 2005 05:09 PM |
Many to many reationship design? | Dave | Database Design | 4 | January 18th, 2005 11:11 PM |
Tables not updating | Chico | Using Forms | 3 | October 22nd, 2004 08:06 PM |
Updating primary key in related tables? | Matt | General Discussion | 1 | September 9th, 2004 05:22 PM |