Create a report using cross tab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a cross tab query using dates as column headers. These dates will change weekly. Is there a way to create a report from this query. Thanks in advance.
 
This is a reply I gave on this news group 2 days ago. It can work equally
well with a single day rather than month.

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance if your column headings were months:
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|Parameters
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 Hookom
MS Access MVP


ddurman said:
I have a cross tab query using dates as column headers. These dates will
Click to expand...
change weekly. Is there a way to create a report from this query. Thanks
in advance.
 
Where exactly do you define the cloumn headings? In the
query itself or in the report?
-----Original Message-----
This is a reply I gave on this news group 2 days ago. It can work equally
well with a single day rather than month.

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance if your column headings were months:
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|Parameters
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 Hookom
MS Access MVP


I have a cross tab query using dates as column
Click to expand...
headers. These dates will
change weekly. Is there a way to create a report from this query. Thanks
in advance.


.
Click to expand...
 
There is a column headings property in the crosstab query.

--
Duane Hookom
MS Access MVP


doreen said:
Where exactly do you define the cloumn headings? In the
query itself or in the report?
-----Original Message-----
This is a reply I gave on this news group 2 days ago. It can work equally
well with a single day rather than month.

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance if your column headings were months:
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|Parameters
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 Hookom
MS Access MVP


I have a cross tab query using dates as column
Click to expand...
headers. These dates will
change weekly. Is there a way to create a report from this query. Thanks
in advance.


.
Click to expand...
Click to expand...
 
It works perfectly!!!
Thank you for all of your advice!
-----Original Message-----
There is a column headings property in the crosstab query.

--
Duane Hookom
MS Access MVP


Where exactly do you define the cloumn headings? In the
query itself or in the report?
-----Original Message-----
This is a reply I gave on this news group 2 days ago.
Click to expand...
It
can work equally
well with a single day rather than month.

Try not to use "absolute" column headings for dates.
Click to expand...
You
could possibly use
"relative" dates. For instance if your column headings were months:
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|Parameters
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 Hookom
MS Access MVP


"ddurman" <[email protected]> wrote
Click to expand...
in
message
news:41F0A4DE-900D-4C1A-A2A9- (e-mail address removed)...
I have a cross tab query using dates as column headers. These dates will
change weekly. Is there a way to create a report from this query. Thanks
in advance.


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top