Dynamic Crosstab Query - Display in listbox! 1

[BvCF]

Have the following crosstab query sql script;

TRANSFORM Count(qryEmpAccountStatus.AcctNo) AS CountOfAcctNo
SELECT Employees.Username, Count(qryEmpAccountStatus.AcctNo) AS NumAcctsAssigned
FROM Employees INNER JOIN qryEmpAccountStatus ON Employees.Employee_ID = qryEmpAccountStatus.AssignedTo
GROUP BY Employees.Username
PIVOT qryEmpAccountStatus.StatusCode;

This results in the following display:

Username----NumAcctsAssigned-----<>------Open
Jill--------23-------------------21------2
John--------15-------------------5-------10

The column titled "<>" is those accounts that have not been worked. Question 1 - How do I include the column heading "NotWorked" in the query results? Note, the various values for the StatusCode are "NotWorked", "Open", "Pending", and "Closed." In essence, when an account is assigned, the status of that assignment will be one of the four status codes.
Question 2 - How do I display the results of this cross-tab query within a listbox so that the Manager can receive a real-time look at the status of the employees' workload?

Thank you.

 

[dhookom]

I would use a subform rather than a list box. However, try SQL like:

TRANSFORM Val(Nz(Count(qryEmpAccountStatus.AcctNo),0)) AS CountOfAcctNo
SELECT Employees.Username, Count(qryEmpAccountStatus.AcctNo) AS NumAcctsAssigned
FROM Employees INNER JOIN qryEmpAccountStatus ON Employees.Employee_ID = qryEmpAccountStatus.AssignedTo
GROUP BY Employees.Username
PIVOT Nz(StatusCode,"NotWorked") In ("NotWorked", "Open", "Pending", "Closed");

This show realtime information at the time the form is opened. If you need updating, you will have to run code to requery the control.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BvCF]

Thanks for the insight.

Before your response, I was able to display the desired results using the following:

TRANSFORM Count(qryEmpAccountStatus.AcctNo) AS CountOfAcctNo
SELECT Employees.Username, Count(qryEmpAccountStatus.AcctNo) AS NumAcctsAssigned
FROM Employees INNER JOIN qryEmpAccountStatus ON Employees.Employee_ID=qryEmpAccountStatus.AssignedTo
GROUP BY Employees.Username
PIVOT IIf(qryEmpAccountStatus.StatusCode Is Null,"Not Worked",qryEmpAccountStatus.StatusCode);

The results are displayed in a listbox.

I have another list box on the form that displays all accounts that have been assigned to the employees. I would like to link the listbox containing the cross tab results to the listbox that contains all of the accounts that have been assigned.

For example, if the manager clicks on a specific username in the "cross tab listbox", can I synch this up to the listbox that contains all of the assigned accounts by displaying the accounts that have been assigned to the individual whose name is selected in the first listbox? Note, the bound column for both listboxes is currently the account number.

Is it possible to synch the two listboxes on another field that is not the bound column field?

Thanks in advance.

 

[dhookom]

Roger Carlson has a small sample mdb that describes cascading combo boxes. This should also work with list boxes. You may need to either change the bound column to one that is needed for the "child" list box or add a hidden text box to use with a control source like:
=lboEmpStatus.Column(2)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]