Combining 2 queries

[Benno2805]

I have (with the help of this forum) created a query that takes date of birth and puts it in to an age range (the criteria for which is held in a table).

SELECT PD.[Patient SID], PD.[Date of birth], AR.Age_Range
FROM tbl_Patients AS PD, tbl_AgeRange AS AR
WHERE (((DateDiff("d",[PD].[Date of birth],Date())) Between [AR].[Min_Age_Days] And [AR].[Max_Age_Days]));



I also have another query returning 'Active Patients' based on when they joined and when their last appoinment was.

SELECT tbl_Patients.[Patient SID], tbl_Patients.Gender, tbl_Patients.[Total invoiced], tbl_Patients.[Payment plan], tbl_Patients.[Created at], Min(tbl_Appointments.[Start date]) AS [First Appt Date], Max(tbl_Appointments.[Start date]) AS [Last Appt Date], tbl_Patients.[Date of birth], [qry_Age in days and Age Range].Age_Range
FROM [qry_Age in days and Age Range] INNER JOIN (tbl_Patients LEFT JOIN tbl_Appointments ON tbl_Patients.[Patient SID] = tbl_Appointments.[Patient SID]) ON [qry_Age in days and Age Range].[Patient SID] = tbl_Patients.[Patient SID]
WHERE (((tbl_Patients.[Created at])>=44926-1100 And (tbl_Patients.[Created at])<=44926) AND ((tbl_Patients.[Prevent appointment booking])<>-1)) OR (((tbl_Patients.[Prevent appointment booking])<>-1) AND ((tbl_Appointments.[Start date])>=44926-1100 And (tbl_Appointments.[Start date])<=44926))
GROUP BY tbl_Patients.[Patient SID], tbl_Patients.Gender, tbl_Patients.[Total invoiced], tbl_Patients.[Payment plan], tbl_Patients.[Created at], tbl_Patients.[Date of birth], [qry_Age in days and Age Range].Age_Range;

My question is; Is there a way of displaying Age Range in the second query without referring to the first query?
The Age range table is simply set out as this,

Min_Age_Days	Max_Age_Days	Age_Range
0​	6570​	<18
6570​	9490​	18-25
9490​	14965​	26-40
14965​	20440​	41-55
20440​	29565​	56-80
29565​	38325​	80+
44000​	50000​

 

[Ranman256]

without using the 2nd query, just use a Age formula, but you still need to join Q1 to the person table to get the birthdate.

 

[MajP]

Sure, but WHY? Simply include all the work you already done for the first query in the second query. Seems weird to want to throw away the work you arleady did.

 

[plog]

Lots of issues I see:

1. Using days to put people into Age ranges. It's just an inaccurate method. For anyone over 31 you are going to be off by at least a week--and people older even more. There are Age functions out there you can use to convert someone's birthdate to an age, then you can use a table to put the year into a range.

2. Non-join query. Formally know as a Cartesian product:

FROM tbl_Patients AS PD, tbl_AgeRange AS AR
WHERE (((DateDiff("d",[PD].[Date of birth],Date())) Between [AR].[Min_Age_Days] And [AR].[Max_Age_Days]));

Instead of the WHERE you should use a JOIN. Let's assume you've created a query to get the age of all your patients and changed your range table to use and have a year in age for your patient, the above code would look like this instead:

FROM qry_Patients LEFT JOIN tbl_AgeRange ON  PatientAge > MinAge AND PatientAge <= MaxAge

3. Ineffective LEFT JOIN. When you apply criteria to a field in a data source that has been LEFT JOINed you effective change that LEFT JOIN to an INNER JOIN:

FROM [qry_Age in days and Age Range] INNER JOIN (tbl_Patients LEFT JOIN tbl_Appointments...
WHERE ... AND ((tbl_Appointments.[Start date])>=44926-1100 And (tbl_Appointments.[Start date])<=44926))

tblAppointments is no longer LEFT JOINed because you applied criteria to Start date. LEFT JOIN lets nulls through, but if you apply criteria to a field then that field must contain a value that meets the criteria--and nulls will not meet that criteria.

4. Aggregating a portion of the data in the main query. Your main query has 3 data sources and you are only aggregating data from 1 (tbl_Appointments), you should do that in a sub query (especially since you want to LEFT JOIN it). You should build a subquery on just tbl_Appointments and do everything you want with it in there (including your criteria in #3). Then you should LEFT JOIN that query it to your main query. This will avoid you having to GROUP BY in the main query. Makes debugging any issues with the appointment data easier and the main query as well.

So to recap, let's call your big query above BigQ. To get all the data you want in BigQ you should build 3 queries:

sub_Patients - SELECT all fields from Patients you need, calculate the age in years for each patient, apply all the criteria in BigQ that uses patient fields.

sub_Appointments - SELECT all fields from Appointments you need, apply criteria that's now in BigQi, aggregate as needed

NewBigQ - based on sub_Patients, sub_Appointments and your new AgeRange table that uses years and JOINed as I showed above to sub_Patients.

 

[ebs17]

I don't really think much of the calculations for days, especially since they don't really take into account different numbers of days in years because of the leap days. Using correct date values is more readable and certainly closer to practice.

I have moved the aggregation of appointments to a subquery. This eliminates the grouping aria over the many fields. I have added the age calculation in years, in the additional switch function you can differentiate directly into age groups. As you can see, the function is not yet complete => please complete it yourself. At this point (SELECT part, use of simple content) the calculations are not (performance) problematic.

Check the following suggestion:

SELECT
   P.[Patient SID],
   P.Gender,
   P.[Total invoiced],
   P.[Payment plan],
   P.[Created at],
   A.[First Appt Date],
   A.[Last Appt Date],
   A.[Date of birth],
   DateDiff("yyyy", A.[Date of birth], Date()) + (Format(Date(), "mmdd") < Format([Date of birth], "mmdd")) AS Age,
   Switch(Age>=0 AND Age<18, "<18", Age>=18 AND Age<26, "18-25", …) AS Age_Range
FROM
   tbl_Patients AS P
      LEFT JOIN
         (
            SELECT
               [Patient SID],
               MIN([Start date]) AS [First Appt Date],
               MAX([Start date]) AS [Last Appt Date]
            FROM
               tbl_Appointments
            WHERE
               [Start date] >= #1/1/2020#
                  AND
               [Start date] < #1/1/2023#
            GROUP BY
               [Patient SID]
         ) AS A
         ON tbl_Patients.[Patient SID] = A.[Patient SID]
WHERE
   P.[Created at] >= #1/1/2020#
      AND
   P.[Created at] < #1/1/2023#
      AND
   (
      P.[Prevent appointment booking] = False
         OR
      P.[Prevent appointment booking] IS NULL
   )