Dynamic Crosstab Report for Multiple Crosstab Queries

[beacon5]

Hi everybody,

Here's the stats:
Access 2003
Split database
Linked tables
All subdatasheets on the back-end set to [None]

I'm in a jam that I'm hoping I can get resolved by Wednesday. In my current application, I have used 4 select queries in order to get the results for one crosstab query, and have used a couple more select queries for the results of the other crosstab query.

I'm trying to set this up dynamically using Microsoft's suggestion for a dynamic crosstab, but I've run into a couple of issues.

First, I can't find a way to get the data for both the crosstab queries into the same report without using DLookup for the 13 or so columns the report will have. As you can imagine, this is making things very slow.

Second, I think the fact that the Microsoft suggestion uses the DAO recordset method has also caused the report to load very slowly...and when I say slow, I mean 10-15 minutes slow.

I've seen Duane Hookum's (sp?) method, but I'm not quite sure how to apply it to my current situation. I have a distinct set of column headings currently, but I'd like to develop this with the idea that the current column headings may need to change at some point.

I'm sure I will need to post additional information, but I only want to post what's necessary so as to prevent this post from being a million, billion pages long.

Thanks for the help...

 

[dhookom]

I'm not sure what method of mine you are referring to. The best solution should be the sample crosstab at Rogers Access Library.

I hate to say this but it would help if you provided more details about your data and specifications.

Duane
Hook'D on Access
MS Access MVP

 

[beacon5]

You're exactly right Duane. The sample at Rogers Library was the method of yours I was referring to.

My next post will have a snapshot of the results of the documenter. I'm going to need to upload it to a server that I can't access from work.

Pages 1-51 of the snapshot shows the query definitions. The following queries are the ones to pay special attention to:
- qryTimeliness
- qryTimelinessFormByPatient
- qryTimelinessFormCount
- qryTimelinessSummary
- qryTotalsByProgram

Pages 52-59 shows the form used to set the date range parameters.

Pages 60-101 shows the report that uses the dynamic crosstab example from Microsoft's support website.

Pages 102-105 shows a couple of code modules that I've included.

And pages 106-109 shows the relationships between the tables.

My goal is to show the number of patient charts that have a Form (Row) that wasn't completed within the timeframe for each Program (Column) for a date range that is entered on frmReportCompliance and passed to one of the queries.

I'm also trying to read in the total number of charts for the same date range so I can determine what percentage of charts had a form that wasn't completed in the timeframe.

My end result should be something like the following:

Program 1 Program 2 Program 3 Total
Form 1 7 4 3 14
Form 2 0 2 1 3
Form 3 12 6 9 27

Not Timely Forms 19 12 13 44
Total Charts 20 24 33 77
Percentage 95% 50% 39% 57%

The forms will be based on the qryFormsCCC and the programs could be static, but I would rather read them in from qryPrograms.

If this is clear as mud, I will try to clarify it as much as possible.

Thank you again for your help!

 

[MajP]

Dhookum's example of dynamic queries is pretty impressive, and beyond my abilities. So if you can get that to work for you, that is the correct approach. However, this may be relational database blasphemy, but sometimes I just find it easier to write the code to produce output like this.

Something like.
1) Read some table and determine the programs
2) create a new tableDef
3) add a description field, field for each program, and a total field
4) populate the records of the table through ado or dao by reading the queries and doing some calculations.

In truth this approach will often run way faster than complex cross tab queries and dlookups.

 

[dhookom]

I was hoping to get some idea of what your data looks like. Can't you get a simple query that has columns for "Form", Program, and some kind of count or other aggregate?

Duane
Hook'D on Access
MS Access MVP

 

[beacon5]

I was going to post the documenter last night, but I can't attach a local file. I don't have access to the other server I mentioned in my previous post.

 

[dhookom]

Why can't you just type the significant information into a reply? The significant tables/fields would be related to:

My goal is to show the number of patient charts that have a Form (Row) that wasn't completed within the timeframe for each Program (Column) for a date range that is entered on frmReportCompliance and passed to one of the queries.

I'm also trying to read in the total number of charts for the same date range so I can determine what percentage of charts had a form that wasn't completed in the timeframe.

Duane
Hook'D on Access
MS Access MVP

 

[beacon5]

The problem with this is that we record every error that's wrong with patients chart. When I go to get the count/aggregate, I have to look at all the forms that were done in that chart, find out if one of the errors is an error related to timeliness, and then count that form only once for a patient's chart (even though in some instances there may be multiples).

Then I have to take the total number of forms with an error on it and give the percentage based on the program.

Will the SQL for the queries work?
qryTimeliness:

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
ORDER BY qryPatientDetail.PatientDetailID, qryPatientDetail.DischargeDate;

qryTimelinessFormByPatient:

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
WHERE (((IIf([count]>0,'Not Timely','Timely'))="Not Timely"))
ORDER BY qryPatientDetail.PatientDetailID;

qryTimelinessFormCount:

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;

qryTimelinessSummary:

PARAMETERS [Forms]![frmReportCompliance]![BeginningDate] DateTime, [Forms]![frmReportCompliance]![EndingDate] DateTime;
TRANSFORM Count(qryTimelinessFormByPatient.Result) AS CountOfResult
SELECT qryForms.FormName
FROM qryForms LEFT JOIN qryTimelinessFormByPatient ON qryForms.FormName = qryTimelinessFormByPatient.FormName
WHERE (((qryTimelinessFormByPatient.DischargeDate) Between [Forms]![frmReportCompliance]![BeginningDate] And [Forms]![frmReportCompliance]![EndingDate]))
GROUP BY qryForms.FormName
PIVOT qryTimelinessFormByPatient.ProgramInitials In ("AFP","BMTP","CAP","Competency","EEP","GPP","MDU","RIDR","SBP","SLRP");

Does this help?

 

[dhookom]

If you use my solution, you would append each ProgramInitials value to a record in a table and assign a letter from A to J (or whatever). Your crosstab would then use this table joined to the qryTimelinessFormByPatient.ProgramInitials to allow using the letters as column headings.

Duane
Hook'D on Access
MS Access MVP

 

[beacon5]

Does the table that I'm appending the ProgramInitials to need to be new and standalone table?

I've got the Programs associated with the records already, so I thought I would just add an additional column in the Programs table that had the initials in it if that would work.

 

[dhookom]

The letter could be updated in your existing table. My solution actually included the initials, a letter, and a level. This allowed for more columns than could be displayed across in a report.

Duane
Hook'D on Access
MS Access MVP