Crosstab Column Headings

  • Thread starter Thread starter Paula
  • Start date Start date
P

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
 
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,

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 said:
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 said:
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
Click to expand...
Click to expand...
 
Try a column heading expression like
"Yr" & DateDiff("m",[DateField],Date())\12

--
Duane Hookom
MS Access MVP


Paula said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top