Enter data in two tables at the same time

[puforee]

I have a DB that has two tables. One "Supervisor" the other "Employee". I use the supervisor data for a dropdown select box on the employees table to identify the employees supervisor

When I enter a supervisors name into the supervisor table, using a form...how can I have the same name added to the Employees table as an employee? After all, my supervisor are employees also.

Thanks,

 

[dhookom]

After all, my supervisor are employees also

Then why not have just one table? Most applications I have seen like this use a single employee table.

Duane
Hook'D on Access
MS Access MVP

 

[mkallover]

Yeah, why not just put them on one table and add an additional True/False column to designate someone as a Supervisor?

Then you could run your query for your drop down off of that.

 

[puforee]

mkallover Thanks for the feed back...here is my reasoning.

Because, part of the employee table is a field that indicates their supervior. To keep data accuracy I fill in that field from a combo box that has a list of supervisors from the supervisor table. I also have many other tables, queries, reports and a lot of them depend on the supervisor table. I could filter the employee table for employees that are supervisors but there are many type of supervisor. This would make the query cumbersom.

 

[dhookom]

If the structures of your employee and supervisor table are significantly different, you could create a supervisor table with the primary key from the employee table and any other fields you might think you need. Bottom line is I would not have First Name and Last Name in two different tables like you seem to be promoting. There is nothing cumbersome about this unless you have millions of employees that aren't supervisors.

BTW: How are the tables different?

Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

It's a big no-no to duplicate data in multiple tables, it breaks normalization rules.

This is actually the ultimate "textbook example", practically every database book I've ever seen uses the Employee-Supervisor relationship as an example of a table where you might want queries with self-joins. So you should be able to find plenty of info out there about the right way to set this up.

I don't see a reason why this would make your queries especially complex. If there are certain business rules because of the "many types" of supervisors, that complexity is going to be there whether or not you include supervisors within the Employees table. It sounds like you will need some sort of SupervisorTypes or SupervisorAttributes table, and that will be the source of the complexity rather than containing all the "employee-related" information about the supervisors solely in the Employees table.

 

[DavidSoussan]

What you have here is a typical Hierarchical Data problem and there are 2 established solutions to this: The Adjacency List Model and The Nested Set Model. Here are 2 articles that fully explain these models and provide you with all of the queries you need to implement them:

http://articles.sitepoint.com/article/hierarchical-data-database

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Now fortunately for you the adjacency list is all you need: nested sets are complex to implement in Access. Both models also mean that supervisors can have their own supervisors or managers as well; which is what really happens in most organisations.

So the employees table has 2 id columns: EmployeeID and ParentID. You store the EmployeeID of each employees' supervisor in the ParentID column, and when it is zero then this is a supervisor. You use a self join to find out the employees that 'belong' to each supervisor and vice versa.

The fields that are only relevant to supervisors are stored in the extra Supervisors table; and an INNER JOIN between the 2 tables automatically returns only supervisors. One interesting feature of Access is that you can use a query with an INNER JOIN between the 2 tables for your AddSupervisor form and the primary key in the Supervisors table will automatically be populated with the EmployeeID autonumber as the new employee and supervisor records are inserted.