Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Best way to setup tables for multible related data sets

Status
Not open for further replies.
puforee

puforee

Technical User
Oct 6, 2006
741
US
I have been asked to set up a DB that shows employees and their managers. This will requires all managers in the management chain to be included. There
are 6 layers from the employee to the top. The data available includes: employee ID, employee name, managers ID. This data is consistent through all levels of employment.

How best to set this up is bothering me. I need to set it up so if any employee/manager moves position, or leaves, the relationships will update automatically.

So, if a 2nd level manager leaves I should be able to replace his name and ID and all relationships are updated. The complication could grow when employees/managers advance in their positions.

Does anyone have any ideas on how to set this up. Tables - 1 0r more? Type of queries?

Any help will be appreciated.

Thanks,

John
 
Sort by date Sort by votes
I would consider a table of all employees and a table of positions. Each employee has a position as well as a position they report to.

[pre]tblEmployees
======================
empEmpID primary key
empFirstName
empLastName
empPosID link to tblPositions for this employee
empSupPosID link to tblPositions for supervisor



tblPositions
=======================
posPosID primary key
posTitle[/pre]

Another possible solution would have

[pre]tblEmployees
======================
empEmpID primary key
empFirstName
empLastName
empPosID link to tblPositions for this employee

tblPositions
=======================
posPosID primary key
posTitle
posReportsPosID[/pre]





Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MrStohler
  • Locked
  • Question
Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1
Replies
5
Views
706
SkipVought
S
JayRo71
  • Locked
  • Question
Importing only unique records from excel tables 1
Replies
10
Views
939
SkipVought
S
Anthony343242
  • Locked
  • Question
MS ACCESS UNION ALL with Attachment data type 1
2
Replies
29
Views
7K
Anthony343242
Anthony343242
Lidias13
  • Locked
  • Question
Odata contains fields from multiple tables in single table with different field names - for same
Replies
1
Views
537
dhookom
dhookom
ameedoo3000
  • Locked
  • Question
Send a message to an email
Replies
2
Views
4K
ameedoo3000
ameedoo3000

Part and Inventory Search

Sponsor

Back
Top