How to count how many clients result in how many appointments

[Andy31421]

I’m a psychotherapist and my clients come at irregular intervals of 2 - 3 - 4 weeks. I want to know how many current clients I need to have, to result in an average of say 15 appointments in each week. Otherwise I have too many or too few clients.

A “current client” is defined as someone who currently has an appointment booked, whether a week or a month in the future.

My booking system gives me a list of (i) client names (ii) the date of each appointment (iii) THE DATE EACH APPOINTMENT WAS BOOKED - this is important.

I can trivially order these in date order and count how many appointments I had in any one week. Then, in the same date ordered list, I need to count how many names have a future appointment booked, which was booked IN or BEFORE the week in question. These are appointments for CURRENT clients. Any appointment booked AFTER the current week will be for someone who became a new client after that week, rather than being a “current client” during that week (anyway that is an accurate assumption.)

So I need to list the appointments by date; take any one given week; count the appointments in that week; then go down the list of later appointments and eliminate any booked after the given week ie to leave behind only appointments booked by clients current in the given week; then count the number of unique names in the reduced list. And make it simple enough to do repetitively.

I end up with 3 columns: the date of the week, the number of appointments in that week, and the number of current clients in that week.

The bit I can do is to list in date order! Any help with any part of the rest would be greatly appreciated.

Many thanks

Andrew, Bristol UK

[Villeroy]

90% of spreadsheet users in trouble try to misuse a spreadsheet as database and fail because a spreadsheet is a poor database at best.

The attachment is a hastily made database, not yet ready made since I know next to nothing about your data. The overall usability could be improved with some macro code, however it may be useful as is.
The user interface of a Base database is made of input forms and reports.
I filled the database with dummy persons and random appointments generated on a spreadsheet.

The clients form lists all clients in alphabetic order (can be overridden) and you can filter the list by name and age. There is a multi-line input box for notes regarding the selected client.
A subform shows the appointments of the currently selected client together with a note box regarding the selected appointment.

The appointments form lists all appointments in descending order (latest on top) and you can filter the list by date range and by client. There is also a list of overlapping appointments that should be fixed.

The form named “New Appointment” should be useful when you want to make a new appointment. On the left there is a calendar of future dates (including today, Saturdays and Sundays hidden) with a second column counting the existing appointments for that day.
Select a date and the white grid shows the appointments of the day.
Select the client from the yellow clients listbox. You may type the surname in order to find the client by name.
If the listbox does not contain the client, enter the new client in the green boxes, save the client, focus the yellow clients listbox again and click the “Update” button. Now the new client should appear in the listbox.

I added a simple report which presents current week’s appointments in printable form.

Appointments.odb (166.1 KB)

[Andy31421]

Dear @Villeroy,
Thank you for your time in doing this, but this solves a problem I don’t have, not the problem I do have. I already have an excellent booking system to make appointments. This outputs a spreadsheet, and I need to extract non-obvious data from the spreadsheet. As far as I can tell, the database sets up appointments, but it does not make the complex analysis I need.
Regards
Andrew

[Villeroy]

@Andy31421

As far as I can tell, the database sets up appointments, but it does not make the complex analysis I need.

I’m confident, it could because it is not a spreadsheet. I just forgot to add the automatic booking date to my appointments table. Your excellent booking system should be able to do this analysis. Did you ask the vendor of that system?
Whatever spreadsheet your booking system generates as a report, we can not see it. Since your spreadsheet report contains highly confidential data, you should make the document anonymous before uploading. Reduce the whole thing to a couple of typical records and replace text data, such as names and addresses, with something else (A, B, C, Charlie, Jenny etc.) keeping numbers and dates intact. Numbers and dates with meaningless text is meaningless.

@Andy31421

So I need to list the appointments by date; take any one given week; count the appointments in that week; then go down the list of later appointments and eliminate any booked after the given week ie to leave behind only appointments booked by clients current in the given week; then count the number of unique names in the reduced list. And make it simple enough to do repetitively.

With a properly set up Excel sheet (very rare thing nowadays) it should be possible having an appropriate set of dummy data at hand.

[Villeroy]

Appointments per future week. I’m afraid, I don’t get the role of the booking dates.
appointments.ods (58.4 KB)