Select Union Min?

[xPaul]

All,

I have created a simple, but maybe complex, permissions structure in my database which comprises of users and groups and their permissions. Essentially put the database has a set of defined permissions (and levels - no, yes, read, write, delete etc) in a table and a user is either added to that permission directly (user permission) or be added to a group and thusly the group is then added to that permission. Of course these group and user permissions then have a selected permission level assigned.

I have added a table with a list of SQL Queries that I wish to dynamically add on the fly and be displayed to the end user via the front end. This way should the user request a specific query it can be created, added and used immediately without requiring the front end to be updated with a button and the SQL behind it.

I wish to restrict the queries to those with the correct permission assigned - the user has to have either the user permission or the group permission and with a permission level higher than no (0). As a result I have included, in the SQL Queries table, the PermissionID that the users or groups with permission they will be able to see.

I am trying to go on the concept of 'lowest permission wins' as a rule of thumb to increase security within the database.

I have created a union query which queries both the users permissions and groups permissions tables to return the lowest permission however this returns the lowest from each query. I, ideally, want a query that will return the lowest value from, as a collective, both queries, not the lowest value from each.

In laymans terms - if the user permission for the SQL queries has a permission level of no (0) and the group permission for the SQL queries has a permission level of yes (1), the query should not return any viewable SQL queries as the lowest value here would be no (0).

I understand that you can do a min on a simple select query but, in Access, can you do a min on the overall union query?

Below I have shown the relationship structure to give you a better understanding of what the current structure.

 

[plog]

I understand that you can do a min on a simple select query but, in Access, can you do a min on the overall union query?

UNION queries should only be for bringing datasets together. You shouldn't do any overriding logic within them. Instead, you should build another query using the UNION query as a datasource:

SELECT FirstName, LastName, SalesAmount FROM TableA
UNION ALL
SELECT FirstName, LastName, SalesAmount FROM TableB WHERE IsActive=True
UNION ALL
SELECT FirstName, LastName, SUM(IndividualSales) AS SalesAmount FROM TableC
GROUP BY FirstName, LastName

...
Then to find SalesAmounts over $100, you do another query:
...

SELECT FirstName, LastName, SalesAmount
FROM UnionQueryName
WHERE SalesAmount>100

TableB required logic specific to that query, TableC required logic specific to that query. Then, all the data shared a common set of logic, that was done in a new query which allowed me to do it with one line of code (instead of 3--one for each SELECT in the UNION). Aggregating data should be handled the same way.

 

[CJ_London]

you could also consider combining your userpermissions and grouppermissions tables as 1 table - if users have a group to themselves then just create a group permissions for them

Would also simplify your relationships