Related Tables

[Joe]

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

 

[tina]

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]

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]

comments inline.

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