Passing data between tables.

[southwestgooner]

Hi,
I am currently working on a split database. The Back_End contains all the tables. The Front_End contains the queries, forms, reports, modules, macros etc, etc.

I have a table named tbl_ECNList this contains fields named OriginatorID, FullName & EmailAddress.
I have another table named tbl_Employee which also contains fields named OriginatorID, FullName & EmailAddress.

I have a relationship set up between the OriginatorID fields of the two tables.
I want to automatically take the data from the FullName and EmailAddress fields from the tbl_Employee back to the tbl_ECNList when the OriginatorID is entered in the tbl_ECNList.

Can anyone help me resolve this problem ?
Do I need to add more relationships as it's not populating the Employee & EmailAddress fields at the moment !

Many thanks in anticipation of a satisfactory resolution.

Regards,
Steve.

 

[PaulO]

Just to clarify, you want to copy the record containing the three fields into the ECN table once they have been added to the to Employee table?

Might first be worth taking a step back from this and consider why you need TWO tables recording exactly the same data ... not a particularly data-efficient objective.

If, for example, the ECN table contains records other than Employees why not just add an additional field into that table to indicate what TYPE of record is being stored i.e. Employee, Customer, Supplier? Then, you could add your Employee records directly into that table.

 

[southwestgooner]

Just to clarify, you want to copy the record containing the three fields into the ECN table once they have been added to the to Employee table?

Might first be worth taking a step back from this and consider why you need TWO tables recording exactly the same data ... not a particularly data-efficient objective.

If, for example, the ECN table contains records other than Employees why not just add an additional field into that table to indicate what TYPE of record is being stored i.e. Employee, Customer, Supplier? Then, you could add your Employee records directly into that table.

Hi,
The tbl_Employee contains 3 fields OriginatorID, FullName & EmailAddress. This table is populated with a list of employee names & email accounts.
The tbl_ECNList contains many fields that aren't relative to my problem. I have a relationship between the two OriginatorID fields of the two tables. I want to use the FullName & EmailAddress data from the tbl_Employee from the OriginatorID of the person who creates the record in the tbl_ECNList.
The FullName & EmailAddress data is required in a automated email created from a form.

I'll bet that was as clear as mud !!

Cheers,
Steve.

 

[PaulO]

OK ... just explain to me what the Originator ID is? Is it a unique code assigned to an Employee?

It may also help to understand what the ECN table is.

 

[southwestgooner]

OK ... just explain to me what the Originator ID is? Is it a unique code assigned to an Employee?

It may also help to understand what the ECN table is.

Hi,
Yes its unique in the tbl_Employee assigned to an employee and is added into the tbl_ECNLIst when that employee creates a record.

 

[PaulO]

And can the employee create more than one record in the ECN table?

 

[PaulO]

OK ... so you need to create an UPDATE query where the two tables are linked via Originator ID fields

You will UPDATE the blank FullName & EmailAddress fields in the ECN table with the contents of the FullName & EmailAddress fields held in the Employee table.

The query could be added to a Command Button, so that you can run the routine at any time.

 

[southwestgooner]

And can the employee create more than one record in the ECN table?

Yes an employee can create multiple records.
I have also removed the FullName & EmailAddress fields from the tbl_ECNList as they are duplicated and are required in the tbl_Employee.