Summary Report

[summer76]

I need to create a summary report detailing the # records closed within 1-3 days, 4-5 days, etc. and open for still open and their aging...sorted by type and region.

I've tried several count queries but I am not getting accurate numbers. I've even created a query that if the data in the column meets 1-3, then move a Yes, and so on. But then I have the problem of getting all these counts into a report. Still no luck. How can I do this summary report?

Thanks!

 

[lespaul]

what determines if a record is "still open"? Number of days since when?

Can you provide your table structure and some sample records and your expected results from that data?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[summer76]

Sample Data (I would really like to be able to provide a report based on this data):

Region Type Closed Aging Open Aging
East UEC 2 0
East UEC 7 0
East UEC 1 0
East EUR 3 0
East EUR 31
East EUR 3

The report should be like this (with several timeliness breakdowns & several regions):

Region Type Closed in 1-3 Closed in 4-10 Open >30
East UEC 2 1 0
East EUR 2 0 1

Thanks.

 

[lespaul]

something like:

SELECT Region, Type, SUM(iif(Closed BETWEEN 1 and 3, 1, 0)) As [Closed in 1 - 3], SUM(iif(Closed BETWEEN 4 and 10, 1, 0)) As [Closed in 4 - 10], SUM(iif(Aging > 30, 1, 0)) As [Open > 30] FROM TableName
GROUP BY Region, Type

copy and paste to the SQL view, fix the TableName (or replace with the query name that gives you the sample data) and run it!

HTH

Leslie

 

[summer76]

Okay, I've pasted and modified the query to this:

SELECT Region, Type, SUM(iif([PSU Data_TAT]![Closed Aging] BETWEEN 1 and 3, 1, 0)) As [Closed in 1-3], SUM(iif([PSU Data_TAT]![Closed Aging] BETWEEN 4 and 5, 1, 0)) As [Closed in 4-5], SUM(iif([PSU Data_TAT]![Closed Aging] BETWEEN 6 and 10, 1, 0)) As [Closed in 6-10], SUM(iif([PSU Data_TAT]![Closed Aging] BETWEEN 11 and 30, 1, 0)) As [Closed in 11-30], SUM(iif([PSU Data_TAT]![Open Aging] > 30, 1, 0)) As [Open > 30] FROM PSUData_TAT
GROUP BY Region, Type

Then I get a pop up box asking me for a number for closed and open and depending on what I tell it, it puts #'s in the column (the #'s are the same no matter what column the #'s are in). I don't know if I want a sum...I need a count of how many records meet that criteria of 1-3 days etc.

 

[lespaul]

I know you want a count, but this set up mimics that, it adds 0 if the condition is not met and adds 1 if the condition is met - acts like a count.

Is PSU Data_TAT a table or a query? Does the field Closed Aging exist in the table/query? You usually get the parameter prompt when the field name that you have in the query doesn't exist in the source.

Leslie

 

[PHV]

Could you please post the SQL code that produced the following ?[tt]
Region Type Closed Aging Open Aging
East UEC 2 0
East UEC 7 0
East UEC 1 0
East EUR 3 0
East EUR 31
East EUR 3[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[summer76]

This is the code that produced my table. The table above was only to provide a sample. The number of days in the closed & open aging are a result of a calculation from a begin date to either a closed date (or today's date if still open). The summary report is supposed to say that X of total was complete in 1-3 days; X of total complete in 4-5, etc.

SELECT [PSU Data_count days].[Date of EUR/UEC request], [PSU Data_count days].Type, [PSU Data_count days].Region, [PSU Data_count days].[Provider TIN], [PSU Data_count days].[Provider Name], [PSU Data_count days].[Provider State], [PSU Data_count days].[Date 1st request sent to PSU], [PSU Data_count days].[Date 2nd request sent to PSU], [PSU Data_count days].[Date 3rd request sent to PSU], [PSU Data_count days].[Date Completed], [PSU Data_count days].[Closed Aging], IIf([PSU Data_count days]![Open Aging]="","0",([PSU Data_count days]![Open Aging])) AS [Open Aging], [PSU Data_count days].Notes INTO [PSU Data_fill blanks]
FROM [PSU Data_count days];

 

[lespaul]

Ok, is [PSU Data_count Days] another query?
What's the SQL for that?

 

[PHV]

And what about this ?
SELECT Region, Type
, Sum(IIf([Closed Aging] BETWEEN 1 And 3, 1, 0)) As [Closed in 1-3]
, Sum(IIf([Closed Aging] BETWEEN 4 And 5, 1, 0)) As [Closed in 4-5]
, Sum(IIf([Closed Aging] BETWEEN 6 And 10, 1, 0)) As [Closed in 6-10]
, Sum(IIf([Closed Aging] BETWEEN 11 And 30, 1, 0)) As [Closed in 11-30]
, Sum(IIf(Val([Open Aging] & '') > 30, 1, 0)) As [Open > 30]
FROM [PSU Data_count days]
GROUP BY Region, Type

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[summer76]

I had made another query after count days but the data is the same and same format (columns).

 

[PHV]

BTW, the reason for the parameters popup:
... SUM(iif([PSU[highlight] [/highlight]Data_TAT]![Open Aging] > 30, 1, 0)) As [Open > 30] FROM PS[!]UD[/!]ata_TAT

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[summer76]

Thanks to the both of you! It is working with the last code from PHV - the other will probably work too now that there is a naming error!