Automatically add new info?

[J]

Hi there -
I have a relational database with 10 tables. They all have
the same primary key called, "Agency". (all unique names)
For example, one table holds the agency's mailing address,
another will hold the agency's revenue information, etc
etc.

There is a few people who will be responsible for data
entry, other than myself.

When one of these people add a new Agency name to one
table, it doesn't automatically populate the other 9
tables with the new addition. (FYI - people will add new
agencies to only one of the 9 tables via a form)

I'm wondering if this is possible somehow.

Referencial entegrity has been enforced, along
with 'Cascade Update' and 'Cascade Delete'.

Many thanks!
J.

 

[John Vinson]

Hi there -
I have a relational database with 10 tables. They all have
the same primary key called, "Agency". (all unique names)
For example, one table holds the agency's mailing address,
another will hold the agency's revenue information, etc
etc.

This is VERY VERY unlikely to be properly designed. One to one
relationships, like this, are very rare and typically used only for
special conditions (such as secure and unsecure fields, or
Subclassing).

If each agency has only one mailing address, one would typically just
have fields (Address1, Address2, City, State, Postcode) in the Agency
table. If the agency might have multiple addresses you would have a
one-to-many link to an Addresses table, which would have its own
primary key and a separate Agency field as a foreign key. Revenue
information would also typically be in a "many" side table - surely
you want to keep more than *one single value* of revenue for an
agency!

There is a few people who will be responsible for data
entry, other than myself.

When one of these people add a new Agency name to one
table, it doesn't automatically populate the other 9
tables with the new addition. (FYI - people will add new
agencies to only one of the 9 tables via a form)

That's correct. It should not do so; creating empty "placeholder"
records is never a good idea and is essentially never necessary.

I'm wondering if this is possible somehow.

Referencial entegrity has been enforced, along
with 'Cascade Update' and 'Cascade Delete'.

The typical approach is to use a Form (for Agency) with Subforms for
the related tables (whether one to many or, rarely, one to one); the
Subform control has a "master/child link field" feature which keeps
the related tables in synch.