Show non-related records
I'm having a tough time trying to figure this one out and I sure hope I can accurately describe what I'm trying to do:
I have a number of employees and we have a number of internal websites, each user has access to some of the websites depending on their seniority. For example the employee we just hired in the mailroom can access a couple of basic sites, each manager can access the basic sites and those related to their department and the owner has access to everything.
I have three tables in a database, Employees holds employee information (a record for each employee) and Sites contains website information (a record for each website). To connect the two together I've created a third table Conector, it contain only two fields, an employee record Id (EMPrecid) and a site record Id (SITErecid). To assign a site to an employee I simply create a new record in the Conector table, enter the employee's Erecid in the EMPrecid field and the STrecid in the SITErecid field. Using relationships to link the three together I simply have to use a portal in the Employees table to list all the sites the employee has access to. I can do the same thing in the Sites table to see who has access to each site. This all seems to work great, it's very easy to maintain the information in both the Employees and Sites tables.
Now I'm I'd like to take it a step further, I'd like to list the sites the employee doesn't currently have access to. This would make it much easier to reassign and remove site for employees as they move between departments. I can't figure out how to set up the relationships to achieve this.
Any help or suggestions would be greatly appreciated.