I've successfully modified the
Member Roster useful report to filter on a single value in the field
dbo.UserRoles.RoleID. Any given user in our database has at least 2 and possibly as many as 5
RoleID values. The following query works for a single value of this field:
SELECT TOP (100) PERCENT dbo.Users.LastName AS [Last Name],
dbo.Users.FirstName AS [First Name],
Address.PropertyValue AS Address,
City.PropertyValue AS City,
State.PropertyValue AS State,
Zip.PropertyValue AS Zip,
Phone.PropertyValue AS [Phone Num],
dbo.Users.Email
FROM dbo.Users
INNER JOIN dbo.UserRoles
ON dbo.Users.UserID = dbo.UserRoles.UserID
AND dbo.UserRoles.RoleID = 5
INNER JOIN dbo.UserProfile AS Address
ON dbo.Users.UserID = Address.UserID
AND Address.PropertyDefinitionID = '26'
LEFT OUTER JOIN dbo.UserProfile AS City
ON dbo.Users.UserID = City.UserID
AND City.PropertyDefinitionID = '27'
LEFT OUTER JOIN dbo.UserProfile AS State
ON dbo.Users.UserID = State.UserID
AND State.PropertyDefinitionID = '28'
LEFT OUTER JOIN dbo.UserProfile AS Zip
ON dbo.Users.UserID = Zip.UserID
AND Zip.PropertyDefinitionID = '30'
LEFT OUTER JOIN dbo.UserProfile AS Phone
ON dbo.Users.UserID = Phone.UserID
AND Phone.PropertyDefinitionID = '31'
The statement above displays the fields for all members of the "Active Member" role (value 5). If I change the value to read
AND dbo.UserRoles.RoleID = 7
(or 8, or 9, or 10) it will display all members of those roles (we are a chorus, and those roles represent voice parts: alto, soprano, tenor, and bass). I want to select on both the "Active Member" role and a single voice part role, so as to display a separate roster for each voice part. When I created this statement I didn't get a SQL error, but the display only showed the column headers. I know by experiment that I can select all the members of any single role. Why does an attempt to select all members of two roles fail to find any data?