Searching the Database

Bp Premier offers a comprehensive database search tool for retrieving records from the database based on patient record or system criteria.

What can I use a database search for?

A common use of the search tool is to produce a list of patients for a mail merge. For example, your practice is introducing a new diabetes management plan, and you need to set up a mailout to all of your active patients with this condition. See Send Health Awareness Communications for more information.

If you are only interested in searching for patients by name or basic demographics, the Patient List window might be easier to use. Press F10 from the main screen.

Searches are created in Structured Query Language (SQL), but you do not need to understand SQL to use the search tool. You can select from a set of standard search filters for the records you are looking for, and Bp Premier will create the SQL query for you. You can also save queries that you run often, and import queries from other Bp Premier users.

Two common searches are searching for patients with diabetes and searching for patients who are or are not immunised. These searches provide examples of creating complex queries using the logic operators NOT, AND, and OR.

Can I run an email merge or phone list from a database search?

You can only mail merge to letter, SMS, or Best Health App from a database search. However, you could save the results to a .csv file and use a spreadsheet application to extract data from a column. See Export query results to file for more information.

Run a database search query

NOTE  Patients who have Deny access to other users set in the patient demographic are excluded from appearing in search utility results. See Understanding confidentiality and patient notes for more information.

  1. Select Utilities > Search from the main menu to view the Database Search screen.
  2. Database Search

    The filter buttons along the top will open a filter screen that allows you to drill down on the data you are looking for. For example, click Demographics to search based on patient demographic data, or click Visits to search based on patient visit data.

  3. If you are proficient at SQL and know the Bp Premier database structure, you can type your custom query directly into the SQL Query text field.
  4. Otherwise, click the button that matches the area you want to search by. A Search for ... screen will be displayed that depends on the selected button.
  5. Complete the criteria you want to search for in the top half of the Search screen. The fields available will depend on the selected button. For example, if you clicked Drugs, you would select the drug prescription you are searching for, and which doctor prescribed. If you clicked Observations, you would select the clinical observation and the range of values the observation should fall between.
  6. Click Add to add the search condition to the Condition box at the bottom of the screen.
  7. Repeat to add as many conditions are you need.
  8. Click OK from the Search screen to return to the Database search screen. The SQL Query has been updated to reflect your search criteria.
  9. If you need to include inactive patients in your search results, tick the check box.
  10. If you need to include deceased patients in your search results, tick the check box.
  11. Click Run query to execute the query and list the patients who meet that condition.
  12. If the query ran successfully, and you think you may need to rerun the query in the future, click Save query to save the query to disk with a meaningful name.

It may take a few attempts to create a query that returns the results you want. You can use the samples database installed with Bp Premier to practice running database requests without putting load on the live server.

Limited time or capacity? Prefer to have someone build a custom report, query, or template for you? Learn more about our SQL and Custom Report Building service here.

Using AND, OR and NOT

SQL logic operators are used in database queries to combine and manipulate conditions. These operators allow you to create complex conditions by connecting multiple logical expressions.

SQL Logic Operators

Explanation

Search Examples

AND Operator

When you use the AND operator, all the conditions connected by it must be true for a row to be selected. It narrows down the results by requiring multiple conditions to be met

Find all active patients who have been given the first vaccine AND do not have an appointment booked for the second dose.

OR Operator

When you use the OR operator, at least one of the conditions connected by it must be true for a row to be selected. It expands the results by including rows that satisfy any of the conditions.

Patients with no allergy OR reaction information recorded

NOT Operator

When you use the NOT operator, it negates a condition. It returns true if the condition following the NOT operator is false, and false if the condition is true. It allows you to exclude certain rows from the results.

Patients over 65 seen in last year, NOT had Fluvax, NOT opted out and NOT allergic.

By combining these logical operators (AND, OR, NOT) with conditions, you can build powerful queries to filter and retrieve data from a database based on specific criteria.

The following video demonstrates how to use the drugs filter to search for patients prescribed Zoloft by both Mr S. Gunter and Dr F. Findacure and have not been prescribed Lipitor by Mr S. Gunter.

Last updated 15 May 2024.