Crosstab Report with unknown number of Columns 1

[engrmpk]

I want a report from a crosstab query. The Crosstab query which has RowHeading- Date, ColumnHeading - Date and MoneyRecv - Amount.

I dont know the dates (number of columns, could be sometimes more than 40 and every month it increased by one), Yes if i know the ColumnHeadings (Dates) then i can make labels (in header section) and text box (in detail section) in report to show the data from Crosstab query but in my case I dont know how many Columns Heading for a particular month , because it runs every month.

Please advise about crosstab report, which has columns increasing everymonth ?

 

[SoCalAccessPro]

1st, Investigate Dynamic X-Tab queries.

Then, there's a couple of things you can do.

1. Force the field names. In the query properties, in the column headings field, type in "1";"2";"3"....etc

2. If you want to get REALLY fancy (and I've done this), run a SQL statement 1st in VBA to the get the number of columns and their names. Then, open the querydef and force the column names using VBA. The advantage to this is you won't have any columns withempty data in them. (Weekends, days with no data, etc)



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[engrmpk]

Well this is good, but my question is that how can add labels and textboxes on report design as i dont know the numbers of columns in advance?

 

[SoCalAccessPro]

If you force the column headings, you DO know the number of columns in advance. I've seen examples in some companies where the column headings are always 1 through 31, so it works on every month.

If you follow option 2, again you know the number of columns after running the code, and then could create a dynamic report.

I answered someone else here regaring dynamic reports, and will let you know what I find out tommorrow after checking some reference I have at home.



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[engrmpk]

But in dynamic report i have to create labels and text boxes on report design before run that report, though it is easy to find out the number of columns in advance, but point is that i have to design this and give to users to run, it is not my hand to add more labels every month after counting the number of columns,

Is it possible if the report can add itself labels and text boxes automatically as per number of columns once the report opens (though we can add with VBA that to count number of columns)??

 

[SoCalAccessPro]

Is it possible if the report can add itself labels and text boxes automatically as per number of columns once the report opens (though we can add with VBA that to count number of columns)??

Yup.

And, as I stated, I just can't put my hands on the code right now. I'll post it after checking my reference material at home.

It's a little tricky to set up initially, but really slick once you get it working.



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[dhookom]

I don't like the querydef method since it is generally slow and inflexible. There is a more flexible solution in the Crosstab download at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Duane,
Eloquent solution.

 

[Remou]

Duane
I note that your elegant solution will also allow an .mde to be created, whereas the dangerous method of creating controls on the fly will not allow this.

 

[dhookom]

Thanks MajP and Remou. I created this solution for a client about 8 years ago. I have to review a sample each time I need to duplicate it.

Duane
Hook'D on Access
MS Access MVP

 

[engrmpk]

I am attaching source table, which has to change to Crosstab query and then to Crosstab report. but considering in future that more rows for new dates would be added:

Date1 Date2 Amount
3/1/2008 3/1/2008 $64,172.42
1/1/2008 1/1/2008 $54,007.65
2/1/2008 2/1/2008 $52,832.55
11/1/2007 11/1/2007 $48,676.54
4/1/2008 4/1/2008 $46,486.63
12/1/2007 12/1/2007 $42,856.39
3/1/2007 3/1/2007 $39,851.61
8/1/2007 8/1/2007 $36,726.74
11/1/2007 10/1/2007 $36,644.72
4/1/2007 4/1/2007 $35,735.58
1/1/2007 1/1/2007 $35,379.97
7/1/2007 7/1/2007 $35,215.43
2/1/2007 2/1/2007 $33,809.72
9/1/2007 9/1/2007 $33,730.72
3/1/2006 3/1/2006 $32,984.55
5/1/2007 5/1/2007 $32,778.28
10/1/2006 9/1/2006 $31,601.54
12/1/2006 11/1/2006 $31,016.71
12/1/2006 12/1/2006 $30,296.56
6/1/2007 6/1/2007 $29,995.93
4/1/2006 4/1/2006 $29,198.60
10/1/2006 10/1/2006 $27,854.58
7/1/2006 7/1/2006 $25,835.89
3/1/2005 3/1/2005 $24,358.96
11/1/2005 11/1/2005 $23,891.33
10/1/2007 10/1/2007 $23,440.04
12/1/2005 12/1/2005 $20,957.97
7/1/2006 6/1/2006 $20,565.78
9/1/2005 9/1/2005 $20,549.19
9/1/2007 8/1/2007 $20,428.27
10/1/2005 10/1/2005 $20,364.56
8/1/2007 7/1/2007 $20,158.29
4/1/2005 4/1/2005 $19,874.18
6/1/2006 5/1/2006 $19,538.10
10/1/2006 8/1/2006 $19,242.12
1/1/2005 1/1/2005 $18,685.23
8/1/2005 8/1/2005 $18,580.44
7/1/2005 7/1/2005 $18,415.76
5/1/2005 5/1/2005 $18,312.15
6/1/2005 6/1/2005 $17,642.84
2/1/2005 2/1/2005 $17,049.88
10/1/2007 9/1/2007 $16,783.12
1/1/2008 12/1/2007 $16,421.20
2/1/2006 2/1/2006 $15,738.20
5/1/2006 5/1/2006 $15,364.87
7/1/2007 6/1/2007 $15,181.51
6/1/2007 5/1/2007 $14,877.20
3/1/2008 2/1/2008 $14,794.46
12/1/2004 12/1/2004 $14,656.25
3/1/2006 2/1/2006 $14,337.74
4/1/2008 3/1/2008 $13,547.34
4/1/2007 3/1/2007 $13,329.96
10/1/2004 10/1/2004 $12,936.31
1/1/2006 1/1/2006 $12,573.33
11/1/2004 11/1/2004 $11,852.84
11/1/2006 11/1/2006 $11,804.05
5/1/2007 4/1/2007 $11,755.70
6/1/2006 6/1/2006 $11,521.78
8/1/2006 8/1/2006 $11,451.74
3/1/2006 1/1/2006 $11,445.98
9/1/2004 9/1/2004 $11,116.29
12/1/2007 11/1/2007 $10,867.96
3/1/2004 3/1/2004 $10,686.53
2/1/2007 1/1/2007 $10,226.74
8/1/2006 7/1/2006 $10,222.18
7/1/2004 7/1/2004 $10,144.55
4/1/2004 4/1/2004 $9,978.87
8/1/2004 8/1/2004 $9,760.83
6/1/2004 6/1/2004 $9,554.47
9/1/2006 8/1/2006 $9,419.83
5/1/2004 5/1/2004 $9,279.31
2/1/2006 1/1/2006 $8,879.92
2/1/2008 1/1/2008 $8,695.96
4/1/2006 3/1/2006 $8,430.53
9/1/2007 7/1/2007 $8,429.75
7/1/2007 5/1/2007 $8,412.92
12/1/2006 10/1/2006 $8,272.24
2/1/2004 2/1/2004 $8,037.60
1/1/2008 11/1/2007 $7,295.66
11/1/2006 10/1/2006 $7,276.62
8/1/2007 6/1/2007 $7,172.16
5/1/2007 3/1/2007 $6,714.70
3/1/2008 1/1/2008 $6,699.84
9/1/2006 9/1/2006 $6,427.43
12/1/2003 12/1/2003 $6,113.90
2/1/2004 1/1/2004 $6,026.77
11/1/2003 11/1/2003 $5,994.62
5/1/2006 4/1/2006 $5,339.79
10/1/2007 8/1/2007 $5,154.52
3/1/2007 2/1/2007 $5,084.35
8/1/2006 6/1/2006 $4,589.54
1/1/2007 12/1/2006 $4,556.07
4/1/2007 2/1/2007 $4,530.40
2/1/2007 12/1/2006 $4,501.84
6/1/2005 7/1/2005 $4,039.94
7/1/2005 8/1/2005 $4,007.98
9/1/2005 10/1/2005 $3,980.95
9/1/2007 6/1/2007 $3,973.88
3/1/2007 1/1/2007 $3,968.61
5/1/2005 6/1/2005 $3,909.87
11/1/2007 9/1/2007 $3,869.49
8/1/2005 9/1/2005 $3,864.78
10/1/2007 6/1/2007 $3,845.82
10/1/2003 10/1/2003 $3,819.81
5/1/2007 2/1/2007 $3,670.68
7/1/2003 7/1/2003 $3,281.43
12/1/2007 10/1/2007 $3,161.29
12/1/2007 9/1/2007 $3,106.94
9/1/2003 9/1/2003 $3,089.69
4/1/2008 1/1/2008 $3,081.27
3/1/2008 12/1/2007 $3,032.19
7/1/2007 2/1/2007 $3,020.55
7/1/2006 5/1/2006 $2,967.96
7/1/2007 4/1/2007 $2,960.36
10/1/2005 9/1/2005 $2,924.88
4/1/2008 2/1/2008 $2,882.16
4/1/2005 5/1/2005 $2,850.30
3/1/2005 4/1/2005 $2,842.02
4/1/2007 1/1/2007 $2,791.66
6/1/2007 4/1/2007 $2,789.71
8/1/2004 9/1/2004 $2,718.03
12/1/2005 11/1/2005 $2,682.52
2/1/2008 12/1/2007 $2,680.44
8/1/2007 5/1/2007 $2,636.18
11/1/2005 10/1/2005 $2,618.80
1/1/2008 10/1/2007 $2,564.57
10/1/2005 11/1/2005 $2,539.27
4/1/2006 12/1/2005 $2,529.68
8/1/2007 3/1/2007 $2,492.64
4/1/2006 2/1/2006 $2,461.72
4/1/2003 4/1/2003 $2,461.47
6/1/2006 4/1/2006 $2,373.78
1/1/2008 9/1/2007 $2,360.86
9/1/2006 7/1/2006 $2,310.07
1/1/2004 1/1/2004 $2,256.88
6/1/2004 7/1/2004 $2,252.85
2/1/2005 3/1/2005 $2,206.70
10/1/2006 7/1/2006 $2,172.40
3/1/2003 3/1/2003 $2,156.75
10/1/2003 9/1/2003 $2,070.23
3/1/2007 12/1/2006 $2,063.86
6/1/2005 5/1/2005 $2,029.49
6/1/2003 6/1/2003 $2,006.32
10/1/2003 5/1/2003 $1,972.02
5/1/2004 6/1/2004 $1,937.77
11/1/2006 9/1/2006 $1,893.08
9/1/2007 4/1/2007 $1,882.99
8/1/2003 8/1/2003 $1,863.62
9/1/2004 10/1/2004 $1,853.53
10/1/2003 6/1/2003 $1,844.50
11/1/2005 12/1/2005 $1,843.97
12/1/2004 1/1/2005 $1,820.70
3/1/2005 2/1/2005 $1,800.43
10/1/2004 11/1/2004 $1,787.30
5/1/2007 1/1/2007 $1,784.47
2/1/2003 2/1/2003 $1,719.97
12/1/2004 11/1/2004 $1,690.89
9/1/2006 6/1/2006 $1,685.94
4/1/2007 12/1/2006 $1,682.76
12/1/2003 8/1/2003 $1,630.52
2/1/2008 11/1/2007 $1,615.52
2/1/2006 12/1/2005 $1,588.23
12/1/2002 12/1/2002 $1,584.58
7/1/2004 8/1/2004 $1,579.51
1/1/2003 1/1/2003 $1,570.32
11/1/2004 12/1/2004 $1,541.97
11/1/2006 8/1/2006 $1,506.44
1/1/2005 2/1/2005 $1,484.09
10/1/2005 8/1/2005 $1,464.41
9/1/2007 5/1/2007 $1,381.06
5/1/2003 5/1/2003 $1,379.85
3/1/2007 11/1/2006 $1,374.05
9/1/2002 9/1/2002 $1,321.12
10/1/2005 7/1/2005 $1,287.39
2/1/2008 10/1/2007 $1,265.68
10/1/2002 10/1/2002 $1,235.87
9/1/2005 8/1/2005 $1,164.82
9/1/2007 3/1/2007 $1,163.76
10/1/2005 6/1/2005 $1,163.06
9/1/2005 7/1/2005 $1,144.24
4/1/2004 5/1/2004 $1,123.15
12/1/2006 9/1/2006 $1,071.71
12/1/2006 7/1/2006 $1,063.04
4/1/2007 11/1/2006 $1,049.26
7/1/2007 1/1/2007 $1,046.47
6/1/2007 12/1/2006 $1,001.20
11/1/2002 11/1/2002 $995.70
8/1/2006 5/1/2006 $974.20
6/1/2007 3/1/2007 $969.97
6/1/2004 5/1/2004 $965.75
12/1/2005 1/1/2006 $946.92
2/1/2005 1/1/2005 $886.38
8/1/2002 8/1/2002 $872.30
10/1/2007 7/1/2007 $844.45
9/1/2006 2/1/2006 $833.83
11/1/2004 10/1/2004 $818.92
7/1/2007 11/1/2006 $816.03
2/1/2007 10/1/2006 $788.19
7/1/2006 4/1/2006 $777.04
9/1/2003 8/1/2003 $773.32
8/1/2005 7/1/2005 $758.48
4/1/2006 1/1/2006 $738.29
6/1/2006 3/1/2006 $728.37
5/1/2006 3/1/2006 $702.44
9/1/2004 8/1/2004 $700.19
11/1/2006 7/1/2006 $689.68
7/1/2007 3/1/2007 $681.31
8/1/2006 1/1/2006 $659.87
8/1/2003 7/1/2003 $658.58
11/1/2007 8/1/2007 $626.86
2/1/2007 9/1/2006 $601.23
7/1/2002 7/1/2002 $594.20
11/1/2006 6/1/2006 $587.54
4/1/2004 3/1/2004 $581.54
7/1/2007 12/1/2006 $568.08
4/1/2005 3/1/2005 $559.96
11/1/2007 5/1/2007 $553.31
12/1/2007 7/1/2007 $551.49
6/1/2003 5/1/2003 $543.17
2/1/2004 10/1/2003 $542.16
9/1/2007 2/1/2007 $538.20
10/1/2005 5/1/2005 $520.83
5/1/2006 1/1/2006 $493.81
11/1/2006 4/1/2006 $493.13
11/1/2006 5/1/2006 $490.64
2/1/2008 9/1/2007 $486.75
6/1/2006 2/1/2006 $471.85
7/1/2005 6/1/2005 $465.30
11/1/2006 2/1/2006 $453.88
2/1/2008 8/1/2007 $450.85
3/1/2004 2/1/2004 $449.99
1/1/2004 12/1/2003 $448.94
6/1/2002 6/1/2002 $445.60
12/1/2007 8/1/2007 $437.39
5/1/2004 4/1/2004 $434.20
12/1/2003 10/1/2003 $424.59
7/1/2004 6/1/2004 $422.31
12/1/2003 1/1/2004 $420.61
1/1/2005 11/1/2004 $416.49
3/1/2005 12/1/2004 $410.36
7/1/2003 8/1/2003 $409.27
4/1/2004 11/1/2003 $409.20
3/1/2006 12/1/2005 $405.66
2/1/2004 8/1/2003 $398.36
12/1/2006 12/1/2005 $398.20
4/1/2006 1/1/2005 $394.57
11/1/2003 12/1/2003 $384.71
5/1/2005 4/1/2005 $378.43
10/1/2007 5/1/2007 $367.79
11/1/2006 3/1/2006 $366.81
8/1/2004 7/1/2004 $358.51
3/1/2007 9/1/2006 $358.30
10/1/2004 7/1/2004 $356.97
6/1/2003 7/1/2003 $351.83
5/1/2006 12/1/2005 $348.88
8/1/2005 4/1/2005 $347.76
5/1/2004 2/1/2004 $347.14
4/1/2004 1/1/2004 $347.12
11/1/2003 10/1/2003 $342.13
3/1/2008 11/1/2007 $337.42
3/1/2007 10/1/2006 $335.33
4/1/2008 12/1/2007 $334.92
10/1/2004 8/1/2004 $333.74
10/1/2006 6/1/2006 $331.54
4/1/2008 11/1/2007 $330.71
3/1/2008 4/1/2007 $322.59
5/1/2002 5/1/2002 $320.84
11/1/2006 1/1/2006 $319.20
10/1/2004 9/1/2004 $307.80
3/1/2005 10/1/2004 $307.65
11/1/2007 2/1/2007 $304.79
5/1/2003 4/1/2003 $301.20
11/1/2007 6/1/2007 $297.62
8/1/2005 6/1/2005 $294.65
3/1/2008 9/1/2007 $292.86
4/1/2004 2/1/2004 $291.71
9/1/2007 1/1/2007 $289.82
7/1/2004 2/1/2004 $289.30
10/1/2006 5/1/2006 $284.26
2/1/2006 11/1/2005 $283.87
3/1/2008 8/1/2007 $281.67
5/1/2006 2/1/2006 $275.91
7/1/2006 3/1/2006 $270.03
2/1/2004 12/1/2003 $266.77
4/1/2008 9/1/2007 $265.12
12/1/2007 1/1/2007 $264.20
3/1/2008 10/1/2007 $261.33
4/1/2004 9/1/2003 $259.46
10/1/2003 11/1/2003 $256.83
2/1/2007 11/1/2006 $252.16
2/1/2005 10/1/2004 $247.02
4/1/2004 12/1/2003 $245.48
2/1/2008 7/1/2007 $243.25
8/1/2007 2/1/2007 $237.16
3/1/2005 8/1/2004 $236.55
11/1/2004 9/1/2004 $236.00
11/1/2007 7/1/2007 $235.89
11/1/2007 4/1/2007 $231.40
12/1/2006 6/1/2006 $229.18
9/1/2004 7/1/2004 $227.86
6/1/2004 4/1/2004 $226.80
11/1/2005 9/1/2005 $224.07
12/1/2006 5/1/2006 $223.22
9/1/2003 10/1/2003 $221.63
1/1/2003 11/1/2002 $217.33
3/1/2006 11/1/2005 $216.99
4/1/2005 11/1/2004 $216.96
3/1/2005 1/1/2005 $215.87
1/1/2007 11/1/2006 $214.06
11/1/2004 7/1/2004 $213.43
1/1/2004 10/1/2003 $212.52
1/1/2003 12/1/2002 $212.51
2/1/2004 9/1/2003 $210.64
1/1/2007 10/1/2006 $210.18
4/1/2004 6/1/2003 $206.72
8/1/2006 3/1/2006 $206.00
1/1/2004 11/1/2003 $205.21
3/1/2006 10/1/2005 $203.02
9/1/2006 3/1/2006 $199.04
8/1/2006 4/1/2006 $197.68
3/1/2008 12/1/2006 $192.16
1/1/2005 12/1/2004 $189.34
9/1/2003 3/1/2003 $182.49
4/1/2005 2/1/2005 $182.39
1/1/2008 7/1/2007 $182.26
9/1/2003 1/1/2003 $181.38
7/1/2006 12/1/2005 $176.58
7/1/2003 6/1/2003 $175.30
1/1/2008 2/1/2007 $172.43
11/1/2003 9/1/2003 $170.49
2/1/2007 6/1/2006 $168.79
4/1/2007 9/1/2006 $168.79
4/1/2003 1/1/2003 $167.68
5/1/2005 3/1/2005 $166.63
7/1/2004 4/1/2004 $165.60
1/1/2008 3/1/2007 $164.73
9/1/2003 7/1/2003 $163.32
4/1/2008 12/1/2006 $161.60
4/1/2003 7/1/2002 $160.48
6/1/2005 4/1/2005 $156.82
12/1/2006 8/1/2006 $154.95
6/1/2006 11/1/2005 $153.58
6/1/2006 12/1/2005 $151.78
8/1/2007 12/1/2006 $149.82
3/1/2003 2/1/2003 $149.14
8/1/2003 3/1/2003 $148.29
9/1/2007 12/1/2006 $148.05
12/1/2007 6/1/2007 $147.93
2/1/2006 10/1/2005 $147.26
7/1/2004 5/1/2004 $141.71
4/1/2003 8/1/2002 $137.28
9/1/2006 11/1/2005 $136.53
4/1/2008 10/1/2007 $136.53
1/1/2008 8/1/2007 $136.46
5/1/2003 3/1/2003 $136.41
6/1/2006 1/1/2006 $134.83
7/1/2007 6/1/2006 $134.62
4/1/2003 3/1/2003 $133.89
2/1/2005 11/1/2004 $132.82
9/1/2003 2/1/2003 $132.35
2/1/2005 12/1/2004 $129.99
5/1/2004 3/1/2004 $129.49
12/1/2005 10/1/2005 $128.93
12/1/2003 11/1/2003 $128.46
3/1/2004 1/1/2004 $127.18
11/1/2002 9/1/2002 $126.72
11/1/2004 8/1/2004 $126.17
4/1/2002 4/1/2002 $125.29
8/1/2002 9/1/2002 $122.92
12/1/2002 11/1/2002 $122.35
10/1/2006 3/1/2006 $122.03
9/1/2006 9/1/2005 $121.47
7/1/2004 3/1/2004 $120.76
10/1/2004 5/1/2004 $120.20
7/1/2006 2/1/2006 $120.02
1/1/2003 10/1/2002 $117.46
3/1/2004 12/1/2003 $117.44
11/1/2003 8/1/2003 $116.26
8/1/2007 1/1/2007 $116.22
2/1/2008 4/1/2007 $116.05
5/1/2003 2/1/2003 $115.46
4/1/2006 11/1/2005 $114.64
10/1/2006 4/1/2006 $114.52
12/1/2003 3/1/2003 $113.54
2/1/2007 12/1/2005 $112.95
12/1/2003 9/1/2003 $111.84
1/1/2007 9/1/2006 $111.73
6/1/2007 1/1/2007 $109.43
3/1/2006 8/1/2005 $109.00
2/1/2004 3/1/2004 $108.54
6/1/2007 2/1/2007 $108.48
2/1/2004 11/1/2003 $107.84
10/1/2003 7/1/2003 $107.83
9/1/2006 12/1/2005 $107.55
7/1/2005 5/1/2005 $106.89
10/1/2002 9/1/2002 $104.06
10/1/2004 1/1/2004 $101.55
2/1/2003 1/1/2003 $100.26
6/1/2003 4/1/2003 $99.67
3/1/2004 4/1/2004 $99.44
10/1/2007 1/1/2007 $99.13
9/1/2006 10/1/2005 $99.04
1/1/2008 6/1/2007 $98.93
1/1/2005 9/1/2004 $98.31
10/1/2005 2/1/2005 $98.08
4/1/2003 9/1/2002 $97.56
4/1/2003 5/1/2003 $95.66
10/1/2006 12/1/2005 $90.98
9/1/2003 6/1/2003 $89.39
6/1/2004 3/1/2004 $88.65
11/1/2007 12/1/2006 $87.87
2/1/2007 8/1/2006 $87.66
3/1/2003 1/1/2003 $84.17
2/1/2004 6/1/2003 $83.12
8/1/2007 10/1/2006 $82.73
8/1/2007 11/1/2006 $82.70
2/1/2008 1/1/2007 $81.91
2/1/2003 3/1/2003 $81.34
11/1/2005 8/1/2005 $81.02
10/1/2004 10/1/2003 $80.70
10/1/2004 11/1/2003 $80.68
8/1/2003 2/1/2003 $80.25
10/1/2004 9/1/2003 $79.13
12/1/2007 5/1/2007 $78.14
9/1/2004 3/1/2004 $77.84
8/1/2004 6/1/2004 $77.83
1/1/2004 8/1/2003 $77.69
8/1/2006 2/1/2006 $75.98
5/1/2006 11/1/2005 $75.79
2/1/2006 9/1/2005 $75.69
10/1/2007 4/1/2007 $75.33
5/1/2003 6/1/2003 $75.22
1/1/2005 5/1/2003 $75.13
12/1/2005 9/1/2005 $75.08
7/1/2006 1/1/2006 $75.03
4/1/2007 10/1/2006 $74.03
9/1/2002 10/1/2002 $73.70
6/1/2004 11/1/2003 $73.12
3/1/2006 9/1/2005 $72.98
2/1/2008 12/1/2006 $72.64
12/1/2007 4/1/2007 $72.15
4/1/2003 12/1/2002 $70.47
10/1/2004 12/1/2003 $69.27
10/1/2006 2/1/2006 $68.72
10/1/2006 8/1/2005 $68.70
4/1/2006 8/1/2005 $67.94
3/1/2003 4/1/2003 $67.86
6/1/2004 8/1/2003 $67.11
3/1/2002 3/1/2002 $66.46
10/1/2006 1/1/2006 $66.27
1/1/2007 8/1/2006 $66.24
12/1/2007 5/1/2006 $64.22
4/1/2003 10/1/2002 $64.15
9/1/2005 4/1/2005 $63.13
9/1/2006 6/1/2005 $63.11
1/1/2008 4/1/2007 $62.00
5/1/2005 2/1/2005 $61.23
7/1/2007 7/1/2006 $61.09
12/1/2004 8/1/2004 $60.56
10/1/2003 4/1/2003 $59.43
4/1/2008 8/1/2007 $59.08
12/1/2004 3/1/2004 $58.94
3/1/2007 6/1/2006 $58.81
9/1/2004 6/1/2004 $58.79
11/1/2007 6/1/2006 $58.70
8/1/2005 5/1/2005 $58.28
11/1/2002 12/1/2002 $57.98
4/1/2004 10/1/2003 $57.90
3/1/2007 5/1/2006 $57.83
10/1/2006 10/1/2005 $57.61
9/1/2007 4/1/2006 $57.58
2/1/2004 7/1/2003 $56.85
10/1/2003 8/1/2003 $56.45
1/1/2005 8/1/2004 $55.96
2/1/2006 7/1/2005 $55.49
4/1/2006 7/1/2005 $55.32
2/1/2007 7/1/2006 $54.63
6/1/2003 3/1/2003 $53.95
12/1/2004 10/1/2004 $53.79
12/1/2005 8/1/2005 $53.79
4/1/2006 10/1/2005 $53.45
10/1/2007 3/1/2007 $53.44
4/1/2005 10/1/2004 $53.15
11/1/2004 5/1/2003 $53.10
12/1/2005 7/1/2005 $52.46
1/1/2008 5/1/2007 $52.29
8/1/2007 6/1/2006 $52.12
6/1/2003 8/1/2002 $52.04
2/1/2006 8/1/2005 $51.87
12/1/2004 9/1/2004 $51.74
2/1/2003 12/1/2002 $51.57
7/1/2004 11/1/2003 $51.10
3/1/2007 8/1/2006 $49.94
12/1/2003 4/1/2003 $49.23
5/1/2005 1/1/2005 $47.92
11/1/2003 7/1/2003 $47.85
4/1/2003 11/1/2002 $47.66
3/1/2008 3/1/2007 $47.45
4/1/2008 7/1/2007 $46.92
2/1/2007 5/1/2006 $46.39
1/1/2005 10/1/2004 $44.93
3/1/2005 9/1/2003 $44.19
11/1/2007 5/1/2006 $43.39
11/1/2005 6/1/2005 $42.77
9/1/2007 11/1/2006 $42.72
3/1/2008 7/1/2007 $42.50
9/1/2005 6/1/2005 $42.46
3/1/2008 6/1/2007 $42.15
3/1/2005 1/1/2004 $42.02
6/1/2007 11/1/2006 $42.00
3/1/2005 2/1/2004 $41.56
6/1/2004 2/1/2004 $40.71
10/1/2002 7/1/2002 $40.57
10/1/2006 11/1/2005 $39.79
4/1/2005 12/1/2004 $38.67
2/1/2007 2/1/2006 $38.39
8/1/2003 11/1/2002 $37.70
4/1/2003 2/1/2003 $37.47
8/1/2007 4/1/2007 $37.44
1/1/2007 6/1/2006 $37.17
1/1/2005 3/1/2004 $36.54
8/1/2002 7/1/2002 $35.88
1/1/2007 7/1/2006 $35.23
5/1/2006 9/1/2005 $35.16
8/1/2007 7/1/2006 $34.85
9/1/2005 5/1/2005 $34.63
2/1/2008 5/1/2007 $34.42
6/1/2003 9/1/2002 $34.01
9/1/2005 3/1/2005 $33.45
6/1/2004 1/1/2004 $31.38
8/1/2007 1/1/2006 $31.18
8/1/2007 3/1/2006 $31.18
6/1/2003 6/1/2002 $31.07
3/1/2003 11/1/2002 $30.87
11/1/2006 12/1/2005 $30.67
11/1/2007 2/1/2006 $30.49
1/1/2004 2/1/2003 $30.49
10/1/2007 12/1/2006 $30.41
2/1/2005 5/1/2004 $30.01
5/1/2005 11/1/2004 $29.87
1/1/2004 12/1/2002 $29.67
6/1/2006 9/1/2005 $29.59
3/1/2007 2/1/2006 $29.22
4/1/2003 5/1/2002 $29.21
11/1/2007 4/1/2006 $28.68
3/1/2008 2/1/2007 $28.57
1/1/2003 6/1/2002 $28.06
4/1/2007 8/1/2006 $27.65
6/1/2005 1/1/2005 $27.35
9/1/2006 4/1/2006 $25.50
11/1/2007 7/1/2006 $25.11
7/1/2007 8/1/2006 $24.94
9/1/2004 8/1/2003 $24.51
2/1/2004 1/1/2003 $23.94
3/1/2005 11/1/2003 $23.91
9/1/2004 4/1/2004 $22.86
2/1/2004 4/1/2003 $22.44
2/1/2004 5/1/2003 $22.39
4/1/2008 1/1/2007 $22.15
11/1/2002 10/1/2002 $22.15
1/1/2007 5/1/2006 $22.09
8/1/2003 5/1/2003 $21.82
9/1/2006 5/1/2006 $21.71
5/1/2007 12/1/2006 $21.54
1/1/2008 9/1/2005 $21.32
3/1/2005 11/1/2004 $20.97
10/1/2007 2/1/2007 $20.93
3/1/2007 1/1/2006 $20.74
7/1/2004 10/1/2003 $20.56
6/1/2005 11/1/2004 $20.48
2/1/2004 11/1/2002 $20.43
3/1/2004 8/1/2003 $20.03
4/1/2007 7/1/2006 $20.00
9/1/2004 5/1/2004 $19.69
1/1/2008 2/1/2006 $19.43
2/1/2004 3/1/2003 $19.41
12/1/2003 7/1/2003 $19.38
6/1/2006 6/1/2005 $19.36
3/1/2008 5/1/2007 $19.23
3/1/2007 3/1/2006 $19.11
10/1/2005 1/1/2005 $18.99
9/1/2005 1/1/2005 $18.86
3/1/2002 2/1/2002 $18.73
1/1/2008 8/1/2006 $18.69
4/1/2008 6/1/2007 $18.37
4/1/2005 5/1/2004 $18.30
8/1/2007 8/1/2006 $17.82
12/1/2003 6/1/2003 $17.80
6/1/2006 7/1/2005 $17.78
6/1/2003 2/1/2003 $17.73
6/1/2003 7/1/2002 $17.64
1/1/2003 2/1/2003 $17.45
5/1/2004 12/1/2003 $17.23
3/1/2004 9/1/2003 $16.99
12/1/2003 5/1/2003 $16.83
1/1/2004 2/1/2004 $16.46
8/1/2007 9/1/2006 $16.21
11/1/2003 3/1/2003 $16.15
1/1/2008 11/1/2006 $16.04
11/1/2002 7/1/2002 $15.96
7/1/2003 5/1/2003 $15.92
11/1/2003 2/1/2003 $15.77
3/1/2005 6/1/2003 $15.35
1/1/2004 7/1/2003 $14.95
10/1/2002 5/1/2002 $14.85
12/1/2007 2/1/2007 $14.69
5/1/2003 1/1/2003 $14.53
10/1/2003 2/1/2003 $14.49
7/1/2003 3/1/2003 $14.31
6/1/2005 2/1/2005 $14.25
7/1/2005 2/1/2005 $14.22
2/1/2007 3/1/2006 $13.89
2/1/2008 10/1/2006 $13.71
11/1/2002 8/1/2002 $13.47
1/1/2008 1/1/2007 $13.46
8/1/2003 1/1/2003 $13.46
12/1/2004 7/1/2004 $13.24
4/1/2004 8/1/2003 $12.90
1/1/2008 7/1/2006 $12.86
6/1/2004 12/1/2003 $12.83
1/1/2004 1/1/2003 $12.44
2/1/2004 10/1/2002 $12.14
1/1/2008 9/1/2006 $11.86
4/1/2005 1/1/2005 $11.77
1/1/2004 6/1/2003 $11.74
2/1/2005 8/1/2004 $11.62
4/1/2004 5/1/2003 $11.53
1/1/2008 10/1/2006 $11.38
3/1/2008 1/1/2007 $11.32
9/1/2005 10/1/2004 $11.18
5/1/2004 1/1/2004 $11.18
8/1/2004 11/1/2003 $10.93
9/1/2004 1/1/2004 $10.90
1/1/2004 8/1/2002 $10.56
1/1/2004 9/1/2003 $10.51
6/1/2003 5/1/2002 $10.28
10/1/2003 9/1/2002 $10.23
5/1/2007 11/1/2006 $10.17
8/1/2007 $10.00
3/1/2004 10/1/2003 $9.75
8/1/2003 6/1/2003 $9.70
11/1/2003 4/1/2002 $9.70
12/1/2005 6/1/2005 $9.68
10/1/2003 11/1/2002 $9.67
3/1/2004 7/1/2003 $9.62
8/1/2004 5/1/2004 $9.40
3/1/2004 6/1/2003 $9.29
11/1/2004 12/1/2002 $9.28
7/1/2004 1/1/2003 $9.08
3/1/2004 11/1/2003 $9.08
3/1/2003 10/1/2002 $8.95
1/1/2004 10/1/2002 $8.93
1/1/2008 6/1/2006 $8.85
5/1/2004 4/1/2002 $8.70
5/1/2005 12/1/2004 $8.68
6/1/2004 9/1/2002 $8.57
6/1/2006 10/1/2005 $8.40
1/1/2008 4/1/2006 $8.37
11/1/2004 11/1/2003 $8.34
1/1/2004 5/1/2003 $8.25
7/1/2003 4/1/2003 $8.00
1/1/2003 8/1/2002 $7.89
6/1/2003 10/1/2002 $7.77
6/1/2007 10/1/2006 $7.74
7/1/2006 11/1/2005 $7.60
4/1/2005 9/1/2004 $7.56
10/1/2004 12/1/2002 $7.52
3/1/2005 3/1/2004 $7.45
6/1/2007 8/1/2006 $7.45
10/1/2007 11/1/2006 $7.34
11/1/2004 2/1/2004 $6.90
5/1/2007 10/1/2006 $6.70
9/1/2005 11/1/2004 $6.69
12/1/2004 11/1/2002 $6.60
12/1/2002 6/1/2002 $6.57
3/1/2004 11/1/2002 $6.57
3/1/2003 6/1/2002 $6.52
10/1/2003 1/1/2003 $6.50
1/1/2003 7/1/2002 $6.43
11/1/2004 4/1/2004 $6.33
2/1/2005 6/1/2004 $6.26
11/1/2007 11/1/2006 $6.16
6/1/2005 3/1/2005 $6.12
9/1/2007 5/1/2006 $6.08
6/1/2003 12/1/2002 $6.02
5/1/2004 6/1/2003 $5.97
3/1/2003 12/1/2002 $5.93
12/1/2002 10/1/2002 $5.87
6/1/2005 10/1/2004 $5.81
11/1/2005 4/1/2005 $5.78
1/1/2004 9/1/2002 $5.65
2/1/2008 3/1/2006 $5.62
1/1/2005 5/1/2004 $5.61
9/1/2002 7/1/2002 $5.55
9/1/2005 12/1/2004 $5.48
6/1/2007 9/1/2006 $5.46
12/1/2004 2/1/2004 $5.34
4/1/2004 7/1/2003 $5.10
12/1/2004 7/1/2002 $5.07
10/1/2004 1/1/2003 $4.84
1/1/2003 5/1/2002 $4.82
8/1/2005 3/1/2005 $4.81
11/1/2003 6/1/2003 $4.56
10/1/2003 10/1/2002 $4.52
2/1/2007 1/1/2006 $4.46
10/1/2002 8/1/2002 $4.46
10/1/2003 3/1/2003 $4.37
12/1/2004 8/1/2002 $4.29
7/1/2005 4/1/2005 $4.28
7/1/2003 2/1/2003 $4.21
9/1/2005 2/1/2005 $4.20
12/1/2004 9/1/2002 $4.06
5/1/2004 8/1/2002 $4.05
2/1/2008 3/1/2007 $3.96
7/1/2004 6/1/2002 $3.86
2/1/2006 4/1/2005 $3.78
4/1/2007 5/1/2006 $3.74
1/1/2005 8/1/2003 $3.69
8/1/2003 4/1/2003 $3.44
6/1/2005 2/1/2004 $3.36
6/1/2005 3/1/2004 $3.35
8/1/2004 1/1/2004 $3.30
6/1/2002 5/1/2002 $3.26
1/1/2003 9/1/2002 $3.25
10/1/2002 4/1/2002 $3.12
7/1/2004 1/1/2004 $3.11
5/1/2004 9/1/2003 $3.10
8/1/2004 12/1/2003 $3.09
5/1/2007 9/1/2006 $2.99
9/1/2003 7/1/2002 $2.95
8/1/2004 2/1/2004 $2.90
5/1/2006 10/1/2005 $2.85
12/1/2003 9/1/2002 $2.71
3/1/2008 11/1/2006 $2.59
1/1/2006 12/1/2005 $2.56
4/1/2008 5/1/2007 $2.54
2/1/2002 2/1/2002 $2.43
9/1/2003 8/1/2002 $2.36
9/1/2004 2/1/2004 $2.24
8/1/2006 12/1/2005 $2.21
9/1/2005 $2.14
11/1/2007 1/1/2007 $2.12
9/1/2003 4/1/2003 $2.11
11/1/2005 7/1/2005 $2.06
2/1/2002 1/1/2002 $2.05
4/1/2006 9/1/2004 $2.05
9/1/2003 5/1/2003 $2.03
12/1/2004 6/1/2002 $2.00
5/1/2007 8/1/2006 $1.88
4/1/2007 12/1/2005 $1.87
12/1/2006 3/1/2006 $1.87
6/1/2004 6/1/2002 $1.67
6/1/2005 6/1/2004 $1.65
11/1/2004 6/1/2004 $1.61
12/1/2006 4/1/2006 $1.56
4/1/2007 5/1/2008 $1.54
7/1/2004 12/1/2003 $1.47
12/1/2007 12/1/2006 $1.39
4/1/2005 8/1/2004 $1.28
3/1/2004 5/1/2003 $1.25
11/1/2004 6/1/2002 $1.13
6/1/2005 9/1/2004 $1.04
2/1/2005 4/1/2004 $1.02
9/1/2002 8/1/2002 $0.99
8/1/2004 8/1/2002 $0.98
3/1/2005 7/1/2004 $0.97
5/1/2005 8/1/2003 $0.97
6/1/2002 7/1/2002 $0.92
9/1/2003 12/1/2002 $0.92
10/1/2005 3/1/2004 $0.91
12/1/2004 5/1/2002 $0.86
2/1/2004 6/1/2002 $0.84
11/1/2004 9/1/2003 $0.74
10/1/2004 6/1/2002 $0.72
1/1/2005 7/1/2004 $0.71
5/1/2005 5/1/2008 $0.67
3/1/2004 2/1/2003 $0.66
5/1/2005 10/1/2003 $0.66
6/1/2005 4/1/2004 $0.61
10/1/2006 5/1/2005 $0.56
12/1/2006 2/1/2006 $0.56
8/1/2005 9/1/2004 $0.53
10/1/2006 $0.51
11/1/2005 1/1/2005 $0.48
5/1/2002 4/1/2002 $0.46
1/1/2005 6/1/2004 $0.45
3/1/2005 2/1/2003 $0.42
7/1/2004 2/1/2003 $0.40
2/1/2005 7/1/2004 $0.40
6/1/2006 5/1/2005 $0.37
4/1/2005 5/1/2008 $0.36
3/1/2005 7/1/2003 $0.35
1/1/2005 2/1/2004 $0.30
3/1/2006 6/1/2005 $0.26
12/1/2004 6/1/2004 $0.26
11/1/2004 7/1/2002 $0.25
3/1/2005 5/1/2004 $0.24
1/1/2005 7/1/2003 $0.21
5/1/2007 5/1/2006 $0.19
6/1/2006 5/1/2004 $0.19
11/1/2007 9/1/2005 $0.16
4/1/2002 3/1/2002 $0.15
1/1/2005 10/1/2003 $0.14
5/1/2007 7/1/2006 $0.13
9/1/2005 1/1/2004 $0.11
12/1/2006 11/1/2005 $0.11
3/1/2005 4/1/2004 $0.10
1/1/2005 9/1/2003 $0.10
2/1/2005 5/1/2002 $0.10
6/1/2004 10/1/2003 $0.10
8/1/2005 6/1/2004 $0.10
2/1/2005 7/1/2002 $0.09
2/1/2005 6/1/2002 $0.08
2/1/2008 2/1/2007 $0.07
5/1/2005 1/1/2004 $0.07
8/1/2004 3/1/2004 $0.06
5/1/2005 11/1/2003 $0.06
11/1/2007 9/1/2006 $0.06
11/1/2003 6/1/2002 $0.06
5/1/2004 11/1/2003 $0.05
3/1/2006 1/1/2005 $0.04
9/1/2005 12/1/2003 $0.04
12/1/2004 12/1/2003 $0.03
8/1/2005 1/1/2005 $0.02
4/1/2003 6/1/2002 $0.02
11/1/2003 5/1/2003 $0.02
2/1/2004 $0.01
5/1/2004 8/1/2003 $0.01
6/1/2006 4/1/2005 $0.01
6/1/2004 9/1/2003 $0.01
4/1/2005 $0.01
11/1/2003 3/1/2002 $0.01
4/1/2002 $0.01
6/1/2004 5/1/2002 $0.00
7/1/2006 $0.00
8/1/2002 $0.00
6/1/2006 6/1/2004 $0.00
5/1/2004 10/1/2003 $0.00
9/1/2002 $0.00
11/1/2002 $0.00
7/1/2003 $0.00
8/1/2003 $0.00
9/1/2003 $0.00
11/1/2003 $0.00
9/1/2004 $0.00
10/1/2004 $0.00
11/1/2004 $0.00
12/1/2004 $0.00
1/1/2005 $0.00
2/1/2005 $0.00
5/1/2005 $0.00
6/1/2005 $0.00
7/1/2005 $0.00
8/1/2005 $0.00
10/1/2005 $0.00
11/1/2005 $0.00
12/1/2005 $0.00
1/1/2006 $0.00
2/1/2006 $0.00
3/1/2006 $0.00
4/1/2006 $0.00
5/1/2006 $0.00
6/1/2006 $0.00
8/1/2006 $0.00
9/1/2006 $0.00
11/1/2006 $0.00
12/1/2006 $0.00
1/1/2007 $0.00
2/1/2007 $0.00
3/1/2007 $0.00
4/1/2007 $0.00
5/1/2007 $0.00
6/1/2007 $0.00
7/1/2007 $0.00
9/1/2007 $0.00
10/1/2007 $0.00
11/1/2007 $0.00
12/1/2007 $0.00
1/1/2008 $0.00
2/1/2008 $0.00
3/1/2008 $0.00
4/1/2008 $0.00
5/1/2008 $0.00
7/1/2002 5/1/2002 $0.00
7/1/2002 6/1/2002 $0.00
8/1/2002 6/1/2002 $0.00
2/1/2003 10/1/2002 $0.00
2/1/2003 11/1/2002 $0.00
7/1/2003 12/1/2002 $0.00
10/1/2003 3/1/2005 $0.00
5/1/2004 7/1/2003 $0.00
8/1/2004 5/1/2003 $0.00
9/1/2004 10/1/2003 $0.00
9/1/2004 12/1/2003 $0.00
10/1/2004 5/1/2003 $0.00
10/1/2004 8/1/2003 $0.00
10/1/2004 6/1/2004 $0.00
11/1/2004 7/1/2003 $0.00
12/1/2004 1/1/2004 $0.00
12/1/2004 5/1/2004 $0.00
1/1/2005 11/1/2003 $0.00
1/1/2005 12/1/2003 $0.00
2/1/2005 1/1/2003 $0.00
2/1/2005 2/1/2003 $0.00
2/1/2005 7/1/2003 $0.00
2/1/2005 12/1/2003 $0.00
2/1/2005 2/1/2004 $0.00
2/1/2005 9/1/2004 $0.00
3/1/2005 12/1/2003 $0.00
3/1/2005 9/1/2004 $0.00
5/1/2005 6/1/2004 $0.00
6/1/2005 7/1/2003 $0.00
6/1/2005 7/1/2004 $0.00
7/1/2005 3/1/2005 $0.00
10/1/2005 6/1/2003 $0.00
11/1/2005 3/1/2005 $0.00
2/1/2006 1/1/2005 $0.00
3/1/2006 2/1/2005 $0.00
3/1/2006 7/1/2005 $0.00
3/1/2006 8/1/2006 $0.00
4/1/2006 9/1/2003 $0.00
4/1/2006 9/1/2005 $0.00
6/1/2006 3/1/2005 $0.00
6/1/2006 8/1/2005 $0.00
10/1/2006 6/1/2005 $0.00
10/1/2006 7/1/2005 $0.00
10/1/2006 9/1/2005 $0.00
2/1/2007 4/1/2006 $0.00
3/1/2007 11/1/2005 $0.00
3/1/2007 12/1/2005 $0.00
6/1/2007 1/1/2006 $0.00
6/1/2007 2/1/2006 $0.00
6/1/2007 4/1/2006 $0.00
6/1/2007 5/1/2006 $0.00
7/1/2007 8/1/2007 $0.00
7/1/2007 9/1/2007 $0.00
7/1/2007 10/1/2007 $0.00
7/1/2007 11/1/2007 $0.00
7/1/2007 12/1/2007 $0.00
7/1/2007 1/1/2008 $0.00
7/1/2007 2/1/2008 $0.00
7/1/2007 3/1/2008 $0.00
7/1/2007 4/1/2008 $0.00
7/1/2007 5/1/2008 $0.00
8/1/2007 5/1/2006 $0.00
10/1/2007 9/1/2004 $0.00
10/1/2007 11/1/2005 $0.00
11/1/2007 10/1/2005 $0.00
11/1/2007 11/1/2005 $0.00
11/1/2007 12/1/2005 $0.00
12/1/2007 8/1/2006 $0.00
2/1/2008 6/1/2007 $0.00
3/1/2008 1/1/2006 $0.00
3/1/2008 2/1/2006 $0.00
3/1/2008 3/1/2006 $0.00
3/1/2008 4/1/2006 $0.00
3/1/2008 6/1/2006 $0.00
3/1/2008 7/1/2006 $0.00
3/1/2008 8/1/2006 $0.00
3/1/2008 9/1/2006 $0.00
11/1/2005 9/1/2002 ($0.09)

 

[dhookom]

Since you have dates as column headings, the only solution that I would use/consider is provided in faq703-5466. If your interval is not monthly, you an easily change this to any other date/time interval.

Duane
Hook'D on Access
MS Access MVP

 

[engrmpk]

my dates are monthly but first of each month, and FAQ703-5466: Dynamic Monthly Crosstab Report. is for 12 months in my case there are 5-6 years worth months - this is big problem, how to represent so many months

 

[Remou]

Surely the months go across the top and the years down the side?

 

[engrmpk]

no i have 2 kinds of different dates date1(RowHeading) and date2 (ColumnHeading) has more than 40 rows and then Corresponding Amount (Value)-

 

[Remou]

I think your best bet is output to a spreadsheet.

 

[roccorocks]

Nice one, way to go.

 

[dhookom]

I don't think it makes any difference how many columns you have. If they are months, I would use the solution I suggested earlier from the FAQ.

Duane
Hook'D on Access
MS Access MVP

 

[engrmpk]

so should i take the last month as enddate and minus other months, but i have to take the first of each months and both row and columns Headings are dates as i showed above.

 

[dhookom]

Exactly. You decide on the maximum number of columns and the user defines the end date. The records are aggregated by the month and displayed.

Duane
Hook'D on Access
MS Access MVP