Making One Record from Two Records


#1

Thanks in advance for any advice.

We are counseling agency that sees couples and individuals.
I want to count how many sessions we do by practitioner. and how many clients we see.

We have a table called Appointments that has:
Appointment ID
Patient ID
Start date and time of Appt
Prractitioner
Invoice ID
etc

We have a table called Clients that has:
Patient ID
that is linked to the appointments and invoices table

We have Invoices Table
Invoice ID
Patient ID
Appointment ID
Date Paid etc.

Step 1: I want to count how many sessions we do by practitioner.

The complication comes in that if we see a couple together, there are two records in the Appointment table for the session (start time + Practitioner) for that particular (one) session - one appointment record for one partner (Patient ID) and one for the other partner (Patient ID). So that is only one session - but two appointment records - two different Appointment IDs.

I am thinking I could count unique “start time + Practitioner” in a filter - but can I use that filtered-sum in another calculation - say in a different table?

I am also thinking I would like to count number of clients by practitioner - but to count the ‘couple’ (two different patient IDs) as one client as well as when one of the clients comes alone. I was thinking I could concatenate the Patient IDs somehow if the startdate-time and practitoners were the same…

Is this beyond Airtable? Or might I need a paid consultant?

Again, thanks in advance…