How to show records which do not meet criteria?

[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?

[former_member292966]

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