Creating Crosstab Reports from Crosstab Queries

[vincelts]

Hi,

Presently I am assigned by my company to use Microsoft Access 2000 to
produce some Sales Management Reports.I am able to generate the
results using Access Crosstab Query.However , when I try to use the
crosstab query to incorporate to the Access Report Design, I could not
create the same result.

The SQL statements for that were generatedfrom the Access Query are as
follows:

PARAMETERS StartDate Value, EndDate Value;
TRANSFORM Sum([Sales By Customer By Product-Summary].UCSAAM) AS Amount
SELECT [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM AS [Customer Name], [Sales By Customer By Product-
Summary].UCCUNO, Sum([Sales By Customer By Product-Summary].UCSAAM) AS
[Total Sales Amount]
FROM [Sales By Customer By Product-Summary]
WHERE ((([Sales By Customer By Product-Summary].YearMth) Between
[StartDate] And [EndDate]))
GROUP BY [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM, [Sales By Customer By Product-Summary].UCCUNO, [Sales
By Customer By Product-Summary].OKCUNM, [Sales By Customer By Product-
Summary].[Main Cust], [Sales By Customer By Product-Summary].YearMth
ORDER BY [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM
PIVOT [Sales By Customer By Product-Summary].CTTX40;

The final results should be able the output of:
1)Total Sales amount for each Customer
2)Total Sales for each product group

Eg;the coulmns will be:
customer | Cust No | main Cust | YYYYMM| Total Sales| Fs| CS| IM| Tool
|......
The column from FS onwards are the dynamic columns that depends on the
selection from the range in YYYYMM. And the Total Sales=summation of
the Product(fS+CS+...) Sales Value.

Since the product groups are not fixed,please advise how I can create
a Dynamic Crosstab report.


Thank You

 

[Duane Hookom]

There is a sample crosstab demo at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane that
reports with dynamic column headings.

 

[vincelts]

There is a sample crosstab demo athttp://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duanethat
reports with dynamic column headings.

--
Duane Hookom
Microsoft Access MVP

Hi,

Presently I am assigned by my company to use Microsoft Access 2000 to
produce some Sales Management Reports.I am able to generate the
results using Access Crosstab Query.However , when I try to use the
crosstab query to incorporate to the Access Report Design, I could not
create the same result.

The SQL statements for that were generatedfrom the Access Query are as
follows:

PARAMETERS StartDate Value, EndDate Value;
TRANSFORM Sum([Sales By Customer By Product-Summary].UCSAAM) AS Amount
SELECT [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM AS [Customer Name], [Sales By Customer By Product-
Summary].UCCUNO, Sum([Sales By Customer By Product-Summary].UCSAAM) AS
[Total Sales Amount]
FROM [Sales By Customer By Product-Summary]
WHERE ((([Sales By Customer By Product-Summary].YearMth) Between
[StartDate] And [EndDate]))
GROUP BY [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM, [Sales By Customer By Product-Summary].UCCUNO, [Sales
By Customer By Product-Summary].OKCUNM, [Sales By Customer By Product-
Summary].[Main Cust], [Sales By Customer By Product-Summary].YearMth
ORDER BY [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM
PIVOT [Sales By Customer By Product-Summary].CTTX40;

The final results should be able the output of:
1)Total Sales amount for each Customer
2)Total Sales for each product group

Eg;the coulmns will be:
customer | Cust No | main Cust | YYYYMM| Total Sales| Fs| CS| IM| Tool
|......
The column from FS onwards are the dynamic columns that depends on the
selection from the range in YYYYMM. And the Total Sales=summation of
the Product(fS+CS+...) Sales Value.

Since the product groups are not fixed,please advise how I can create
a Dynamic Crosstab report.

Thank You- Hide quoted text -

- Show quoted text -

Thank you Duane, for your speedy response. Will try it out and keep
you update of its results.

 

[vincelts]

Hi Duane,

Thanks for the useful advise.Have manage to generate the dynamic
fields label but still have difficulty to output the data correspond
to the dynamic fields. Eg. Dynamic Field is refering to Product
Groups: "Flat Spring";Wire Spring","
;Tooling" etc.. and its corresponding values are
$200000;$2350;$100000.

By the way, also would like to inform you that below website that you
recommended does not have any file to be downloaded as a reference:

http://www.invisibleinc.com/divFiles.cfm?divDivID=4


Regards

Vincent

There is a samplecrosstabdemo athttp://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duanethat
reports withdynamiccolumn headings.

Hi,
Presently I am assigned by my company to use Microsoft Access 2000 to
produce some Sales Management Reports.I am able to generate the
results using AccessCrosstabQuery.However , when I try to use the
crosstabquery to incorporate to the AccessReportDesign, I could not
create the same result.
The SQL statements for that were generatedfrom the Access Query are as
follows:
PARAMETERS StartDate Value, EndDate Value;
TRANSFORM Sum([Sales By Customer By Product-Summary].UCSAAM) AS Amount
SELECT [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM AS [Customer Name], [Sales By Customer By Product-
Summary].UCCUNO, Sum([Sales By Customer By Product-Summary].UCSAAM) AS
[Total Sales Amount]
FROM [Sales By Customer By Product-Summary]
WHERE ((([Sales By Customer By Product-Summary].YearMth) Between
[StartDate] And [EndDate]))
GROUP BY [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM, [Sales By Customer By Product-Summary].UCCUNO, [Sales
By Customer By Product-Summary].OKCUNM, [Sales By Customer By Product-
Summary].[Main Cust], [Sales By Customer By Product-Summary].YearMth
ORDER BY [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM
PIVOT [Sales By Customer By Product-Summary].CTTX40;
The final results should be able the output of:
1)Total Sales amount for each Customer
2)Total Sales for each product group
Eg;the coulmns will be:
customer | Cust No | main Cust | YYYYMM| Total Sales| Fs| CS| IM| Tool
|......
The column from FS onwards are thedynamiccolumns that depends on the
selection from the range in YYYYMM. And the Total Sales=summation of
the Product(fS+CS+...) Sales Value.
Since the product groups are not fixed,please advise how I can create
aDynamicCrosstabreport.
Thank You- Hide quoted text -

- Show quoted text -

Thank you Duane, for your speedy response. Will try it out and keep
you update of its results.- Hide quoted text -

- Show quoted text -

 

[Duane Hookom]

I'm not sure if you have tried the link that I provided and if so, where your
issue is. Did you get the multiple column subreport to display the column
titles?

I haven't posted that link for a long time. Most of the samples are either
at Roger Carlson's site
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane or my site
www.access.hookom.net


--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Thanks for the useful advise.Have manage to generate the dynamic
fields label but still have difficulty to output the data correspond
to the dynamic fields. Eg. Dynamic Field is refering to Product
Groups: "Flat Spring";Wire Spring","
;Tooling" etc.. and its corresponding values are
$200000;$2350;$100000.

By the way, also would like to inform you that below website that you
recommended does not have any file to be downloaded as a reference:

http://www.invisibleinc.com/divFiles.cfm?divDivID=4


Regards

Vincent

There is a samplecrosstabdemo athttp://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duanethat
reports withdynamiccolumn headings.

:
Hi,

Presently I am assigned by my company to use Microsoft Access 2000 to
produce some Sales Management Reports.I am able to generate the
results using AccessCrosstabQuery.However , when I try to use the
crosstabquery to incorporate to the AccessReportDesign, I could not
create the same result.

The SQL statements for that were generatedfrom the Access Query are as
follows:

PARAMETERS StartDate Value, EndDate Value;
TRANSFORM Sum([Sales By Customer By Product-Summary].UCSAAM) AS Amount
SELECT [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM AS [Customer Name], [Sales By Customer By Product-
Summary].UCCUNO, Sum([Sales By Customer By Product-Summary].UCSAAM) AS
[Total Sales Amount]
FROM [Sales By Customer By Product-Summary]
WHERE ((([Sales By Customer By Product-Summary].YearMth) Between
[StartDate] And [EndDate]))
GROUP BY [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM, [Sales By Customer By Product-Summary].UCCUNO, [Sales
By Customer By Product-Summary].OKCUNM, [Sales By Customer By Product-
Summary].[Main Cust], [Sales By Customer By Product-Summary].YearMth
ORDER BY [Sales By Customer By Product-Summary].[Main Cust], [Sales By
Customer By Product-Summary].YearMth, [Sales By Customer By Product-
Summary].OKCUNM
PIVOT [Sales By Customer By Product-Summary].CTTX40;

The final results should be able the output of:
1)Total Sales amount for each Customer
2)Total Sales for each product group

Eg;the coulmns will be:
customer | Cust No | main Cust | YYYYMM| Total Sales| Fs| CS| IM| Tool
|......
The column from FS onwards are thedynamiccolumns that depends on the
selection from the range in YYYYMM. And the Total Sales=summation of
the Product(fS+CS+...) Sales Value.

Since the product groups are not fixed,please advise how I can create
aDynamicCrosstabreport.

Thank You- Hide quoted text -

- Show quoted text -

Thank you Duane, for your speedy response. Will try it out and keep
you update of its results.- Hide quoted text -

- Show quoted text -