Crosstab Column Headings

[Paula]

I need to do a summary using a crosstab query. The data has a Date field
(Not named "Date"). I can do the Row Heading and Value but am having trouble
with the Column Heading. The summary Columns needs to read like:
Sept01 to Aug02 Sept02 to Aug03 Sept03 to Aug04
Can anyone give me a recommendation on how to get from the Date field to
these Column Headings? I then would like to set criteria where I can select
something that will give me either 1, 2, 3 , 4 or 5 columns in a selected
date range. I need help on that too.

Thanks!

Paula

[Duane Hookom]

If possible, don't 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 with menuing: 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 with control sources of:
=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 Hookom
MS Access MVP
"Paula" <pm*****@notmymail.com> wrote in message
news:B3*****************@newsread2.news.atl.earthl ink.net...

I need to do a summary using a crosstab query. The data has a Date field
(Not named "Date"). I can do the Row Heading and Value but am having trouble with the Column Heading. The summary Columns needs to read like:
Sept01 to Aug02 Sept02 to Aug03 Sept03 to Aug04
Can anyone give me a recommendation on how to get from the Date field to
these Column Headings? I then would like to set criteria where I can select something that will give me either 1, 2, 3 , 4 or 5 columns in a selected
date range. I need help on that too.

Thanks!

Paula

[Paula]

Duane,

Thanks for responding to this post also! And thanks once again for showing
me how to get the zeroes!

I'm sorry my post here was not absolutely clear. I do not want a monthly
summary. I want a yearly summary where the colum headings I showed are
twelve month periods. So in my example, I want three columns with the total
for the twelve month period being the value. I can do the Row Heading and
the Value, I need help on how to go from the Date field to the Column
Heading where the twelve month period starts at some month in a year and
goes to the previous month in the next year.

Please give it a relook!

Paula
"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
news:uU**************@TK2MSFTNGP10.phx.gbl...

If possible, don't 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 with menuing: 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 with control sources of:
=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 Hookom
MS Access MVP
"Paula" <pm*****@notmymail.com> wrote in message
news:B3*****************@newsread2.news.atl.earthl ink.net...

I need to do a summary using a crosstab query. The data has a Date field
(Not named "Date"). I can do the Row Heading and Value but am having

trouble

with the Column Heading. The summary Columns needs to read like:
Sept01 to Aug02 Sept02 to Aug03 Sept03 to Aug04
Can anyone give me a recommendation on how to get from the Date field to
these Column Headings? I then would like to set criteria where I can

select

something that will give me either 1, 2, 3 , 4 or 5 columns in a selected date range. I need help on that too.

Thanks!

Paula

[Duane Hookom]

Try a column heading expression like
"Yr" & DateDiff("m",[DateField],Date())\12

--
Duane Hookom
MS Access MVP
"Paula" <pm*****@notmymail.com> wrote in message
news:wR*****************@newsread2.news.atl.earthl ink.net...

Duane,

Thanks for responding to this post also! And thanks once again for showing
me how to get the zeroes!

I'm sorry my post here was not absolutely clear. I do not want a monthly
summary. I want a yearly summary where the colum headings I showed are
twelve month periods. So in my example, I want three columns with the total for the twelve month period being the value. I can do the Row Heading and
the Value, I need help on how to go from the Date field to the Column
Heading where the twelve month period starts at some month in a year and
goes to the previous month in the next year.

Please give it a relook!

Paula
"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
news:uU**************@TK2MSFTNGP10.phx.gbl...

If possible, don't 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 with menuing: 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 with control sources of:
=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 Hookom
MS Access MVP
"Paula" <pm*****@notmymail.com> wrote in message
news:B3*****************@newsread2.news.atl.earthl ink.net...

I need to do a summary using a crosstab query. The data has a Date field (Not named "Date"). I can do the Row Heading and Value but am having

trouble

with the Column Heading. The summary Columns needs to read like:
Sept01 to Aug02 Sept02 to Aug03 Sept03 to Aug04
Can anyone give me a recommendation on how to get from the Date field to these Column Headings? I then would like to set criteria where I can

select

something that will give me either 1, 2, 3 , 4 or 5 columns in a

selected date range. I need help on that too.

Thanks!

Paula