How can I update 2 tables by the criteria of a field in another?

[Guest]

Okay, I have one table that everyone can enter company information into for
clients, contractors, suppliers, etc... However, I also have individual
tables for each which are then linked to a table of primary contacts for
those companies. Here's my problem. I need the "Architects" table and the
"Contractors" table to update automatically when information is entered into
the "All Companies" table based on the information entered into the
"Catagory" field.
Ex of Records: 123 Architecture 1234 Oaks. Ln. New York, NY 123-123-1234
Architect
Constructions R' Us 456 Street Denver, CO
789-789-7899 Contractor

The top one would have to be added to the "Architects" table and the second
one to the "Contractors" table. I hope this makes sense. Anyone have any
ideas on how I can make this happen?

 

[Guest]

Greetings Admin Assist,
Just for the record in general you do not want duplicate data in your
system... With that said, if you have not already, you will need to provide
your users with a form to enter in this new data. When the users click the
save button you will then have it send the data to the multiple tables. It
sounds like you will need to provide the user with a way to select which
"secondary" table will receive the duplicate data. All of this is going to
require some vba code writing on your part. I am assuming that you are new
to this based on the question and if I am way off here please forgive me for
my misunderstanding. If I am correct and if you would like help with this I
would be more than happy to help you. I enjoy it! Take Care & God Bless ~

 

[Guest]

Is there a reason for having redundent information in multiple tables? If
not, then you could create an unbound form and based on a field in the form
you would run an append query to add it to the appropriate table.

 

[John Spencer]

Why? As in you already know from the last field in your sample records that
the record in the table is an Architect or a Contractor. It usually makes
little sense to have a table named after one or the other. Are you storing
different types of data in the Architect table versus the Contractor Table?

At most, I would expect to see a primary key field in the All Companies
table reflected in the other tables.

If you are stuck with this design, then you are going to need vba code to
run SQL queries when the record is saved from the form. That code would
need to handle adding records, updating records, and deleting records.