BvCF
MIS
- Nov 11, 2006
- 92
Currently I have the following cross tab query;
TRANSFORM Count(qryEmpAccountStatus.AcctNo) AS CountOfAcctNo
SELECT L_Employees.Username AS AssignedTo, Count(qryEmpAccountStatus.AcctNo) AS
NumAcctsAssigned
FROM L_Employees INNER JOIN qryEmpAccountStatus ON
L_Employees.Employee_ID=qryEmpAccountStatus.AssignedTo
GROUP BY L_Employees.Username
PIVOT IIf(qryEmpAccountStatus.StatusCode Is Null,"Not
Worked",qryEmpAccountStatus.StatusCode);
Results in a display on a "Manager" form in a format like the following:
AssignedTo---NumAcctsAssigned---Closed---NotWorked---Partial
John---------20------------------5-------15
Sally--------15------------------5-------8------------2
Fran---------10------------------2-------8
However, Management desires that the pivot table should display only active records and those
accounts that have been closed within the last 30 days. "Active" implies that the status
on the account is either "NotWorked","Open", "Partial" or "OnHold." If an account is closed, then
the DateClosed field will display the date that the account was closed.
How should the cross tab query be modified so that I will display all "active" accounts
and those accounts that have been closed within the last 30 days? Also, is it possible to have the cross tab query automatically refresh upon the Manager reassigning accounts?
Manager reassigns accounts by using the same "Manager" Form.
Tried to modify the query but haven't had much success. My latest attempt, as displayed
below, results in no records being displayed.
TRANSFORM Count(qryEmpAccountStatus.AcctNo) AS CountOfAcctNo
SELECT L_Employees.Username AS AssignedTo, Count(qryEmpAccountStatus.AcctNo) AS
NumEncsAssigned
FROM L_Employees INNER JOIN qryEmpAccountStatus ON
L_Employees.Employee_ID=qryEmpAccountStatus.AssignedTo
where Date() - qryEmpAccountStatus.DateClosed < 31 and qryEmpAccountStatus.StatusCode in
("NotWorked", "Open", "Partial")
GROUP BY L_Employees.Username
PIVOT IIf(qryEmpAccountStatus.StatusCode Is Null,"Not
Worked",qryEmpAccountStatus.StatusCode);
TRANSFORM Count(qryEmpAccountStatus.AcctNo) AS CountOfAcctNo
SELECT L_Employees.Username AS AssignedTo, Count(qryEmpAccountStatus.AcctNo) AS
NumAcctsAssigned
FROM L_Employees INNER JOIN qryEmpAccountStatus ON
L_Employees.Employee_ID=qryEmpAccountStatus.AssignedTo
GROUP BY L_Employees.Username
PIVOT IIf(qryEmpAccountStatus.StatusCode Is Null,"Not
Worked",qryEmpAccountStatus.StatusCode);
Results in a display on a "Manager" form in a format like the following:
AssignedTo---NumAcctsAssigned---Closed---NotWorked---Partial
John---------20------------------5-------15
Sally--------15------------------5-------8------------2
Fran---------10------------------2-------8
However, Management desires that the pivot table should display only active records and those
accounts that have been closed within the last 30 days. "Active" implies that the status
on the account is either "NotWorked","Open", "Partial" or "OnHold." If an account is closed, then
the DateClosed field will display the date that the account was closed.
How should the cross tab query be modified so that I will display all "active" accounts
and those accounts that have been closed within the last 30 days? Also, is it possible to have the cross tab query automatically refresh upon the Manager reassigning accounts?
Manager reassigns accounts by using the same "Manager" Form.
Tried to modify the query but haven't had much success. My latest attempt, as displayed
below, results in no records being displayed.
TRANSFORM Count(qryEmpAccountStatus.AcctNo) AS CountOfAcctNo
SELECT L_Employees.Username AS AssignedTo, Count(qryEmpAccountStatus.AcctNo) AS
NumEncsAssigned
FROM L_Employees INNER JOIN qryEmpAccountStatus ON
L_Employees.Employee_ID=qryEmpAccountStatus.AssignedTo
where Date() - qryEmpAccountStatus.DateClosed < 31 and qryEmpAccountStatus.StatusCode in
("NotWorked", "Open", "Partial")
GROUP BY L_Employees.Username
PIVOT IIf(qryEmpAccountStatus.StatusCode Is Null,"Not
Worked",qryEmpAccountStatus.StatusCode);
