Results 1 to 5 of 5
  1. #1
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30

    Design To Limit Database Viewing Rights

    This could be under the security thread, but I am mostly after design ideas, so I placed it here. Any ideas and suggestions are appreciated.

    My goal is to limit query results based on the user running the query. This is easy enough at first glance, but I have another layer to it that is causing me issues. I have a table(tblPurchaseOrders) where all of my companies orders are inputted via a form. The sales persons is associated with every PO listed. Every Salesperson has an assistant, and some have multiple assistants. The salesperson should see all of their own orders, but the assistants should only see the orders that they are associated with and not the other assistants under the sales person. I have thought of a few ways to do it, but they seem a bit over the top. If anyone has a simple design for this, I would appreciate it. I don't need any code, just design ideas.

    Thanks,

    Garret

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    A table of users with the ID taken from their OS login (Environ("username") with a level determining their status. A cross-reference table with primary user and secondary user. If their status is secondary then only show orders with their own ID attached, if their status is primary show them all their own plus all from the cross-reference table.

  3. #3
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Garret,

    Sounds like you already have it working by Salesperson.

    Are you looking for suggedstions on how to improve your design. Is yes, then it helps if you post your current design.

    How are you linking sales people with their assistant(s)?

    You may be able to use your current table design. I would first look into using either sub queries and/or Union queries to do what you need.

    If you are looking to redesign your tables:

    In my applications I have a "master" table for all people. I use juction tables to relate people togethre or with "roles"

  4. #4
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30
    Thanks to both of you. I ended up creating another table for assistants, and creating a junction table for the relationship to the salesperson. I then added the usernames associated with those tables into the query and used those against the user login that I store when the employee logs into the database for what orders will display. It may not be the most elegant, but it works!

  5. #5
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by whisp0214 View Post
    Thanks to both of you. I ended up creating another table for assistants, and creating a junction table for the relationship to the salesperson. I then added the usernames associated with those tables into the query and used those against the user login that I store when the employee logs into the database for what orders will display. It may not be the most elegant, but it works!
    You're welcome. Glad we could assist.

    Great job getting it working.

    Thanks for paying it forward by sharing your solution.

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

Similar Threads

  1. Issue viewing all records on a split database
    By rosscortb in forum Access
    Replies: 4
    Last Post: 02-17-2015, 06:26 AM
  2. Limit Data that Users see in Database
    By katkth7533 in forum Access
    Replies: 6
    Last Post: 02-11-2015, 08:09 AM
  3. Viewing Attachments in Access Database
    By RootMason in forum Access
    Replies: 22
    Last Post: 12-12-2013, 10:00 AM
  4. Viewing database pictures
    By Mnelson in forum Access
    Replies: 1
    Last Post: 07-10-2012, 03:31 PM
  5. Replies: 5
    Last Post: 12-22-2011, 01:12 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