Many to Many Grid

[Mike Thomas]

Does anyone have a suggestion for making a grid which would be expandable as
to both columns and rows? (eg, something like a cross tab grid?)

I'd like to make a form which connects user profiles and their permissions
regarding certain tasks.

Ideally, the grid in the form would look something like:

Profile 1 Profile 2
Profile 3

Change Part Price yes no yes
Create Purchase Order yes yes yes
Delete Order yes no no

and so on. Profiles could be added (rare but probable) and functions could
be added or dropped. The table setup seems straightforward: 3 tables -
profile, function, and a many to many join table.

But is there a way to display such a grid?

Many thanks
Mike Thomas

 

[HSalim]

Mike,
Let us say you have this table:
TblProfileFunctions
ProfileNum Integer
FunctionName Char(50)
Permission Yes/No

and you input the values you provided below.

You can use this query to return your result set.

TRANSFORM Min(TblProfileFunctions.Permission) AS MinOfPermission
SELECT TblProfileFunctions.FunctionName
FROM TblProfileFunctions
GROUP BY TblProfileFunctions.FunctionName
PIVOT TblProfileFunctions.ProfileNum;

You need an aggregate in the transform clause - use anything - sum, min
whatever, it will still return the resultset.

HS