Append or Insert Command on Form for multiple table entries

[Steven.Allman]

I have 4 tables linked by a single Employee ID Number.

Table1.EmployeeID
Table2.EmployeeID
Table3.EmployeeID
Table4.EmployeeID



I have a form that I use for data input into Table1. I want to add a simple code after I enter the EmployeeID to automatically append the EmployeeID to table2 table3 and table4. But nothing is working.
I went with a simple DoCMD.RunSQL insert... tried an append just using table2 and a where statement in my sentax for current record, everything. It seems this is super super simple but most the questions on appends are moving massive amounts of data, dozens of fields, and archiving entire tables.
I just want me to put EmployeeID 33342332 and BOOM, 33342332 is added to my other 3 tables. Cannot be that darned hard, right?

[Bob Fitz]

Why do you need to do this

[Steven.Allman]

Because I am tracking about 3000 data points and I really dont want 3000 columns in my one table. THe DB is split across the network, and for certain departments to have access to the data they need to edit, etc. No one person except for me and my supervisor have admin rights to each of the tables.
I guess the why doesnt seem like a relevant question, so far as to how.

[Steven.Allman]

I have a table for personnel actions... Well, the person in charge of inputting and verifying they had attended a safety training doesnt need to have access to their personnel file to see if there was adverse actions or see there pay rate..

[Bob Fitz]

Because I am tracking about 3000 data points and I really dont want 3000 columns in my one table. THe DB is split across the network, and for certain departments to have access to the data they need to edit, etc. No one person except for me and my supervisor have admin rights to each of the tables.
I guess the why doesnt seem like a relevant question, so far as to how.

I can't imagine any normalized db having 3000 fields. I could be wrong but AFAIK the limit in Access is two hundred and fifty something fields per table. In the thirty or so years that I have dabbled with Access Iv'e never come across a db that had anywhere near that number. Not even one that got even remotely close to one hundred fields.

I don't understand what the db is supposed to do but IMHO there's something wrong if you need 3000 fields in one table. If that is the case then I would urge you to read up on Database Design and Normalization.

[isladogs]

The maximum number of fields allowed in a table is 255 and only a poorly designed database would ever have tables with anything approaching that limit.
You should never have multiple tables with the same structure.

To me, it sounds like you may be trying to use Access like an Excel spreadsheet.
Whilst possible, that's not how to use Access efficiently.

[Steven.Allman]

Jesus H Christ....
My wife asks me how much golf costs and I reply $4,566,398. Just a random number. I think I explained why in the other responses. Not really sure why the question is asked to begin with. Multiple tables with a joined key field, is that not NORMALIZED db design?

This forum has helped me thousands of times.... wait, sorry, its helped me 23 times, and never got so much grief over my questions.

Again, split database, 4 tables, tables in different network locations with limited access depending on user. No one has rights to all the data but myself and one other person.
So, I want the HR folks to worry about Table2 (also not its real name) and I need the training folks to worry about table3 (also not its real name).
So when I put someone in table1 (guess what, not its real name) I want to automatically just append the EmployeeID number, and nothing else, a single field, into table 2, 3 and 4.

So if there is anyone with an answer to that question, which considering the complexity of things others have helped me out with before, this is very very minor, I would appreciate it.

Form1 fieldID employeeID onupdate append/insert employeeID table2 and table3 and table4.

[isladogs]

That's fine. I'll drop out of this thread now