Crosstab Query with between Date() and date()-5 as column heading (1 Viewer)

[michaeljryan78]

I am really perpplexed with this one. Right now I have 4 separate queries running from one table (counsedetails) that show the status of a request (status) and the date that the status changed [status change date] so I know a count of what claims (claimnumber) has been pending over 5 days, 6-10 days, 11-15 days and 16+ days, each range being in a seaprate query.

I am looking to put this all together into a crosstab where the row heading is the status and the column headings are 1-5 days, 6-10 days, ect. and the count fills in.

Can anyone please help?

 

[vbaInet]

What you need to do is:

1. In the Select query, create an alias field to get the DateDiff()
2. Based on the column step 1 using an IIF() to determine in which band to group it, i.e. something along the lines of:

IIF([DiffField] < 6, "5 days", IIF([DiffField] < 11, "6 - 10 days", IIF([DiffField] > 15, "16 + days", Null)))

3. In the Crosstab query use the field in Step 1 as the Value, i.e. Count on it, or Count on the field in step 2 and use as Value
4. Use the field in Step 2 as the Column Heading.

 

[michaeljryan78]

Thanks for the quick response!

Where do I place the iIf statement, in the select query, or the crosstab? You will have to excuse me, I am very new to access. If you can break down the steps that is great!

I did get the dateDiff to work and place that as a column (days) in my select query.

 

[vbaInet]

It is broken down in steps already.

Steps 1 and 2 are Select Query steps. 3 and 4 Crosstab.

It's a matter of doing the first two steps and changing that same query into a Crosstab Query, or create the Crosstab based on the Select Query (which I think you will find easier to understand).

Place step 2 in a new column in the Select Query and it will be prefixed with Expr1:
Rename it DaysBand:

 

[michaeljryan78]

The query works like a charm! Thanks for all of your help! Is there a way to provide feedback for your account?

 

[vbaInet]

Glad that worked for you!

I think it's those little scales at the top right hand corner of the frame of my post.

 

[michaeljryan78]

and I have the SQL for both queries, can you PLEASE help to convert the pendingcrss (select query) to the crosstab?? I need it to be a subreport on a main report. Becuase of the SQL in the crosstab that references the select, the report see's it as forgien.

Select query:
SELECT CounselDetails.ClaimNumber, CounselDetails.STATUS, CounselDetails.[STATUS CHANGE DATE], DateDiff("d",[STATUS CHANGE DATE],Date()) AS days, IIf([days]<6,"5 days",IIf([days]<11,"6 - 10 days",IIf([days]<16,"11-15 Days",IIf([days]>15,"16 + days",Null)))) AS [days band]
FROM CounselDetails
WHERE (((CounselDetails.STATUS) Like "*pending*"));

Crosstab:
TRANSFORM Count(pendingcrss.ClaimNumber) AS CountOfClaimNumber
SELECT pendingcrss.STATUS, Count(pendingcrss.ClaimNumber) AS [Total Of ClaimNumber]
FROM pendingcrss
GROUP BY pendingcrss.STATUS
PIVOT pendingcrss.[days band];


Thanks again, I am in a real crunch!!!

 

[vbaInet]

and I have the SQL for both queries, can you PLEASE help to convert the pendingcrss (select query) to the crosstab?? I need it to be a subreport on a main report.

I think you have this mixed up. You want to convert the select query into a crosstab query like that of pendingcrss.

1. Create a new query which is based on your Select Query. That is open a new query in design view, Show Table and select that Select Query
2. Drop all the necessary fields onto the query - you don't need Days
3. Convert this query to a Crosstab Query by selecting Crosstab
4. Set the Total row of DaysBand to Count and the Crosstab row to Value
5. Add the DaysBand field a second time and set the Total row to GroupBy and Crosstab to Column Heading
6. Set the other necessary fields as Row Headings

By the way, you need to set a criteria of > 4 under the Days field in the Select Query so that it only shows those records.

 

[michaeljryan78]

Thanks! Works like a charm! I appreciate the help!

 

[vbaInet]

Happy to hear!!