I'm using MS Access 2003 in a Windows XP environment.
I've created an aging report for my department that is based on what I call the "Main query" with an IIf statement that allows the user to define the dates into 30 day aging segments. Then I've created a crosstab query which counts the results.
How I'd like the report to appear is like this:
Expand|Select|Wrap|Line Numbers
- Entity_Type | 1 to 30 days | 31 to 60 days | Over 60 days
- OPFACT | 0 | 0 | 0
- PARM | 10 | 20 | 0
- WO | 30 | 40 | 0
- WR | 20 | 30 | 0
Expand|Select|Wrap|Line Numbers
- Entity_Type | 1 to 30 days | 31 to 60 days
- PARM | 10 | 20
- WO | 30 | 40
- WR | 20 | 30
Here's my crosstab query:
Expand|Select|Wrap|Line Numbers
- TRANSFORM Count(EP_Aging_Main_Query.STAT_DATE) AS CountOfSTAT_DATE
- SELECT EP_Aging_Main_Query.ENTITY_TYPE, Count(EP_Aging_Main_Query.STAT_DATE) AS [Total Of STAT_Date]
- FROM EP_Aging_Main_Query
- GROUP BY EP_Aging_Main_Query.ENTITY_TYPE
- PIVOT EP_Aging_Main_Query.[Date Interval];
And here is the query that it is based off of:
Expand|Select|Wrap|Line Numbers
- SELECT [01c_Report_Two_Six].CONCATENATED, [01c_Report_Two_Six].ENTITY_TYPE, [01c_Report_Two_Six].STAT_DATE, IIf([STAT_DATE]<#4/5/2007#,"Over 61 Days",IIf([STAT_DATE] Between #4/4/2007# And #4/8/2007#,"31 to 60 Days",IIf([STAT_DATE] Between #4/7/2007# And #4/14/2007#,"1 to 30 Days"))) AS [Date Interval]
- FROM 01c_Report_Two_Six
- GROUP BY [01c_Report_Two_Six].CONCATENATED, [01c_Report_Two_Six].ENTITY_TYPE, [01c_Report_Two_Six].STAT_DATE, IIf([STAT_DATE]<#4/5/2007#,"Over 61 Days",IIf([STAT_DATE] Between #4/4/2007# And #4/8/2007#,"31 to 60 Days",IIf([STAT_DATE] Between #4/7/2007# And #4/14/2007#,"1 to 30 Days")));
Any help would be greatly appreciated as I have two other reports that have this same issue.
Thanks!
Bob