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
|
|||
|
|||
Related Tables
Essentially what I want is simple. I have two different tables. Both store
the same records, but each one stores different information about the records. Each time a record is added to or deleted from Table A, I would like the same action to apply automatically to Table B. This way, there are always the exact same number of records in each table. The only field that is the same amongst the two tables is 'ID.' This field is an autonumber in Table A and a long integer in Table B. Thanks very much in advance! -- Joe |
#2
|
|||
|
|||
from the database window, open the Relationships window (on the menu bar,
Tools | Relationships, or from a button on the toolbar). add the two tables, if they're not already showing in the window. click on the ID field in TableA, and drag/drop it over the ID field in TableB. in the Edit Relationships window, checkmark the Enforce Referential Integrity box, and then checkmark the Cascade Delete Related Records box. save, then close the Relationships window. hth "Joe" wrote in message ... Essentially what I want is simple. I have two different tables. Both store the same records, but each one stores different information about the records. Each time a record is added to or deleted from Table A, I would like the same action to apply automatically to Table B. This way, there are always the exact same number of records in each table. The only field that is the same amongst the two tables is 'ID.' This field is an autonumber in Table A and a long integer in Table B. Thanks very much in advance! -- Joe |
#3
|
|||
|
|||
Thanks, Tina. I think that was a start, but there are still a few things not
working. For one, Table A is the table that I want to be able to add and delete records from. However, after employing your method and attempting to add records to Table A, I got the following error message: "You cannot add or change a records because a related record is required in [Table B]." Also, when I enter records into Table B, they do not automatically update in Table A. Any other suggestions? Joe "tina" wrote: from the database window, open the Relationships window (on the menu bar, Tools | Relationships, or from a button on the toolbar). add the two tables, if they're not already showing in the window. click on the ID field in TableA, and drag/drop it over the ID field in TableB. in the Edit Relationships window, checkmark the Enforce Referential Integrity box, and then checkmark the Cascade Delete Related Records box. save, then close the Relationships window. hth "Joe" wrote in message ... Essentially what I want is simple. I have two different tables. Both store the same records, but each one stores different information about the records. Each time a record is added to or deleted from Table A, I would like the same action to apply automatically to Table B. This way, there are always the exact same number of records in each table. The only field that is the same amongst the two tables is 'ID.' This field is an autonumber in Table A and a long integer in Table B. Thanks very much in advance! -- Joe |
#4
|
|||
|
|||
comments inline.
"Joe" wrote in message ... Thanks, Tina. I think that was a start, but there are still a few things not working. For one, Table A is the table that I want to be able to add and delete records from. However, after employing your method and attempting to add records to Table A, I got the following error message: "You cannot add or change a records because a related record is required in [Table B]." sounds like you did your dragging/dropping backward - TableB to TableA. suggest you delete the relationship and try it again. when the Edit Relationships dialog opens, the Table/Query column should list TableA, and the Related Table/Query column should list TableB. Also, when I enter records into Table B, they do not automatically update in Table A. i don't know what you mean by "automatically update". TableA is the parent table, TableB is the child table. Once you enter a record in TableA, you can enter a record with the same ID in TableB; the record in TableA always has to be created first. also, your original post states that each table "stores different information about the records". i assumed that TableB is used to store additional data about the records in TableA, in either a one-to-many or one-to-one relationship. if, instead, you have data about a record that is duplicated in both tables, then updating the record data in one table will *not* update it in the other table. there is no way to do this automatically in Access (i suspect because data duplication violates data normalization rules). you would have to write code to do it programmatically at, or after, the point of data entry - but i would recommend reviewing your table structure first, to make sure you have a valid reason for breaking data normalization rules. Any other suggestions? if the above comments aren't helpful, suggest you post the structure of your tables (TableName, followed by FieldNames), along with an explanation of what the database is being used for. hth Joe "tina" wrote: from the database window, open the Relationships window (on the menu bar, Tools | Relationships, or from a button on the toolbar). add the two tables, if they're not already showing in the window. click on the ID field in TableA, and drag/drop it over the ID field in TableB. in the Edit Relationships window, checkmark the Enforce Referential Integrity box, and then checkmark the Cascade Delete Related Records box. save, then close the Relationships window. hth "Joe" wrote in message ... Essentially what I want is simple. I have two different tables. Both store the same records, but each one stores different information about the records. Each time a record is added to or deleted from Table A, I would like the same action to apply automatically to Table B. This way, there are always the exact same number of records in each table. The only field that is the same amongst the two tables is 'ID.' This field is an autonumber in Table A and a long integer in Table B. Thanks very much in advance! -- Joe |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Number of tables related in a query | Marion | Running & Setting Up Queries | 2 | March 25th, 2005 02:13 PM |
Understanding Primary Keys | Khai | Database Design | 3 | January 20th, 2005 09:47 PM |
Show Null as well in a query of related tables...? | Offace | General Discussion | 2 | September 21st, 2004 02:25 AM |
Moving Multiple Related Records | Tony | Running & Setting Up Queries | 1 | June 23rd, 2004 04:14 PM |
Import related tables from Access into Contacts | mima | Contacts | 2 | June 7th, 2004 10:49 PM |