Ignore suppressed records in crosstab

Question

My report shows only the latest diagnosis per patient based on their date_of_diagnosis - all other records are suppressed:

I summarize by diagnosis and age group in a crosstab. Crosstabs evaluate before printing, so any attempts to suppress, share variables, or summarize happen after the crosstab populates. This means Total in Each Age Group is correct, because each patient only has one age - but if a patient has more than one diagnosis, even if they're suppressed, they get counted multiple times:

I absolutely must use a crosstab for this due to the large number of diagnoses and age groups involved. How can I get the crosstab to ignore suppressed records? Or if I need to use a custom SQL Command table, how can I rewrite the existing SQL to ignore obsolete records?

---

Crystal's auto-generated SQL (through ODBC):

SELECT "Codes"."diagnosis_code",
       "Codes"."diagnosis_value",
       "Codes"."PATID",
       "Codes"."FACILITY",
       "Codes"."EPISODE_NUMBER",
       "Record"."date_of_diagnosis"

FROM   "SYSTEM"."Codes" "Codes",
       "SYSTEM"."Entry" "Entry",
       "SYSTEM"."Record" "Record"

WHERE  "Codes"."DiagnosisEntry"="Entry"."ID" AND
       "Codes"."EPISODE_NUMBER"="Entry"."EPISODE_NUMBER" AND
       "Codes"."FACILITY"="Entry"."FACILITY" AND
       "Codes"."PATID"="Entry"."PATID" AND
       "Entry"."DiagnosisRecord"="Record"."ID" AND
       "Entry"."EPISODE_NUMBER"="Record"."EPISODE_NUMBER" AND
       "Entry"."FACILITY"="Record"."FACILITY" AND
       "Entry"."PATID"="Record"."PATID"

Answer 1

Building off of Muffaddal Shakir's answer, I was able to write this query to perform the correct filter:

SELECT "Codes"."PATID",
       "Codes"."diagnosis_code",
       "Codes"."diagnosis_value",
       "Codes"."FACILITY",
       "Codes"."EPISODE_NUMBER",
       "Record"."date_of_diagnosis"

FROM "SYSTEM"."codes" "Codes",
     "SYSTEM"."entry" "Entry",
     "SYSTEM"."record" "Record"

WHERE "Codes"."DiagnosisEntry"="Entry"."ID" AND
      "Codes"."EPISODE_NUMBER"="Entry"."EPISODE_NUMBER" AND
      "Codes"."FACILITY"="Entry"."FACILITY" AND
      "Codes"."PATID"="Entry"."PATID" AND
      "Entry"."DiagnosisRecord"="Record"."ID" AND
      "Entry"."EPISODE_NUMBER"="Record"."EPISODE_NUMBER" AND
      "Entry"."FACILITY"="Record"."FACILITY" AND
      "Entry"."PATID"="Record"."PATID"

AND "Record"."date_of_diagnosis" = (   
    SELECT MAX("Record2"."date_of_diagnosis")

    FROM "SYSTEM"."entry" "Entry2",
         "SYSTEM"."record" "Record2"

    WHERE "Entry2"."DiagnosisRecord"="Record2"."ID" AND
          "Entry2"."EPISODE_NUMBER"="Record2"."EPISODE_NUMBER" AND
          "Entry2"."FACILITY"="Record2"."FACILITY" AND
          "Entry2"."PATID"="Record2"."PATID" AND
          "Record"."PATID"="Record2"."PATID"
)

The key differences being:

1. The subquery uses unique aliases from the main query.
2. The last line "Record"."PATID"="Record2"."PATID" - Without this, the query only pulls back one diagnosis (the latest one in the whole system.) But now it checks for the latest diagnosis per person.