Custom Tracking Queries
Abstract
Capable 21C has the ability to use native SQL Queries to produce lists of patients/clients
for tracking. This knowledgebase article explains how to use this feature.
Underlying challenge
SQL (Structured Query Language) is a computer programming language designed to allow
an operator to interrogate, and even update a database. Whilst it was originally
intended to be close to natural english language, it is actually quite technically
complex, and can be difficult to learn and use. Capable 21C technicians are
expert in the use of SQL, and can easily produce SQL queries to retrieve a wide
variety of lists of patients/clients. An example an SQL query follows:
SELECT Patient.[Patient ID], DateSerial(Year(Now()),Month([Date of Birth]),Day([Date
of Birth])) AS [Action Date]
FROM Appointments INNER JOIN Patient ON Appointments.[Patient ID] = Patient.[Patient
ID] WHERE (((Appointments.Appointment)>DateAdd("d",-180,Now())) AND ((Appointments.Status)="Completed"))
GROUP BY Patient.[Patient ID], DateSerial(Year(Now()),Month([Date of Birth]),Day([Date
of Birth]))
HAVING (((DateSerial(Year(Now()),Month([Date of Birth]),Day([Date of Birth]))) Between
Now() And DateAdd("d",30,Now())))
Whilst complicated, this particular query will retrieve all patients/clients who
have a birthday in the next 30 days, and have also had a visit in the last 180 days.
Once the SQL query has been developed, it can be pasted into Capable 21C.
From there, Capable 21C has the ability to retrieve the list of matching patients/clients
and display them much the same as the existing Bring Up, Recall or Birthday lists.
This empowers the operator to print lists, mailmerge letters or even send SMSes
and e-mails to the nominated patients/clients.
Resolution
To configure Capable 21C to exploit a query such as the example shown above:
- Go to Setup|Setup Custom Tracking Queries.
- Note that existing hard-coded queries are already listed. These may not be
altered, but new queries can be created.
- Click the New Query button.
- Key in an appropriate name for the new query (eg Birthdays with recent visit).
- Paste the text of the query. As an example, it is quite safe to paste the
example text shown above, or if an alternative query has been e-mailed, paste it
instead.
- Click the Test button to ensure that the query functions.
- Click Close.
To use the query, once configured:
- Go to Tools|Track From Custom Query.
- Choose the desired query.
Detailed Information
The following information is more technical and designed for operators with some
experience writing and debugging SQL Statements.
Capable 21C requires the query to return at least two output fields. These
must be called Patient ID and Action Date. The query does not need to return
any records (but will not be very useful if it does not).
The query can be further enhanced by introducing parameter values. To do this,
substitute any expression within the query with parameters. To implement a
parameter, surround the textual prompt with either %d (for a date), %t (for plain
text) or %n (for a numerical value). For example, to add a parameter to the
query shown above, try the following:
SELECT Patient.[Patient ID], DateSerial(Year(Now()),Month([Date of Birth]),Day([Date
of Birth])) AS [Action Date]
FROM Appointments INNER JOIN Patient ON Appointments.[Patient ID] = Patient.[Patient
ID] WHERE (((Appointments.Appointment)>DateAdd("d",-180,Now())) AND ((Appointments.Status)="Completed"))
GROUP BY Patient.[Patient ID], DateSerial(Year(Now()),Month([Date of Birth]),Day([Date
of Birth]))
HAVING (((DateSerial(Year(Now()),Month([Date of Birth]),Day([Date of Birth]))) Between
Now() And DateAdd("d",%nPlease key the number of days of birthdays%n,Now())))
Note that queries are automatically adapted for the database platform, so the text
delimiter for Microsoft Access should be used (eg use double quotes to delimit strings
constants, and not single quotes).
|
This knowledge resource is designed
for use on a Windows (tm) XP system operating at a screen
resolution of 1024x768 or better. Windows Media Player must be
installed for this training resource to operate correctly.
Copyright 2008 Capable Software
Pty Ltd
Training Sessions
Fundamentals
Training Modules
Intermediate
Training Modules
Advanced
Training Modules
Other Resources
Return to Capable
Software home
Contact Technical Support
|