Displaying a cross tab query in report

[Guest]

Hi

I am having much trouble with displaying a cross tab query in a report, I
basically have a tbl (tbl expenses categories) that stores the names of
expenses categories such as business travel.

And another tbl (tbl expenses details) that stores the expenses items price
of expenses details e.g. £22.99 Meal somewhere.

The aim of the Qxtb is to find all expenses details and sum up the expenses
items price according to category, this qry works fine but when I display the
query results in rpt the category names are duplicated and category totals
are displayed across the page.

I have tried
Several report formats
Exported rpt data to excel
Exported rpt data to word
Tried to display the qry as chart rpt and pivot table.

Thanks stew

 

[Duane Hookom]

You really didn't give us much to go on.

Does the crosstab itself look ok? What is the record source of the report?
Can you provide us with the SQL view?

 

[Guest]

The qurey its self is fine and displays all category totals across the
results grid as nomal, but i cant find away of producing a rpt of this qry
that displays the labels and category totals on one page without duplicated
labels

Below is the SQL view of the Qxtb

TRANSFORM Sum(tblExpensesDetails.ExpensesItemsPrice) AS
SumOfExpensesItemsPrice
SELECT tblExpensesCodes.ExpensesCategory
FROM tblExpensesCodes INNER JOIN tblExpensesDetails ON
tblExpensesCodes.ExpenseCode = tblExpensesDetails.ExpenseCode
GROUP BY tblExpensesCodes.ExpensesCategory
PIVOT tblExpensesCodes.ExpensesCategory;

Thank you for reply so quickly stew

 

[Duane Hookom]

Are there too many columns to fit across your page? What do you mean by
"labels"? Are these column headings that you have added or something else?

 

[Guest]

In reports the labels for the result of this cross tab qry being (expenses
categories) are repeated across the report either horizontally if the report
is tabular or vertically if the report is columnar.

The expenses total to each category are displayed on seprate pages of the
report

 

[Duane Hookom]

I still don't understand where you are getting duplicates. Maybe you could
type some displayed results in a reply to give us an idea of what you are
seeing?

 

[Guest]

RPT header

Page 1 of the report
Business Travel £59.99
Computers
Accomdation
Etc

When you go to page 2 of the report this would be the view

Page 2 of the report
Business Travel
Computers £22.99
Accomdation
Etc

Page 3 of the report
Business Travel
Computers
Accomdation £567.87
Etc

and so on

Unfortuantley i have many more categories so the rpt is around 20 pages
because each total of category is displayed on diffrent page of the report
along with all category labels (with thier totals blank) My end users wish to
print this rpt on regular basis and with the sheer amount of pages this is a
problem. If the labels where not repeating and all category totals where
displayed its likely this report would only be 1 page of a4 to print

I beginning to think this is something you have to accpet with showing
results of cross tab qry's in reports would i be right in saying this?

Thank you once agian for you help

p.s. i have tried mulitple sortings and groupings and the same problem
occurs.

 

[Duane Hookom]

looking back at your SQL, I think I see the issue. You have ExpensesCategory
as both a column heading and a row heading in your crosstab. Try:

TRANSFORM Sum(tblExpensesDetails.ExpensesItemsPrice) AS
SumOfExpensesItemsPrice
SELECT "Totals" As Totals
FROM tblExpensesCodes INNER JOIN tblExpensesDetails ON
tblExpensesCodes.ExpenseCode = tblExpensesDetails.ExpenseCode
GROUP BY "Totals"
PIVOT tblExpensesCodes.ExpensesCategory;

 

[Guest]

Thanks very much this works the report now displays the category totals on
one page without repeating labels
Thanks very much for your help
Its greatly appreciated

Stewart Turner