group query

[Guest]

If I have a table with columns: Assignment - User names - then 65 different
security columns. The Assignment could be Booking then a user name then a Y
or N in the 65 security columns.
I want to Group the Assignment, then pull out all the users that have Y's
in the same security columns.
Not sure how to do this.
Thanks,

 

[Guest]

Pam,

It looks like your table structure needs to be revised. The 65 security
columns should actually be represented by 2 columns (I'll call them
Security_Location and Security_Access).

Assuming you are stuck with the structure you have, and are looking for all
of those users that 'Y' in specific columns, you could do something like:

SELECT Assignment, User_Name
FROM yourTable
WHERE IIF([SecCol1]="Y", 1, 0) + IIF([SecCol3]="Y", 1, 0)) = 2
GROUP BY Assignment, User_Name

This would identify those that have a "Y" in Security Columns 1 and 3. You
could write some code to build this on the fly to build up the portion that
lists the specific fields you want based on some criteria.

HTH
Dale