Simple 4 tables all linked together

[Kevin_Moreau]

Hello All,

I have a simple task but seems to have some trouble to make it work. I have 4 tables

1. Employees
2. Phone number
3. Inventory
4. Worksite

1.1 Employees table : I want to see and change which phone number (table 2) and which device (table 3) each employee has.

2.1 Phone Number table : I want to see and edit which employee and devices is linked to the phone number and also be able to change it in this table as well. So if I change the employee from a phone number in this table, the phone number in the employee table would change as well.

3.1 Inventory table : I want to be able to see and edit what number and which employee or worksite this number is assigned to.

4.1 Worksite table : I want to see and edit which device and phone number are assigned to each worksite

The problem I have right now is that if I use Inventory table and use a linked field for the 3 other tables, it works great. Table 3 Inventory is linked to all the other tables

Example : iPhone 8 is linked to John, Phone number: 111-222-3333 and worksite Newyork Condo
When I go in the worksite table, I see that it is linked to iPhone 8 but it does not link to the phone number or the employee. The solution is to show a lookup field. The issue is that the lookup field can’t be edited directly from the worksite table (I need to click on it to go to the primary field and then change it there)

How can I make the 4 tables directly linked without the lookup field?

[Kevin_Moreau]

Hi all, I am still trying to figure out the best way to go about this workspace. I have worked on it for quite some time and was able to almost get what I need. I will try to reduce the number of tables to 3 + 1 junction table.

Basically I will have three main tables

1. Employees

2. Inventory

3. Cell phone numbers

and then a junction table (4.)

I want to use the junction table to assign / link each table together. I assign a phone number and an iPhone to an employee with a date. In the future if the employee is assigned a new iPhone, I want to see in the employee table only the latest iPhone for that employee. Then the old iPhone can be assign to another employee or be a backup at the office. 

I have attached a sketch of what I want to achieve. I was able to get the latest date for each items in each tables using a lookup field but I am having trouble to get the result I want for all the field in each of the 3 tables.