Best Way to Reference Same Table from Multiple Fields?

[BobBob]

Hello All,

Fairly new to Access, so please forgive me if my terminology is slightly off. As part of a database I am creating (XP, Access 2007), I have set up two tables: one contains employee information, while the other contains position information. The employee table holds fields including first name, last name, email, etc. and uses an Employee ID as its primary key. The position table holds information including department, salary, title, etc. and uses a Position ID as its primary key.

The position table also contains three fields on employees relevant to the position: Holder (the actual employee filling the position), Supervisor (The position's supervisor), and Previous Holder (The employee who held the position last). The first of these fields is required, while the latter two are not. The problem I have, however, is that all three of these fields in the Position table reference the same Employee Table, as they all use Employee ID as their foreign key (in addition, the same employee can fill different fields in different records; ex. a holder of one position may be a supervisor of another).

When I build queries around these tables, I have to continually rebuild the relationship structure by creating three iterations of the employee table in each query and redeclaring the proper relationship between them and Position Table. Is there a way I might set this relationship in the relationships tab so that Access recognizes it when I create new queries? Thank you!

 

[Gideon1973]

The setting up of relationships has two main purposes: enforcement of referential integrity (i.e., don't put values in a child table that isn't in the parent) and automatic "auto-linking" of tables by the related fields when building a query.

Rather than recreating the employee tables each time, what about creating three different queries with the types of joins/relationships you want -- one for each position type of "Holder", "Supervisor" and "Previous Holder"? Then in your query, you reference the query object you're interested in at that time and the relationships are already created.

Not sure if this answers your question, but hopefully this makes sense...

 

[BobBob]

The setting up of relationships has two main purposes: enforcement of referential integrity (i.e., don't put values in a child table that isn't in the parent) and automatic "auto-linking" of tables by the related fields when building a query.

Rather than recreating the employee tables each time, what about creating three different queries with the types of joins/relationships you want -- one for each position type of "Holder", "Supervisor" and "Previous Holder"? Then in your query, you reference the query object you're interested in at that time and the relationships are already created.

Not sure if this answers your question, but hopefully this makes sense...

Actually, this works perfectly. Thanks! Didn't think to use queries in this way.

 

[Gideon1973]

Actually, this works perfectly. Thanks! Didn't think to use queries in this way.

You're welcome! That approach (referencing a saved query in another query) is a very handy way to handle more complex types of queries. It helps with isolating various levels of complexity and breaks down a complex process into smaller units of work. Depending on how much data crunching you are doing, you may take a performance hit and may need to materialize the data into a physical table first, then query off of that. However, that's only when you are dealing with a few 100,000 rows or more in each sub-query.

In larger DBMSs such as SQL Server or Oracle, the idea of "saved" queries are called views: View (database) - Wikipedia, the free encyclopedia. They are also sometimes referred to as "sub-queries" if they are written in-line directly within the SQL itself.

Glad it worked out for you!

 

[HiTechCoach]

When I build queries around these tables, I have to continually rebuild the relationship structure by creating three iterations of the employee table in each query and redeclaring the proper relationship between them and Position Table. Is there a way I might set this relationship in the relationships tab so that Access recognizes it when I create new queries?

A better solution would be to modify your table design following the rules of normalization.

The problem I have, however, is that all three of these fields in the Position table reference the same Employee Table,

A major design issue is that you have repeating fields in the position table. The three fields that are foreign keys to the Employee Table should each be in separate records. Then yoiu need only one relationship between the tables.

With the exception of a Junction table to create self joins, whenever I see a table needing two or more relationships set to another table, like Position with Employee, there is design issues with repeating fields.

Getting your tables properly normalized is key to the success for any Database application. This is not limited to Access. It is true with all databases. A properly normalized/designed database will make everything else easier.

 

[Gideon1973]

A better solution would be to modify your table design following the rules of normalization.

Getting your tables properly normalized is key to the success for any Database application. This is not limited to Access. It is true with all databases. A properly normalized/designed database will make everything else easier.

Yes, that's a great point. When I proposed the saved query approach, I didn't dive into the table structure as you did. Something seemed "off" about the way the table was structured and seems like it needs some type of intermediary or junction table. My approach was a bit of a "hack" to get the OP going, but yes, I do agree that a more indepth review and restructuring of the base table(s) are needed since he will likely run into other problems down the line...

 

[HiTechCoach]

... since he will likely run into other problems down the line...

I totally agree.

It is better to fix it now. That way there is less to update.

 

[BobBob]

I totally agree.

It is better to fix it now. That way there is less to update.

Is there a normalization issue with the tables? Which normalization form is this table violating? I don't believe I would be able to put each of the foreign key references in different Position records, as all three references (Holder, Supervisor, Prior Holder) are all unique to each position code. Seperating the Employee table into three tables would not work either, as far as I can tell, as "Holder" employee records can (and often are) referenced as "Supervisors" in different Position records. The query method outlined above has been working just fine, but I agree, if this is going to cause problems down the line I'd like to nip this in the bud.

Thanks!

 

[HiTechCoach]

Is there a normalization issue with the tables? Which normalization form is this table violating?

The Position table has repeating fields. That violates NF1.

"The position table also contains three fields on employees relevant to the position: Holder (the actual employee filling the position), Supervisor (The position's supervisor), and Previous Holder (The employee who held the position last)."

The Position table needs a child table that holds one record for each employee "Position".

Example Table:
PositionEmployees
- PositionEmployessKey (primary Key autonumber)
- PositionKey (long -foreign key to Poisiton table)
- PositionRoleType (long -foreign key to Lookup table for Role - Holder, Supervisor, etc)
- PositionEmployeeKey (long - foreign key to Employee Table)
- PositionStartDate
- PositionEndDate

I would use the End Date to calculate if is is the previous employee in the role.

The PositionEmployees is a junction table between Positions and Employees. This design would allow you to track as many previous employees and supervisors as needed.

With this recommended design it would also be very easy to add a sub form to the Employee form to show all there Roles. With you original design this would be a lot more difficult to do.

Seperating the Employee table into three tables would not work either

The employee table is fine. I was not referring to this table. I like to use a table named People in my apps that hold all people. Employees, customers, vendors, etc. all in a single table.