Query Help Please

[edcoppen]

Please can I have assistance with making 3x different queries:

1) I want to know what percentage of active patients have an email address recorded. Let's consider active as any patient that has attended in the last 2 years. I imagine this is achieved by two reports, one looking at the number of active patients and another looking at the number of active patients with an email address.

2) I want to know how many active patients are registered to a particular dentist. Again, let's consider active as attending within the last 2 years.

3) I want to generate a list of patients booked in with a specific dentist for exams, but who are registered to another specific dentist.

Thanks in advance,
Ed

[matthewbridge]

Hi @edcoppen,

Many thanks for your post, to make this easier to read I will split my responses into 3.

1) Percentage of active patients that have an email.

As you highlighted this will be 2 different queries, we need to firstly create a query for any patient who has had a completed appointment in the past 2 years

1. Load into the patient selector field
2. Select query wizard
3. New query > start > next
4. Leaving the top box ticked, select specify attributes
5. In the fields section scroll down till you see the bold heading appointments
6. Highlight last completed appt date and press add
7. Change the operator to is later than or equal to
8. Press the 3 dots under value and change the option to relative to current time
9. Select 2 years and press OK.
10. Press OK and finish, then save.

The above will show you all patients that have, if you run the query the number will be at the bottom right. Make a note of this number

We then need to add the email field into a different query, follow the below.

1. Find the query created above, highlight and press make copy
2. Highlight and rename to what you want to call the query, ie. add with email
3. Select edit and next
4. Leaving the top box selected select specify attributes
5. In the fields section scroll down till you see the bold heading addresses
6. Highlight email and press add
7. In the value column, double click and add the @ symbol
8. Press OK and finish, then save

This is then only including patients who have been seen in the past 2 years with an email address, use the number at the bottom right and work out the percentage from there.

Regards

Matt

[matthewbridge]

Hi @edcoppen,

Onto the 2nd query.

2) Active patients registered to a dentist.

1. Load into the patient selector field
2. Select query wizard
3. New query > start > next
4. Leaving the top box ticked, select specify attributes
5. In the fields section under the general section, scroll down till you see user
6. Highlight user and press add
7. From here you have the option, if you only want 1 dentist then find them under value
8. If however you want to run this for all dentists, then tick the box that says ask. The system will then ask you each time you run the query which dentist
9. Go back to the fields section and scroll down till you see the bold heading appointments
10. Highlight last completed appt date and press add
11. Change the operator to is later than or equal to
12. Press the 3 dots under value and change the option to relative to current time
13. Select 2 years and press OK.
14. Press OK and finish, then save

The above will then show you a list of patients registered to that dentist who have had an appointment in the last 2 years.

Regards

Matt

[matthewbridge]

Hi @edcoppen,

In regards to the 3rd query, I am reading this as future appointments. If this isnt the case please let me know.

3) List of patients booked in for exam, but registered to another dentist

Please create the query below:

1. Load into the patient selector field
2. Select query wizard
3. New query > start > next
4. Leaving the top box ticked, select specify attributes
5. In the fields section under the general section, scroll down till you see user
6. Highlight user and press add
7. From here you have the option, if you only want 1 dentist then find them under value
8. If however you want to run this for all dentists, then tick the box that says ask. The system will then ask you each time you run the query which dentist (Recommended)
9. Press OK
10. Tick the 4th option, that have had an appointment and press next
11. Tick with (user) and press specify criteria
12. From here you have the option, if you only want 1 dentist then find them under value
13. If however you want to run this for all dentists, then tick the box that says ask. The system will then ask you each time you run the query which dentist (Recommended)
14. Tick the last option for (treatment type) and press specify criteria
15. In the value, find the appointment type you are looking for.
16. Press OK and finished, then save

The query will then each time ask you to specify the registered dentist and then the dentist they are seeing in the future. Ie. registered to dentist A but seeing dentist B.

Regards

Matt

[edcoppen]

Thank you for your help Matt!