Total Sum depending on category

[erick-flores]

Hello all

I want my report to show the total sum depending in the category. The
categories are Lodging, Meals, Auto, Bus. This is how my report looks
like:

Date Description Category Account # Amount
10/10 test Meals 2343 34.34
10/11 test3 Auto 34343 45.90
10/12 test2 Bus 34343 56.09
10/12 test3 Meals 435 34.00
10/13 test4 Auto 343 10.00

And I want to show the total for EACH category at the end of the
report:
Total Meals: 68.34
Total Auto: 55.90
Total Bus: 56.09

The report is group by ExpenseNumberID

Any ideas? Thank you in advance

 

[Al Campagna]

Erick,
I don't see where you show the ExpenseNumberID in your sample data, so I'll work with
what you show.
The way to get your totals is to Group and sort by Category, and also sort by
ExpenseDate (don't use the name "Date" for a field)
In the Category Group footer, a text control with = Sum(Category) as the contol source
would yield the totals

 

[erick-flores]

I understand what you r saying, but I want the totals (category) to be
shown only at the end of my report (so i can keep each expense record
one below another one, in other words in order), and because right now
i have the ExpenseReportID as my principal group I can not do the
category group to be display at the end of the report, you know what I
mean?

I cant have two groups in one Footer. Any ideas?

 

[erick-flores]

I was thinking of doing a query that will display
expensecategory=meals=amount = total, something like that. I ran a
query and it gave me the information that I want, this is how it looks:
SELECT [Expense Details].ExpenseCategoryID, [Expense
Categories].ExpenseCategory, Sum([Expense Details].ExpenseItemAmount)
AS SumOfExpenseItemAmount, Sum([Expense Details].ExpenseItemAmountCo)
AS SumOfExpenseItemAmountCo, [Expense Reports].ExpenseReportID
FROM (Employees INNER JOIN [Expense Reports] ON Employees.EmployeeID =
[Expense Reports].EmployeeID) INNER JOIN ([Expense Categories] RIGHT
JOIN [Expense Details] ON [Expense Categories].ExpenseCategoryID =
[Expense Details].ExpenseCategoryID) ON [Expense
Reports].ExpenseReportID = [Expense Details].ExpenseReportID
GROUP BY [Expense Details].ExpenseCategoryID, [Expense
Categories].ExpenseCategory, [Expense Reports].ExpenseReportID
HAVING ((([Expense Categories].ExpenseCategory)="MEALS"));

but I dont know if I can use this query for one field in my report, can
I? or do I need to do any changes so I can use it in my report

 

[Al Campagna]

Erick,
I think I'm still not quite getting what you're trying to do.
Have you considered a sub-report at the end of the report (Report Footer) based on a
Totals query against each Category... or however you want it. Just develop a query that
returns the results you wnat, and use that as the RecordSource for the subreport.

 

[erick-flores]

Yes, thats what I though...I created a subreport and run the query
against each category. Thanks for your help