Skip to Content
0

How to show records which do not meet criteria?

Dec 07, 2017 at 04:45 PM

21

avatar image
Former Member

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Brian Dong Dec 07, 2017 at 05:32 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded