Extracting data from one field in a table to another--Help!!!

[zaf786]

Hi Guys,

I have a table called "tblEmployees" with the following fields:
EmployeeNo (Primary key)
Name
Address
DOB etc
Letter1Dated
Letter2Dated

I have another couple of tables:

"tblLetter1" with the following fields:
EmployeeNo (Primary key)
Name
Letter1Dated
Letter1RemainingFields...

"tblLetter2" with the following fields:
EmployeeNo (Primary key)
Name
Letter2Dated
Letter2RemainingFields...

I want to be able to put the data from 'Letter1Dated' field (from the "tblLetter1" table) into the 'Letter1Dated' Field in the "tblEmployees" table.
Likewise, I want to be able to put the data from 'Letter2Dated' field (from the "tblLetter2" table) into the 'Letter2Dated' Field in the "tblEmployees" table.

All three tables have the same primary key (EmployeeNo), which makes it even more difficult to do this!
I understand this may sound abit confusing so i apologise for this.

I would really appreciate it if anyone could point me in the right direction please.

Thanks alot.

 

[Malcy]

Hi
I think you have some structure problems
Your central table should be tblEmployees but you have got too much duplicating data. Why not consider keeping all the data relating to the employees in this table and then have another table called tblLetters where you store the employee number, a field for the letter type and then the various other fields.
This way employee 99 can, if necessary get two different copies of letter 1 over time. In your setup this could not happen.

This is a very quick response but might stimulate a productive vein of thought for you
Good luck

 

[neileg]

Wrong approach.

First, Letter1 and Letter2 should be in the same table with a field to indicate which type they are. Next, EmployeeNo is not the primary key for the letter table, it is a foreign key and is used to link the letter records with the relevant employee record. Create a new primary key, probably LetterID and set it as an autonumber. Now you need to remove the Letter1Dated and Letter2Dated fields from tblEmployees.

Construct a query with tblEmployees and the new letter table. Join the two tables on EmployeeID. Drag the fields you want to see into the query grid.

When you've done that, explain what else you need and we'll try and help.

Edit: Malcy, stop duplicating my advice!

 

[Malcy]

Good to know that if great minds think alike then just perhaps I am approaching greatness!

 

[comptechbranden]

once you get your data working great and in one location you can use a subform and do a bunch of stuff to pull data of employees to other forms using a ID # or something unique like first initial last name...

Im not much help but this is what I did to get autopopulation going on!