I have an employee database that has three different types of employees (officials, contractors, transcriptionists) with a separate table and form for each type of employee. On my "Officials" form, I have a checkbox that I can check to let me know if an official is also on the transcriptionist list. My goal is to not have to type in all of the officials and contractors who are also transcriptionists all over again. I want to be able to check that box on the "officials" form and the contact information will automatically be copied to the "transcriptionist" form and table. I'm sure this can be done, but I'm kind of new at this and don't have a clue how to make it happen.......HELP!!

Thanks in advance.

You have a normalisation problem (your tables are not set up correctly...

The 3 types of employees belong in 1 table.

If the data is the same for all employees (and I assume it is) then you only want one table for employees and a related table that lists their type of employement(s).

tblEmployees
EmployeeID (PK and auto)
LastName
FirstName
...etc...

tblPositionsLookup
PositionsID (PK and auto)
PositionName

tblEmployeePositions
EmployeePositions (PK and auto)
EmployeeID (FK)
PositionsID (FK)

hth,
Jack

Hiya Danny -

Ya beat me by minutes...

Jack

I'm not sure I explained my situation correctly....

The officials are my only employees, however I manage a contractor list as well as a transcriptionist list. I have them all in one database, but for the most part the three are not related. My "officials" table has a lot more in depth information than the other two. The other two only contain basic contact information that about 2000 other people have access to. I do not send out the "officials" information. It has a lot of personal information that I don't want people having access to. I actually have the "officials" tables and forms password protected.

That being said, some of my official reporters do work as transcriptionists on the side, so there is certain contact information that I would like to send over to the "transcriptionists" form when I click the checkbox.

I hope I'm making sense.

It sounds like Contractors and Transcriiptionists should be in the same table, not separate tables. If your data is properly normailzed, as suggested by Danny Seager, then you should be able to have a subform on the transcriptionist form that shows the related data that you want. A boolean field in the table of Contractors and Transcriptionists should work as a trigger to show or hide a subform on the Transcriptionist form.

hth,
Jack

Jack... my answer might have been first but yours was of more substance.

You were missed when you were not on UA last week... I saw 2 threads wondering where you had gone.

Danny -

Thank you for your kind words! I have been up to my eyebrows in a complex db and that is why I have not been around very much lately. I still have a long way to go on the db, but my brain is porridge so I have been taking some time off, now and again, and dropping in for a visit and to rest up the muddled synapses...

Jack

Any time Jack...

yeah dont you love the way that database logic prevents your brain from functioning on anything else whilst trying to sort out the details...

I'm looking at getting another assistant so I can hopefully spread the workload more... just gotta find the right 18 year old female candidate (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)

Naughty naughty... Just pick the best looking applicant and then your can train her if she has no skills... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/grinhalo.gif)

Jack... That's the sort of idea that'll get me single..

I've not got time to train anyone really so I'm looking for someone that know's their way round SQL server, Access and ASP...

You dont fancy relocating do you (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif) You must be getting sick of all that gorgeous weather by now (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)

Don't tell!!!

I would love to relocate but the last time I was there King Charles banned me to America for life....

BUT! What about the Vino? Couldn,t resist!

Edited by: jmcwk on Tue May 24 18:42:14 EDT 2005.

Thanks for the info, guys. I know where to go with the contractors and transcriptionists tables and forms....but how can I tie in the fact that some officials' contact information needs to be on the transcriptionist table? I'm still a little confused as to the best way to go about that since they won't be on the same table.

Could I use an update query to accomplish this task?

Remember, I'm learning. Hope I'm not being annoying.

I wish someone would ban me to america for life (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)

LMAO!!!

If I had not been banned I would have the wine shipped to me, though some of the good California wines are readily available in the UK... If Betty (the current queen) lifts the ban I will get on the first ship loaded with California wines and head back....

Jack

Why anyone would give up life in california for drizzly bleak england I could never guess...

You are not annoying as everyone of us has been where you are....

You do not put the data from the 'Officials' table into the other tables because that would cause redundant information and that is something that is a big no-no in a relational database. What you do is 'relate' the tables and how best to do that I cannot say as I have not seen your structure. My guess would be that you need a junction table so that you can join the Official table and the other tables. This way you can get the data from the Officials table and show it on the form that has data from the other table with Contractors and Transcriptionists. The junction table might look like this:

tblOfficialsAndOthers
OfficialAndOthersID (PK and auto)
OfficialID (FK)
ContractorTranscriptionistID (FK)

hth,
Jack

It is very photogenic there on the 3 days that the sun shines...

On second thought maybe I will just hang out here in the spectaular Bay Area...

Will work on it. Thanks for the advice.