Isn't this correct? One user be granted access to many systems. A given system can, of course, have many users.
That's a many to many relationship.
And am I correct that a given location can only be part of one system?
I'm coming up with 4 tables here, not 3 and certainly not 2.
The Join table is used to implement a many to many relationship between users and systems.
Little fuzzy coming from Lotus if your that old and dusty access.
Do I need to auto populate the field of the fk in join from pkuser and if so do i do that with the check boxes within relationships dialog box and which ones?
join of __pkUserID to _fkJoinUserID
Start with these relationships:
Users::__pkUserID = User_System::_fkUserID
Systems::__pkSystemID = User_System::_fkSystemID
You can place a portal to User_System on the Users layout to list and select Systems records for each given Users record. Fields from Systems can be included in the Portal to show additional info about each selected Systems record and the _fkSystemID field can be set up with a value list for selecting Systems records by their ID field.
Enabling "allow creation..." for User_System in the Users to User_System relationship will enable you to create a record in the User_System table by entering data into any row of the "add row" found in the portal to User_System. Thus, you can create the record and link it to both Users and Systems by formatting the _fkSystemID field with a value list for selecting a system.
You can also turn this around and work the same time of set up from a layout based on Systems where you select a user for the system from the _fkUserID field.