Cross Tab Query - Display Active Records and Automatic Refresh 2

[BvCF]

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);

 

[dhookom]

I assume DateClosed might be null for open accounts.
Try this:

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() - Nz(qryEmpAccountStatus.DateClosed,Date()) < 31 
GROUP BY L_Employees.Username
PIVOT Nz(qryEmpAccountStatus.StatusCode,"NotWorked") IN ("NumAcctsAssigned", "Closed", "NotWorked", "Partial");

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]

dhookom,

Thanks for the insight.

I will test and post back.

Question - how would one refresh (or requery) the crosstab query when management reassigns accounts using a listbox that I have below the display of the crosstab results? What is the preferred method? Upon opening the form? Using command button?

Thanks in advance.

 

[dhookom]

How are you showing the crosstab? Is this in a form? If so, you generally can Requery the form or subform.

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]

dhookom,

The crosstab is displayed in a listbox on a page titled "Administrator" on a multi-tabbed form. The name of the multi-tabbed form is "frmTabbed."


Below the first listbox is another listbox tht displays all of the accounts in the database that the administrator can reassign to "balance" the work load. If the administrator reassigns 1 or more accounts, I would like the crosstab to reflect the "revised" work load in real time.

Is this possible? If so, how could this be accomplished?

Thanks in advance for any additional insight.

 

[PHV]

Have you tried to requery the listbox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BvCF]

Which is preferred?

Requery of the listbox or requery of the form that the listbox is on? Advantages? Disadvantages?

If I performed the requerying of the listbox, where would the requerying be located - in the listbox that displays the crosstab or the listbox that contain the accounts that the administrator re-assigns?

I assumed that a requery of the form that both listboxes are located would be preferred.

How is the requerying accomplished?

 

[PHV]

In the AfterUpdate event procedure of the reassigned accounts listbox:
Me![name of crosstab listbox].Requery

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BvCF]

PHV,

I will try this and post back.

Thanks for the insight.

 

[BvCF]

When I input "Me!List192.Requery" in the after update event of the listbox at the bottom of the form, I receive an error message;

Variance(Name of database) can't find the macro 'List192.'

The administrator reassigns accounts by selecting the accounts in the bottom listbox and then clicking on a command button to actually update the "AssignedTo" field in the local Access table.

Shouldn't there be a requery on the after update event for the command button?

 

[dhookom]

You must enter code into a module, not a property. Click the builder button to the right of the event property and go to the code window to enter PHV's code suggestion.

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]

I entered the code into a module but it is not working.

Note, the administrator selects the account within the multi-select listbox and then presses the command button titled "Reassign Acct."

The other listbox at the top of the form is based on a crosstab query and displays the employee workload. I had expected that the employee workload section would dynamically update when the administrator presses the command button but nothing happens. The Employee workload section is updated if I exit the database and then re-enter.

The name of the listbox that contains the crosstab query results (employee workload) is "List192."

Any ideas as to the reason why the listbox that contains the crosstab results is not dynamically updating?

Thanks in advance.

 

[dhookom]

Is the form being used bound to a record source? Are you expecting the record to be saved? You may need to save the record prior to requerying the list box.

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 and patience.

I have the following;

One multi-tabbed form named "frmTabbed." The record source for the multi-tabbed form is "qryTblMainNov29" and the pages are titled "Administrator", "ReviewAccounts" and "Reports." The Administrator Page contains 3 listboxes with the following properties:

Name-----------RowSource--------------Bound Column
List188--------Select qryTblMain......1 (AcctNo)
lstUnassigned--qryAdmin---------------3 (AcctNo)
List192--------qryEmpAccount_Crosstab-1 (?)

Upon the administrator reassigning accounts by selecting the accounts in the lstUnassigned listbox and then depressing the command button titled "Reassign" List188 is refreshed but List192 is not. It appears that the problem has something to do with the bound column. If so, then how can I specify that the bound column in the crosstab query to be "AcctNo?"

The crosstab query is displayed below;

TRANSFORM Count(qryEmpAccountStatus.AcctNo) AS CountOfAcctNo
SELECT Employees.Username AS AssignedTo, Count(qryEmpAccountStatus.AcctNo) AS NumEncsAssigned
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);

Any additional insight as to how I can dynamically refresh the crosstab results that are displayed in List192 would be greatly appreciated.

 

[dhookom]

If a list box has no bound column and no control source then refreshing might not have any effect. Your List192 doesn't have an AcctNo field in its Row Source.

If you want to requery the List192, use code in the after update of lstUnassigned to something like:
Me.Dirty = False
Me.List192.Requery


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]

dhookom,

Thanks for the patience on this.

When the administrator selects one or more accounts in lstUnassigned, he or she must depress the command button to update the main table, tblMain.

The contents of lstUnassigned is based on a global filter within a textbox that is right above the listbox. In other words, if the administrator types in "903", all accounts that begin with "903" are displayed. If the administrator deletes "903" then all of the accounts within the query results are displayed.

I am not understanding why the code needs to be input on the "after update" event of lstUnassigned instead of on the "after update" event of the command button. Is there some other method to requery the query titled "qryEmpAccountStatus_Crosstab?

Just trying to enhance my understanding of the requery feature.

 

[PHV]

Requery List192 in the Click event procedure of the command button ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Requery List192 in the very end of the Click event procedure of the command button ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BvCF]

Code in the onclick event of the command button displayed below. It still is not functioning as planned.

It appears that due to the fact that the crosstab query does not have the bound column equal to AcctNo, this cannot be done.


Private Sub cmdReassignAcct_Click()
On Error GoTo Err_Handler
'Purpose: Update fields on tblMain for those items selected in the list box.
Dim varItem As Variant 'Selected items
'Loop through the ItemsSelected in the list box.
With Me.lstUnassigned
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
DoCmd.RunSQL "Update tblMain " & _
"SET DateAssigned = Date(), AssignedTo = cmbUsername.Value " & _
"Where AcctNo = " & Me.lstUnassigned.ItemData(varItem)

End If
Next
End With

Exit_Handler:
Exit Sub
Err_Handler:
MsgBox err.Description
Resume Exit_Handler
Me.Dirty = False
Me.List192.Requery
End Sub

 

[PHV]

...
End With
DoEvents
Me!List192.RowSource = Me!List192.RowSource
Exit_Handler:
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886