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:

  1. Go to Setup|Setup Custom Tracking Queries.
  2. Note that existing hard-coded queries are already listed.  These may not be altered, but new queries can be created.
  3. Click the New Query button.
  4. Key in an appropriate name for the new query (eg Birthdays with recent visit).
  5. 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.
  6. Click the Test button to ensure that the query functions.
  7. Click Close.

To use the query, once configured:

  1. Go to Tools|Track From Custom Query.
  2. 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