This organization, among many other things, provides counseling. The receptionist books the appointments and over the years they’ve accumulated a lot of names. The counseling manager stated they were cleaning up the data and inactivating individuals that hadn’t been seen in over a year. So I decided to help their staff a little. Actually a lot.
Below is the the revised search form. I added the circled areas.
Last assigned date
This shows the last appointment date the individual had with the organization. Notice how vertical scroll bar button is a long ways down on this list box. This shows that many Active & Intake individuals have never made an appointment. And see how some active individuals last appointment was in 2005. Definitely overdue for some data cleanup.
Now I always have trouble with this kind of query as I don’t do it very often. So I always have to go looking it up. And each time I do I say to myself “Tony, record this as you’ll need it in six months again.” Well here it is.
The key things here are you must create a Totals query that you will join to another table or query that is based on the client appointment table. (I did not design this system so don’t blame me for the table names. And yes, folks much more talented in SQL can probably figure out how to create a sub query but I’m not one on those.) The primary key, in this case, the ClientNo is set to Group By. As we want the last assigned date the Max of the AssignedDate is chosen along with sorting the field in descending sequence
I decided to rename the AssignedDate field myself as otherwise the name in the below query would’ve been MaxofAssignedDate.
This is the query for the listbox above. Note the right join to the above query. The right join ensures that folks without ever having made an appointment will appear on the listbox.
Sort By
The Sort By allows the users to view the folks which the oldest appointment first so it’ll now be quite easy to inactivate folks. In the frames On Click event I have the following code.
Call FilterAndSortClientListComboBox
I prefer to call a subroutine because the record source of the list box is now updated in three places. The client name search field, the client status combo box and the Sort By filter. This way the three search and filter fields all call one subroutine. We’ll get to that subroutine shortly.
Client Status
The client status combo box is based on the client status table but with some extras as you can see. The extras were done using a Union query.
SELECT tblClientStatus.ClientStatusNo, tblClientStatus.ClientStatus FROM tblClientStatus
UNION SELECT -1 as Expr1, ' (All)' as Expr2 From tblClientStatus
UNION SELECT -2 as Expr1, ' (None)' as Expr2 From tblClientStatus
UNION SELECT -3 as Expr1, ' (Active & Intake)' as Expr2 From tblClientStatus
ORDER BY tblClientStatus.ClientStatus;
Of course a SELECT is ridiculous in this circumstance but you have to follow SQL rules. Do ensure these dummy values come from a table with very few records. I put a space before the bracket in Expr2 so these sort first and it’s quite obvious to the user that these are special values.
The FilterAndSortClientListComboBox subroutine is also called in the combobox’s AfterUpdate event.
The FilterAndSortClientListComboBox subroutine
Sub FilterAndSortClientListComboBox()
Dim strSQL As String
Select Case Me.cboClientStatusFilter
Case -3 ' 3 & 1, Active and Intake - This is the default
strSQL = "SELECT * FROM qryClientListCS " & _
"WHERE ClientStatusNo=1 OR ClientStatusNo=3"
Case -2 ' None
strSQL = "SELECT * FROM qryClientListCS " & _
"WHERE ClientStatusNo Is Null"
Case -1 ' All
strSQL = "SELECT * FROM qryClientListCS"
Case Else
strSQL = "SELECT * FROM qryClientListCS " & _
"WHERE ClientStatusNo=" & Me.cboClientStatusFilter
End Select
If Me.frmSortBy = 2 Then _
strSQL = strSQL & " ORDER BY [Last Assigned Date]"
Me.lstClient.RowSource = strSQL
End Sub
Summary
These changes didn’t take too long. But they should make a significant difference in the ease of use of this system. One of my rules is to always spend some time with the key users of any system. Not just the managers but the users. I frequently break that rule these days especially when doing remote programming. But I’ve designed enough systems that I arrogantly feel I can put some ease of use features in with just a few sentences from the users describing the pain points.
(Well, it took longer than expected as the original developers of this system were not Access gurus and it was tough figuring out what they were all trying to do. They were not taking advantage of Access’s bound forms in many ways. )