By improvement, I suppose you have performance in mind because your queries are fairly basic and not hard to understand.
I have had a look at your SQL fiddle and your tables have no indexes. Absent any other optimization mechanism the database engine will have to perform a full table scan to fetch the wanted rows. Thus, I don't expect much difference between ...
You can greatly simplify things as well as wildly improve performance by using something like the following...
@start_date date = '2020-09-24',
@interval_in_days int = 30,
@appt_count int = 100;
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), -- 10
cte_n2 (n) AS (SELECT 1 FROM cte_n1 ...
You'll have to take this with a huge grain of salt, because the following advice is for generic, standards-abiding SQL - which Access is not.
In regular SQL, this is as simple as:
select dupes.id1, ST1.nam
group by id1
having count(*) > 1
join ST1 on ST1.id1 = dupes.ST1
order by dupes.id1;
Another way to ...