Results 1 to 6 of 6
  1. #1
    Glenikins is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    3

    Returning records from a table x times removed from current table

    First of all apologies for the confused-sounding title of this thread; I sat for a while trying to put my problem into a few words and that's the best I could come up with.

    Here's my problem in a few more words - I have a chain of related tables, one linked to the next via a one-to-many relationship. Together they define how users of a particular system connect to remote computers, by defining a) a list of personnel, b) a list of accounts held by personnel, c) a list of jump machines to which each account has access, d) a list of remote systems to which each jump machine has access. For each remote system I want to present in a form (via a query) a list of all personnel who have access to it. Person X has access to System Y if he/she has an account to a jump machine which has access to that system. There are actually two more steps in my database but the above is a simplified model of the problem.

    (NB a jump machine is an intermediate machine to which a person needs to gain access in order to connect onwards to the final remote system).



    I have been grappling with various SELECT queries using different sorts of JOINs, but all I've managed to achieve is a list of all users. I have not been able to specify the intervening criteria which determine if a record in the Personnel table should be included or not.

    If anyone can a) understand what I'm trying to describe above and b) offer any pointers, I would be very grateful.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You have the primary key of each parent in a foreign key field of every child table? A pic of your relationships (if you've created them) might help to understand. Or a copy of the db, compacted and zipped and uploaded here.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Glenikins is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    3
    Here's a snip with the table relationships. For whichever tblSystems record is being presented on the form, I'm looking for a sub-form (based on my query) which lists all personnel (from tblPersonnel) who have an account defined in tblSystemUsers which maps to a group (via tblSystemvSphereVMUsers and tblSystemvSphereGroups) which includes a VM (virtual machine - tblSystemvSphereVMs) which has access to the system in question (defined in tblSystemvSphereVMAccess). As I said, all I've managed to achieve so far is a lengthy SELECT query with multiple INNER JOIN statements to link all the table together and result in all entries in tblPersonnel being displayed on my form. I'm struggling with how to implement the conditional logic I'm after. I should have mentioned that I am very new to Access (although that's probably apparent).


  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 3 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Glenikins is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2021
    Posts
    3
    Don't know what your post means Paul, although I guess the screen-shot I attached to my post was removed?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Normally when you reply to a thread other users who have been active on the thread get email notifications. When a post is moderated and later approved, those email notifications don't go out. I post something like I did to alert other users that you've replied.

    As to your screen shot, I did not remove it. I assume that the forum software was unable to handle it for some reason, so perhaps try attaching it again?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 02-20-2020, 09:07 PM
  2. Update Query returning more records than table it is updating
    By frustratedwithaccess in forum Queries
    Replies: 4
    Last Post: 02-07-2015, 04:20 PM
  3. Replies: 6
    Last Post: 05-04-2014, 12:53 PM
  4. Replies: 2
    Last Post: 04-04-2012, 03:52 AM
  5. Replies: 1
    Last Post: 09-20-2011, 03:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums