Crosstab Column Headings

[Guest]

I made a crosstab query that returns a date as the column heading. I would
like to change this to returning the text Week1, Week2, Week3. How would I
go about this? Below is an example of what I am talking about.

**************************************************

Current Column Headings:

12/03/04 12/10/04 12/17/04 12/24/04

Desired Column Headings:

Week1 Week2 Week3 Week4

************************************************



Thanks for all of your help.

Chad Lucy

 

[Duane Hookom]

Here is a method that does this with Months. You should be able to
re-purpose the solution for weeks.
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 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.

 

[Guest]

Duane,
Thanks, that put me on the right track but I am not sure as to how to
get those 'relative' month dates without using the DateDiff Function as
described below. I am not comparing my dates to a specific date on a form, I
am simply listing them from a table. Is there a way to do this?

Thanks again,
Chad

Here is a method that does this with Months. You should be able to
re-purpose the solution for weeks.
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 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

I made a crosstab query that returns a date as the column heading. I would
like to change this to returning the text Week1, Week2, Week3. How would I
go about this? Below is an example of what I am talking about.

**************************************************

Current Column Headings:

12/03/04 12/10/04 12/17/04 12/24/04

Desired Column Headings:

Week1 Week2 Week3 Week4

************************************************



Thanks for all of your help.

Chad Lucy

 

[Guest]

Duane,
I played around with my project a little bit and was able to set up my
query to function like you specified below. I still have one problem
though....When creating a report using this query as the record source it
will not allow me to reference weeks that have not already taken place.

For example:

I would like a report with these headings:

Week1 Week2 Week3 Week4 Week5

Unfortunaly, when creating text boxes to output this data to, report will
not run, it will come up with an error message saying that Week 4 or Week5
(Or whatever the control source oif the control is) cannot be found. I would
like to have it be 0 or null instead... is there a way to do this?

Thanks again for your help.

Chad

Here is a method that does this with Months. You should be able to
re-purpose the solution for weeks.
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 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

I made a crosstab query that returns a date as the column heading. I would
like to change this to returning the text Week1, Week2, Week3. How would I
go about this? Below is an example of what I am talking about.

**************************************************

Current Column Headings:

12/03/04 12/10/04 12/17/04 12/24/04

Desired Column Headings:

Week1 Week2 Week3 Week4

************************************************



Thanks for all of your help.

Chad Lucy

 

[Guest]

Duane,
SOrry for all of the posts but if I could do this one thing (which could
be easy) it would solve all of my problems. Is there a way that I could
incorporate the below example as well as have the output return the column
headers of Week1 Week2 Week3 Week4 Week5?

This would help a lot. I have seen peope use the In Function under pivot to
do this tpye of thing but I have not been able to get my values to go under
each column. Please help when you get a chance.

Thanks,
Chad

Here is a method that does this with Months. You should be able to
re-purpose the solution for weeks.
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 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

I made a crosstab query that returns a date as the column heading. I would
like to change this to returning the text Week1, Week2, Week3. How would I
go about this? Below is an example of what I am talking about.

**************************************************

Current Column Headings:

12/03/04 12/10/04 12/17/04 12/24/04

Desired Column Headings:

Week1 Week2 Week3 Week4

************************************************



Thanks for all of your help.

Chad Lucy

 

[Duane Hookom]

If you want to change from a date value to something like "week1" then how
would you know which date value corresponds with which date? That is why I
suggest you compare the date value to another date (from a form control).
You could use Date() rather than the control. You could also compare to the
minimum date value in your table.

--
Duane Hookom
MS Access MVP

Duane,
SOrry for all of the posts but if I could do this one thing (which could
be easy) it would solve all of my problems. Is there a way that I could
incorporate the below example as well as have the output return the column
headers of Week1 Week2 Week3 Week4 Week5?

This would help a lot. I have seen peope use the In Function under pivot to
do this tpye of thing but I have not been able to get my values to go under
each column. Please help when you get a chance.

Thanks,
Chad

Here is a method that does this with Months. You should be able to
re-purpose the solution for weeks.
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 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

I made a crosstab query that returns a date as the column heading. I would
like to change this to returning the text Week1, Week2, Week3. How

would
I

go about this? Below is an example of what I am talking about.

**************************************************

Current Column Headings:

12/03/04 12/10/04 12/17/04 12/24/04

Desired Column Headings:

Week1 Week2 Week3 Week4

************************************************



Thanks for all of your help.

Chad Lucy

 

[Guest]

Duane,
Thanks for your advice...I was able to play around with my project and
set it up the way you suggested (by comparing it to anorhter field.) I have
it set up so it tells me which week of the month specific data was recorded
on (these dates will show up in weekly intervals as it is uploaded once a
week for the accounting week of the company I work for.)

The output now looks like this:

Week1 Week2 Week3 Week4 Week5

A month will only have 4 or 5 weeks included in it. My problem now is that
in the first or second week of the month there are no records (Columns) for
Weeks 3 - 5 and I would like to include them on a report as Null or 0 values.
so I was wondering if there was a way to have the output of the query
include all 5 weeks no matter which week the query is run on. I hope this
makes sense, I know it is kind of confusing but I wanted to give you a brief
breakdown as to why I needed to set things up the way they were.

Again any help would be very appreciated,

Chad

If you want to change from a date value to something like "week1" then how
would you know which date value corresponds with which date? That is why I
suggest you compare the date value to another date (from a form control).
You could use Date() rather than the control. You could also compare to the
minimum date value in your table.

--
Duane Hookom
MS Access MVP

Duane,
SOrry for all of the posts but if I could do this one thing (which could
be easy) it would solve all of my problems. Is there a way that I could
incorporate the below example as well as have the output return the column
headers of Week1 Week2 Week3 Week4 Week5?

This would help a lot. I have seen peope use the In Function under pivot to
do this tpye of thing but I have not been able to get my values to go under
each column. Please help when you get a chance.

Thanks,
Chad

Here is a method that does this with Months. You should be able to
re-purpose the solution for weeks.
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 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


I made a crosstab query that returns a date as the column heading. I
would
like to change this to returning the text Week1, Week2, Week3. How would
I
go about this? Below is an example of what I am talking about.

**************************************************

Current Column Headings:

12/03/04 12/10/04 12/17/04 12/24/04

Desired Column Headings:

Week1 Week2 Week3 Week4

************************************************



Thanks for all of your help.

Chad Lucy

 

[Duane Hookom]

From my previous post
Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Change these values to match your column headings.