M
Markpud
Hi all,
I am trying to build a query that must draw on a table that is separate from
all the other relations in my db..
For a customer accounts database, the "VCC Role" defines the class of
operator who will be taking calls from customers, and what access this
operator gets. I have a query that brings up a list of "Activites" that this
role cannot access, and the hand-off required. I also have to factor in a
"Status" on the customer account, which is an unrealted factor, e.g. bad
payment history which prevents the operator from accessing the account, and
forces a hand-off to a different office.
So I have tried to add this into the query to show which "Activities" the
"VCC Role" would be able to access normally, but cannot because of the
"Status". Here is the SQL!!:
SELECT [VCC Role].[VCC Role], [Status Codes].[Status Code],
[Activities].[Activity Name], [VCC Role to Activity].[Access Allowed], [VCC
Role Routing].[VCC Role Routing], [Status Codes].[Access Allowed], [Status
Codes].Routing
FROM [Status Codes], [Activity Folder] INNER JOIN ([Activities] INNER JOIN
([VCC Role Routing] INNER JOIN ([VCC Role] INNER JOIN ([VCC Role to Activity]
INNER JOIN [VCC Role Message Display] ON [VCC Role to Activity].[VCC Role to
Activity ID] = [VCC Role Message Display].[VCC Role to Activity]) ON [VCC
Role].[VCC Role ID] = [VCC Role to Activity].[VCC Role]) ON [VCC Role
Routing].[VCC Role Routing ID] = [VCC Role Message Display].[VCC Role
Routing]) ON [Activities].[Activity ID] = [VCC Role to Activity].Activity) ON
[Activity Folder].[Activity Folder] = [Activities].[Activity Folder]
WHERE ((([VCC Role].[VCC Role])=[Enter VCC Role]) AND (([Status
Codes].[Status Code])=[Enter Status]) AND (([VCC Role to Activity].[Access
Allowed])=0)) OR ((([VCC Role].[VCC Role])=[Enter VCC Role]) AND (([Status
Codes].[Status Code])=[Enter Status]) AND (([VCC Role to Activity].[Access
Allowed])=-1) AND (([Status Codes].[Access Allowed])=0))
ORDER BY [Activity Folder].Priority;
I want this to output the Activites that the VCC Role cannot access, and the
ACtivities that the VCC Role can access, but the Status prevents any access
to, but all it gives is the Activities that the VCC Role cannot access.
This is giving me headaches, but is a requirement for the database to be
able to do so!
Anyone can make sense of this, and offer any ideas?!
I am trying to build a query that must draw on a table that is separate from
all the other relations in my db..
For a customer accounts database, the "VCC Role" defines the class of
operator who will be taking calls from customers, and what access this
operator gets. I have a query that brings up a list of "Activites" that this
role cannot access, and the hand-off required. I also have to factor in a
"Status" on the customer account, which is an unrealted factor, e.g. bad
payment history which prevents the operator from accessing the account, and
forces a hand-off to a different office.
So I have tried to add this into the query to show which "Activities" the
"VCC Role" would be able to access normally, but cannot because of the
"Status". Here is the SQL!!:
SELECT [VCC Role].[VCC Role], [Status Codes].[Status Code],
[Activities].[Activity Name], [VCC Role to Activity].[Access Allowed], [VCC
Role Routing].[VCC Role Routing], [Status Codes].[Access Allowed], [Status
Codes].Routing
FROM [Status Codes], [Activity Folder] INNER JOIN ([Activities] INNER JOIN
([VCC Role Routing] INNER JOIN ([VCC Role] INNER JOIN ([VCC Role to Activity]
INNER JOIN [VCC Role Message Display] ON [VCC Role to Activity].[VCC Role to
Activity ID] = [VCC Role Message Display].[VCC Role to Activity]) ON [VCC
Role].[VCC Role ID] = [VCC Role to Activity].[VCC Role]) ON [VCC Role
Routing].[VCC Role Routing ID] = [VCC Role Message Display].[VCC Role
Routing]) ON [Activities].[Activity ID] = [VCC Role to Activity].Activity) ON
[Activity Folder].[Activity Folder] = [Activities].[Activity Folder]
WHERE ((([VCC Role].[VCC Role])=[Enter VCC Role]) AND (([Status
Codes].[Status Code])=[Enter Status]) AND (([VCC Role to Activity].[Access
Allowed])=0)) OR ((([VCC Role].[VCC Role])=[Enter VCC Role]) AND (([Status
Codes].[Status Code])=[Enter Status]) AND (([VCC Role to Activity].[Access
Allowed])=-1) AND (([Status Codes].[Access Allowed])=0))
ORDER BY [Activity Folder].Priority;
I want this to output the Activites that the VCC Role cannot access, and the
ACtivities that the VCC Role can access, but the Status prevents any access
to, but all it gives is the Activities that the VCC Role cannot access.
This is giving me headaches, but is a requirement for the database to be
able to do so!
Anyone can make sense of this, and offer any ideas?!