Crosstab Query Report Question

[kentwoodjean]

After multiple attemps over a period of time, I finally created my 1st crosstab query report. I created one for the year 2002, and it formatted beautifully. I prepared a crosstab query report for the year 2003 also. It summarizes huge amounts of information that comes to me about 2 months after the fact (Jan-Aug currently). Here is my question. Will the additional months of 2003 appear as I import the information on my table? Stupd qustion probably, but this is my 1st crack at the crosstabs.

 

[dhookom]

Please post your SQL. We have no idea what your row and column headings are or you value.

Duane
MS Access MVP

 

[kentwoodjean]

Sorry, this crosstab stuff is rather new to me.

Row heading - Toll Free Number
Row Heading - Number Name
Column Heading - Date
Value - Count of # of calls made to each Toll Free Num.


TRANSFORM Count(QryAll2003.MySort) AS [The Value]
SELECT QryAll2003.NumName, QryAll2003.TollFreeNum, Count(QryAll2003.MySort) AS [Total Of MySort]
FROM QryAll2003
GROUP BY QryAll2003.NumName, QryAll2003.TollFreeNum
PIVOT QryAll2003.Month;

 

[dhookom]

Your description doesn't match your SQL. Where does Month come from. That point aside, I suggest you create a crosstab using relative months. The following is a posting I made to a public news group. Come on back if you have questions.

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.
Set your queries Column Headings property to:
Mth0, Mth1, Mth2, Mth3,.., Mth11
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.


Duane
MS Access MVP