Skip to Content
avatar image
Former Member

How to show records which do not meet criteria?

Hi,

I'm looking to create a report to show a list of users who do not have a certain access right. I'll need to link two tables for this, the USERS table and SYSACCESS table. However, a user can be set up in multiple groups.

The tables are laid out like:

USERS

USERNAME ACTIVE

DAVID Y

COLIN Y

JASON Y

SYSACCESS

USERNAME ACCESS_GROUP

DAVID FINANCE_USERS

DAVID BUDGET_MANAGERS

DAVID ACC_REPORTING

COLIN BUDGET_MANAGERS

COLIN DEVELOPMENT_USERS

JASON DEVELOPMENT_USERS

JASON ACC_REPORTING

From this example, I want the report to show all users who do not have the BUDGET_MANAGERS group, so it should just show JASON. However, I can't get my head around what restrictions I should use.

Anyone able to help?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Dec 07, 2017 at 05:32 PM

    Hi David,

    Try doing a Left Outer Join between Users and SysAccess.

    Create a group on SysAccess.ACCESS_GROUP so you can see the groups

    Once you've done that, place Users.Username and SysAccess.ACCESS_GROUP in the Detail section.

    You should be able to format the Detail section to hide with a formula like:

    Not IsNull ({SysAccess.ACCESS_GROUP}); 

    This should only show you people that don't belong in that group.

    Avoid using a Record Selection because this can force an Equal join and then you won't get the people with no matching records.

    Good luck,

    Brian

    Add comment
    10|10000 characters needed characters exceeded