Missing rows in Crosstab

[beacon5]

Hi everybody,

[Access 2003]

I've got a large database that I'm using to track timeliness for a particular set of forms (that aren't static and will indeed change in the future) in a patient's chart. The forms I'm looking at are a selection indicated be yes/no field on the forms table (identified as 'CCC').

I've got multiple queries set up that show me the data that I need, but I noticed that for small date ranges in the parameters it will exclude forms that don't have timeliness issues (no timeliness issues for a form is a good thing, but not having them show up on the report isn't).

I would like to add these forms, but I'm having a lot of trouble. I know it involves LEFT JOINS, but I've currently got the query with all the row values left joined to the query with the data.

I'm attaching the SQL for each of the 4 queries:

qryTimeliness - shows all records for the 'CCC' forms

SELECT DISTINCT 
    qryPatientDetail.PatientDetailID, qryPatientDetail.Episode, 
    qryPatientDetail.DischargeDate, NZ([FormName]) AS Form, 
    qryForms.FormName, qryPrograms.ProgramInitials, 
    IIf(([ComplianceCalculationConsideration]=True),True,False) AS CCC, 
    qryEpisodeDetail.CorrectedDate, 1 AS TotalCharts, 
    qryDeficiencyType.DeficiencyType, 
    IIf([deficiencytype]="Not Done in Timeframe *UNC",1,0) AS Timeframe

FROM 
    (qryForms RIGHT JOIN ((qryPatientDetail LEFT JOIN qryEpisodeDetail 
    ON qryPatientDetail.PatientDetailID = qryEpisodeDetail.PatientDetailIDFK) 
    LEFT JOIN qryDeficiencyType ON 
    qryEpisodeDetail.DeficiencyIDFK = qryDeficiencyType.DeficiencyTypeID) 
    ON qryForms.FormID = qryEpisodeDetail.FormIDFK) 
    LEFT JOIN qryPrograms ON qryPatientDetail.ProgramIDFK = qryPrograms.ProgramID

WHERE 
    (((IIf(([ComplianceCalculationConsideration]=True),True,False))=True))
ORDER BY 
    qryPatientDetail.PatientDetailID, qryPatientDetail.DischargeDate;

qryTimelinessFormCount - shows a total for the timely/not timely forms for each patient

SELECT DISTINCT 
    qryForms.FormName, qryTimeliness.PatientDetailID, 
    Sum(qryTimeliness.Timeframe) AS [Count]

FROM 
    qryForms RIGHT JOIN qryTimeliness 
    ON qryForms.FormName = qryTimeliness.FormName

GROUP BY 
    qryForms.FormName, qryTimeliness.PatientDetailID;

qryTimelinessFormByPatient - shows the 'Not Timely' forms for each patient along with the unit they reside on

SELECT DISTINCT 
    qryPatientDetail.PatientDetailID, IIf([count]>0,'Not Timely','Timely') AS Result, 
    qryPrograms.ProgramInitials, qryPatientDetail.DischargeDate, 
    qryTimelinessFormCount.FormName

FROM 
    (qryPrograms INNER JOIN qryPatientDetail 
    ON qryPrograms.ProgramID = qryPatientDetail.ProgramIDFK) 
    LEFT JOIN qryTimelinessFormCount 
    ON qryPatientDetail.PatientDetailID = qryTimelinessFormCount.PatientDetailID

GROUP BY 
    qryPatientDetail.PatientDetailID, IIf([count]>0,'Not Timely','Timely'), 
    qryPrograms.ProgramInitials, qryPatientDetail.DischargeDate, qryTimelinessFormCount.FormName

HAVING 
    (((IIf([count]>0,'Not Timely','Timely'))="Not Timely"))

ORDER BY 
    qryPatientDetail.PatientDetailID;

qryTimelinessSummary - and last, but not least, the crosstab (I have parameters in the query so that I can show the date range selected on the final report)

PARAMETERS 
    [Forms]![frmReportCompliance]![BeginningDate] DateTime, 
    [Forms]![frmReportCompliance]![EndingDate] DateTime;

TRANSFORM 
    Count(qryTimelinessFormByPatient.Result) AS CountOfResult

SELECT 
    qryFormsCCC.FormName, 
    Forms!frmReportCompliance!BeginningDate AS BeginningDate, 
    Forms!frmReportCompliance!EndingDate AS EndingDate, 
    Count(qryTimelinessFormByPatient.Result) AS [Total by Form]

FROM 
    qryFormsCCC LEFT JOIN qryTimelinessFormByPatient 
    ON qryFormsCCC.FormName = qryTimelinessFormByPatient.FormName

WHERE 
    (((qryTimelinessFormByPatient.DischargeDate) 
    Between [Forms]![frmReportCompliance]![BeginningDate] 
    And [Forms]![frmReportCompliance]![EndingDate]))

GROUP BY 
    qryFormsCCC.FormName, 
    Forms!frmReportCompliance!BeginningDate, 
    Forms!frmReportCompliance!EndingDate, 
    Forms!frmReportCompliance!BeginningDate, 
    Forms!frmReportCompliance!EndingDate

PIVOT 
    qryTimelinessFormByPatient.ProgramInitials In 
    ("AFP","BMTP","CAP","Competency","EEP","GPP","MDP","RIDR","SBP","SLRP");

I'm sorry for the clutter, but I appreciate any help that anyone can throw my way. I'm completely stumped and I have a deadline to submit this by the end of today.

I can send a picture of the relationships between my tables if that would help.

Thanks...

 

[lameid]

What data exactly is missing? Is it in one query and not the other? If so which query shows it?

 

[beacon5]

The forms table has a field called 'CCC' in it and only 9 of the 60 forms have a check mark in this field. When I run the crosstab, I'm only showing values for 'CCC' forms, but I'm only showing for 'CCC' forms that are don't have a timeliness issue.

I need all 9 forms to show up on the final query with null/zero values if there is nothing to report.

So, some sample data might be:
Forms
1. Admission
2. Discharge
3. Transfer
4. Consult
5. Referral
6. Observation
7. Assessment
8. Progress Note
9. Treatment Plan

On the crosstab, this is an example of what is showing:
Form Unit1 Unit2 Unit3
1. Admission 1 2
2. Discharge 4 6
3. Transfer 2 3 1
4. Consult 9 1 1
8. Progress Note 3 4

As you can see, I'm missing form #7 and form #9. I need these forms to show up with null values and I need them to show up on the query so that my report will generate based off of the 'CCC' items in the forms table and not because I created static fields on the report.

The majority of the work the queries are doing is to take the forms that are entered (there may be multiple for one patient, but if one isn't timely then the chart isn't considered timely), check for timeliness, count the number of charts that aren't timely, and then return the value.

Does that make sense?

 

[lameid]

If I am inferring everything correctly your culprit is in qryTimelinessFormByPatient. The follwoing criteria is limiting your reults.

HAVING 
    (((IIf([count]>0,'Not Timely','Timely'))="Not Timely"))

 

[beacon5]

The code you showed does limit my results, which is part of the problem.

I need the results for the values on the crosstab limited to 'Not Timely' records, but I need to list ALL 'CCC' forms whether they were timely or not. For form #7 and #9 above, I need to find a way to include these forms, that have no timeliness issues, in the report despite the limitations on the records.

Essentially, I need the forms to show up regardless of the values in the crosstab, which is what is currently dictating which forms show up.

I found a similar problem on the following site, but I'm completely lost on how to implement it with in my database.
->

http://ewbi.blogs.com/develops/2007/04/expanding_an_ac.html

 

[PHV]

The following criteria defeats the LEFT JOINN:
WHERE
(((qryTimelinessFormByPatient.DischargeDate)
Between [Forms]![frmReportCompliance]![BeginningDate]
And [Forms]![frmReportCompliance]![EndingDate]))

Workaround:
Put the date criteria in the relevant query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[beacon5]

Hi PHV,

I just want to make sure I'm understanding you correctly. If I put the discharge parameters in one of the earlier queries, my results should return properly?

 

[lameid]

Yes, when you put criteria on the outside side of an outer join, you effectively make it an inner join unless you include criteria of OR IS NULL.

Putting the criteria inside of the underlying query prevents this inner join effect from happening.

 

[beacon5]

You guys (or girls) are awesome, plain and simple. Thanks you a million billion times for your help.

Why does that do that and are there any resources that you know of that sheds some light on these tricks?

 

[PHV]

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

The short answer is that the join is processed before the where clause.