append records from 1 table to 2 tables in 1 query

[Guest]

When a new employee record is created I would like to append records to three
different tables. Right now the form simply appends to the one table. I was
trying to use an APPEND Query on the ON CLOSE that appends the same fields to
2 tables but I can't figure it out. Specifically I want to append SSN, Last
Name and First Name to 2 additional tables at the same time all the other
data is being appended to the Employee table.

 

[Van T. Dinh]

It sounds to me that the Table Structure of your database is incorrect since
you are trying to store redundant data. Fields like LastName, FirstName
should not be stored in multiple Tables. They should only be stored in ONE
Table only that represent the entity "Person" in the database and other
Tables should only store the PrimaryKey Field value (possibly the SSN) from
the Table "tblPerson". This way, if a Person changes his / her name by deed
poll (or whatever equivalent in your area), LastName and FirstName need to
be changed in ONE Table, NOT multiple Table.

Suggest you check out Relational Database Design Theory and modify your
Table Structure accordingly before worrying about the Form.

 

[Guest]

Thanks for replying. The SSN is the primary key so it is in several tables
to get rid of redundancy. However when a new employee is added that key
needs to be appended to the two additional tables so that when those tables
are used the record will already be there.

 

[Van T. Dinh]

You wrote:

"Specifically I want to append SSN, Last Name and First Name to 2 additional
tables ..."

and I am fairly sure that [Last Name] and [First Name] are redundant???

While it is possible to add "nearly-blank" Records (with just the linking
Field value), I have never seen any need for Records to be created BEFORE
there are actually data items to be stored. That said, you can use the
OpenQuery Method to execute a saved Append Query or RunSQL Method to execute
an "Insert Into ..." SQL String. Alternatively, you can use the Execute
Method of the Database Object.

Check Access VB Help on OpenQuery, RunSQL and Execute.