0

This is in relation to an Access Database. I have a first table that lists employees with a primary key of id. In the first table, I include all employees (whether overlords or underlings). I have a second table that identifies approvals of expenses. I have one column that is a foreign key referencing the underling that made the expense. I have a second column in the same table that is a foreign key referencing the overlord that approved the expense. When creating a query, the query breaks because of the dual relationship. Could someone help me with some code to put in the query to lookup the overlord from the employees table based on the overlord id? Thanks.

blainedwards8
  • 57
  • 1
  • 7

2 Answers2

0

I think I have it solved using the DLookUp Function. It looks something like this:

Overlord: DLookUp("[employee_name]", "employees", "ID = " & overlord_id)

I would still like to know if there is a way of doing this just with relationships.

blainedwards8
  • 57
  • 1
  • 7
  • copy the employee table into the query twice - once linked to the underling and once to the overlord. (BTW - I like your description of the problem) – Don George Dec 08 '16 at 21:03
0

Another way would be to create a 3rd, associative table (ie: tblExpensePeople). Each row of the 3rd table would have a DB generated ID for Primary Key, and the ID of the expense (FK of Expense table), a Person ID (FK of the People table), and Type of Person (ie: Overlord or Underling or Wantabe lol ...maybe in the future, She Who Pays the Bills ;). The Type of Person could be a validated against a list or a foreign key from data in another separate table. This solves query issues, and opens up to store other info important to "when" the expense is pushed around, like a single date field that, depending on the Type of Person, would indicate when the expense was submitted / when the expense was approved / when the expense was submitted for payment). This design also inherently keeps people info separate from expense info.

user21415117
  • 1