Self populating Table from another table

[tlyons]

Alright, so this might be stupid question but I can't seem to wrap my mind around how to accomplish this in Access. Any advice is much appreciated.



I have two tables. One is Employees and the other is Top Positions. In the employee table I include all information about employees (general as well as resume info) and in the Top Positions table I've included all information about the position as well as some information about the current employee in the position (resume info).

When an employee is inputted into the Top Positions table I want their resume info to automatically stream from their employee profile to the resume section in the Top Positions table.

The problem is that not all employees will be occupying a top position. Please let me know if this is possible and how it could be done.
Thank you!

[jzwp11]

Welcome to the forum!

You may need a third table, but in order to determine that we need to know more about your application.

Can a top position apply to more than one person over time?
Can a person have many different top positions over time?

When an employee is inputted into the Top Positions table I want their resume info to automatically stream from their employee profile to the resume section in the Top Positions table.

In a relational database, there is no need to copy or "stream" information from one table to another. You would build a query between the related tables to bring the information together.

[tlyons]

Yes, basically this database is going to act as a succession plan for the top 100 positions in the company. Multiple employees will be occupying the same position over time. And employees can be promoted and demoted and move through the positions.

The reason that I have the redundency in the tables is so that reports can be printed off into two books both would hold resume information.

[jzwp11]

Based on your description, you need three tables

tblEmployees
-pkEmployeeID primary key, autonumber
-txtFName
-txtLName
other field related strictly to the employee

tblTopPositions
-pkTPositionID primary key, autonumber
-txtPositionName

other fields only related to the position


Now the third table to join the employee with their position(s)

tblEmployeePositions
-pkEmpPositionID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees
-fkTPositionID foreign key to tblTopPositions
-dteEffective (effective date when the employee assumed the position)

The reason that I have the redundency in the tables is so that reports can be printed off into two books both would hold resume information.

No need for redundancy, you would create a query and base your report on the query. Having redundancy can lead to data integrity issues!

[tlyons]

Hmmm, alright. Thanks! I'll give it a go and see how it works. The problem, I think, is the way I've set up my reports. They are spaced to look like print versions of my forms so that the whole database can be viewed as a physical book.

[jzwp11]

The table structure in a relational database is of the utmost importance for proper functioning. The forms and reports should be based on the correct table structure; the table structure should not be based on the forms and reports desired.

[tlyons]

Alright, I think I'm going to need to rebuild the DB from the ground up. It started as a simple version of a physical book but a lot more is being demanded of it and I'm not sure I have the knowledge to keep up. Thanks for your help!

[jzwp11]

We're here to help if you need it! Best of luck.