Related Records and Table Occurences
I have four tables - a Projects table, a Jobs table, a Contacts table, and a Timesheets table. In the Jobs table I have a _ProjectID key field which is my related field to the primary key field ProjectID from my Projects table. This works great so I can use the ProjectID relationship to fill in the ProjectName, ProjectDescription, etc fields in my Job table via this relationship.
In my Timesheets table, I have a _JobID key field which is related to the primary key field JobID from my Jobs table. This is also great as I can pull JobName, JobDescription, and even ProjectName, and ProjectDescription from the Jobs and Projects table via this relationship (and the Jobs-Projects relationship).
However, I also have a key field in my Timesheets table called _ContactID, that I want to pull a Contact's ID and have it fill in the Contact's name and other info in other tables. I created a Value List that has the ContactID's in it, and I set up my other fields to pull further data from the Contacts table. However, even though the Value list worked, and I can have it display my ContactID, I can't get the other fields to fill in.
I tried solving this by creating a second table occurrence called Timsheets2 and a field _ContactID. I then related this field to the primary key field ContactID from the Contacts table. However, this didn't work either (it didn't fill in additional contact information in related fields when I selected the ContactID from the Value List pop-up field). Basically I want to have two tables that aren't related to each other - relate to my Timesheets table.
So am I doing this wrong? Do I not understand how table occurrences work? Is there a solution to my problem? Thanks.