Crosstab Report

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

Guest

I have a budget report (you may remember from the "Duplicate values in
subreport" on the 13th) anyhow, I want to put totals from each column (fund#)
but filter the total by the importance of the information.

Fund #
Importa. 100 200 300 400
1 10 5 10 15
1 1 5 12 10
2 10 5 10 15
3 1 5 12 10
4 10 5 10 15
2 1 5 12 10
_____________________________________

(End of report not page)
1 Total 11 10 22 25
2 Total 11 10 22 25
3 Total 11 10 22 25
4 Total 11 10 22 25

How can I acheive this?
 
Create a totals query based on your crosstab query. Use this query as the
record source of a subreport which you can place in your report footer.

I'm not sure how your data from the top results in the bottom display....
 
I've tried it; but the error reads:
"You can't use a pass through query or a non fixed column crosstab query as
a record source for a subform or subreport.
Before you bind the subform or subreport to a crosstab query, set the
Query's ColumnHeadings Property."

I've tried but it still gives me the same message. Suggestions?
 
So I tried changing the column headings to read the fund columns; however
when I place the subreport in the report, the totals I receive are only for
the least important information. It doesn't print the more important totals.
Suggestions?
 
Add the fund numbers to the Column Headings property of your crosstab query.
Maybe you need to provide the SQL view of your crosstab query.
 
So I tried changing the column headings to read the fund columns; however
when I place the subreport in the report, the totals I receive are only for
the least important information. It doesn't print the more important totals.
Suggestions?


Here's the SQL view for the subreport Funds by Category:
TRANSFORM Sum(BudgetAmtByCategory.BudgetAmt) AS SumOfBudgetAmt
SELECT BudgetAmtByCategory.Importance, BudgetAmtByCategory.CategoryDescription
FROM BudgetAmtByCategory
WHERE (((BudgetAmtByCategory.Year)="2006B"))
GROUP BY BudgetAmtByCategory.Importance,
BudgetAmtByCategory.CategoryDescription
PIVOT BudgetAmtByCategory.[Fund#] In
("700","710","715","720","730","750","760","770","780","790","800","810","825","830","840","845","850","860","870","890","899");

AND here's the SQL view for the main report:
TRANSFORM Val(Nz(First(BudgetAmtByCategory.BudgetAmt),0)) AS FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription, BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance, Account.Name
FROM BudgetAmtByCategory INNER JOIN Account ON BudgetAmtByCategory.[Acct#] =
Account.[Acct#]
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#], BudgetAmtByCategory.Importance, Account.Name,
Account.Description, Account.Name
PIVOT BudgetAmtByCategory.[Fund#];

I can't figure out what I'm missing. Can you?
 
Have you told us what you mean by "least important" and "more important"? I
don't see anything in your structure or messages that suggests this key
information.

--
Duane Hookom
MS Access MVP


Jan said:
So I tried changing the column headings to read the fund columns; however
when I place the subreport in the report, the totals I receive are only
for
the least important information. It doesn't print the more important
totals.
Suggestions?


Here's the SQL view for the subreport Funds by Category:
TRANSFORM Sum(BudgetAmtByCategory.BudgetAmt) AS SumOfBudgetAmt
SELECT BudgetAmtByCategory.Importance,
BudgetAmtByCategory.CategoryDescription
FROM BudgetAmtByCategory
WHERE (((BudgetAmtByCategory.Year)="2006B"))
GROUP BY BudgetAmtByCategory.Importance,
BudgetAmtByCategory.CategoryDescription
PIVOT BudgetAmtByCategory.[Fund#] In
("700","710","715","720","730","750","760","770","780","790","800","810","825","830","840","845","850","860","870","890","899");

AND here's the SQL view for the main report:
TRANSFORM Val(Nz(First(BudgetAmtByCategory.BudgetAmt),0)) AS
FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance, Account.Name
FROM BudgetAmtByCategory INNER JOIN Account ON BudgetAmtByCategory.[Acct#]
=
Account.[Acct#]
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#], BudgetAmtByCategory.Importance, Account.Name,
Account.Description, Account.Name
PIVOT BudgetAmtByCategory.[Fund#];

I can't figure out what I'm missing. Can you?
--
Jan

--
Jan


Duane Hookom said:
Add the fund numbers to the Column Headings property of your crosstab
query.
Maybe you need to provide the SQL view of your crosstab query.
Click to expand...
Click to expand...
 
It is specified by the name Importance in my structure. How it works is that
every account has a Category Description and they are ranked by importance.
The most important categorical description is Revenue followed by important
expense accounts. I need to print Revenue separate from the expense accounts
as it's an important account. I can lump all the expense categories into one
but I need the Revenue account separated. What I receive when I place the
subreport into the report footer is only one of the expense accounts. I
don't receive all of the accounts listed. Could the reason be because it
recognizes the last category on the page and only prints that one? How can I
remedy this?

I tried using a Pivot Table but that's not going to work for me neither.
The structure isn't as refined as I would like it to be. Any suggestions?
--
Jan


Duane Hookom said:
Have you told us what you mean by "least important" and "more important"? I
don't see anything in your structure or messages that suggests this key
information.

--
Duane Hookom
MS Access MVP


Jan said:
So I tried changing the column headings to read the fund columns; however
when I place the subreport in the report, the totals I receive are only
for
the least important information. It doesn't print the more important
totals.
Suggestions?


Here's the SQL view for the subreport Funds by Category:
TRANSFORM Sum(BudgetAmtByCategory.BudgetAmt) AS SumOfBudgetAmt
SELECT BudgetAmtByCategory.Importance,
BudgetAmtByCategory.CategoryDescription
FROM BudgetAmtByCategory
WHERE (((BudgetAmtByCategory.Year)="2006B"))
GROUP BY BudgetAmtByCategory.Importance,
BudgetAmtByCategory.CategoryDescription
PIVOT BudgetAmtByCategory.[Fund#] In
("700","710","715","720","730","750","760","770","780","790","800","810","825","830","840","845","850","860","870","890","899");

AND here's the SQL view for the main report:
TRANSFORM Val(Nz(First(BudgetAmtByCategory.BudgetAmt),0)) AS
FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance, Account.Name
FROM BudgetAmtByCategory INNER JOIN Account ON BudgetAmtByCategory.[Acct#]
=
Account.[Acct#]
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#], BudgetAmtByCategory.Importance, Account.Name,
Account.Description, Account.Name
PIVOT BudgetAmtByCategory.[Fund#];

I can't figure out what I'm missing. Can you?
--
Jan

--
Jan


Duane Hookom said:
Add the fund numbers to the Column Headings property of your crosstab
query.
Maybe you need to provide the SQL view of your crosstab query.

--
Duane Hookom
MS Access MVP
--

I've tried it; but the error reads:
"You can't use a pass through query or a non fixed column crosstab
query
as
a record source for a subform or subreport.
Before you bind the subform or subreport to a crosstab query, set the
Query's ColumnHeadings Property."

I've tried but it still gives me the same message. Suggestions?
--
Jan


:

Create a totals query based on your crosstab query. Use this query as
the
record source of a subreport which you can place in your report
footer.

I'm not sure how your data from the top results in the bottom
display....

--
Duane Hookom
MS Access MVP
--

I have a budget report (you may remember from the "Duplicate values
in
subreport" on the 13th) anyhow, I want to put totals from each
column
(fund#)
but filter the total by the importance of the information.

Fund #
Importa. 100 200 300 400
1 10 5 10 15
1 1 5 12 10
2 10 5 10 15
3 1 5 12 10
4 10 5 10 15
2 1 5 12 10
_____________________________________

(End of report not page)
1 Total 11 10 22 25
2 Total 11 10 22 25
3 Total 11 10 22 25
4 Total 11 10 22 25

How can I acheive this?
Click to expand...
Click to expand...
Click to expand...
 
I still don't have a clue based on your first posting what is important and
what is not. Your two data displays don't seem to compute.

However, make sure that your subreport control doesn't have any values in
the Link Master/Child properties.

--
Duane Hookom
MS Access MVP


Jan said:
It is specified by the name Importance in my structure. How it works is
that
every account has a Category Description and they are ranked by
importance.
The most important categorical description is Revenue followed by
important
expense accounts. I need to print Revenue separate from the expense
accounts
as it's an important account. I can lump all the expense categories into
one
but I need the Revenue account separated. What I receive when I place the
subreport into the report footer is only one of the expense accounts. I
don't receive all of the accounts listed. Could the reason be because it
recognizes the last category on the page and only prints that one? How
can I
remedy this?

I tried using a Pivot Table but that's not going to work for me neither.
The structure isn't as refined as I would like it to be. Any suggestions?
--
Jan


Duane Hookom said:
Have you told us what you mean by "least important" and "more important"?
I
don't see anything in your structure or messages that suggests this key
information.

--
Duane Hookom
MS Access MVP


Jan said:
So I tried changing the column headings to read the fund columns;
however
when I place the subreport in the report, the totals I receive are only
for
the least important information. It doesn't print the more important
totals.
Suggestions?


Here's the SQL view for the subreport Funds by Category:
TRANSFORM Sum(BudgetAmtByCategory.BudgetAmt) AS SumOfBudgetAmt
SELECT BudgetAmtByCategory.Importance,
BudgetAmtByCategory.CategoryDescription
FROM BudgetAmtByCategory
WHERE (((BudgetAmtByCategory.Year)="2006B"))
GROUP BY BudgetAmtByCategory.Importance,
BudgetAmtByCategory.CategoryDescription
PIVOT BudgetAmtByCategory.[Fund#] In
("700","710","715","720","730","750","760","770","780","790","800","810","825","830","840","845","850","860","870","890","899");

AND here's the SQL view for the main report:
TRANSFORM Val(Nz(First(BudgetAmtByCategory.BudgetAmt),0)) AS
FirstOfBudgetAmt
SELECT BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#],
BudgetAmtByCategory.Importance, Account.Name
FROM BudgetAmtByCategory INNER JOIN Account ON
BudgetAmtByCategory.[Acct#]
=
Account.[Acct#]
GROUP BY BudgetAmtByCategory.CategoryDescription,
BudgetAmtByCategory.[Acct#], BudgetAmtByCategory.Importance,
Account.Name,
Account.Description, Account.Name
PIVOT BudgetAmtByCategory.[Fund#];

I can't figure out what I'm missing. Can you?
--
Jan

--
Jan


:

Add the fund numbers to the Column Headings property of your crosstab
query.
Maybe you need to provide the SQL view of your crosstab query.

--
Duane Hookom
MS Access MVP
--

I've tried it; but the error reads:
"You can't use a pass through query or a non fixed column crosstab
query
as
a record source for a subform or subreport.
Before you bind the subform or subreport to a crosstab query, set
the
Query's ColumnHeadings Property."

I've tried but it still gives me the same message. Suggestions?
--
Jan


:

Create a totals query based on your crosstab query. Use this query
as
the
record source of a subreport which you can place in your report
footer.

I'm not sure how your data from the top results in the bottom
display....

--
Duane Hookom
MS Access MVP
--

I have a budget report (you may remember from the "Duplicate
values
in
subreport" on the 13th) anyhow, I want to put totals from each
column
(fund#)
but filter the total by the importance of the information.

Fund #
Importa. 100 200 300 400
1 10 5 10 15
1 1 5 12 10
2 10 5 10 15
3 1 5 12 10
4 10 5 10 15
2 1 5 12 10
_____________________________________

(End of report not page)
1 Total 11 10 22 25
2 Total 11 10 22 25
3 Total 11 10 22 25
4 Total 11 10 22 25

How can I acheive this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top