Accumulate in the report

[trident]

I have created a report by running a query. in the report the following information are there:


1-Invoice Date
2-Total Sale
3-Total Cost
4-Internal/External (choice)
5-Staff Name

I was able to get the right information on screen.

below is a sample data

Invoice Date	Total Sale	Total cost	Int/Ext	Staff
7/1/2017	4000	2000	Int	A
7/1/2017	3000	1500	Int	A
7/2/2017	1500	100	Ext	A
7/2/2017	6000	500	Ext	B

The Total Sale is a text box with the formula =sum(([quantity]*[saleprice])-[discount]))

I want to accumulate based on the staff and int/ext, so i made another TextBox1 with my formula as:

=iif([staff]="A" and [int/ext]="Int",sum(([quantity]*[saleprice])-[discount])),"")

When I made this formula, error was shown on TextBox1, what I do not understande is error was also shown on Total Sale, even thought they have different formulas.

Can you please help me on this.

Thank you.

Trident

[ranman256]

Can't you just run a sum query to get the totals?

[trident]

I tried putting this in a sum query but problem is my grouping.

but i will try your suggestion, it may take a lot of queries, but it will reach the goal.

Will inform if it works.

thanks.

[orange]

Suggest you post a copy of the database. Your sample data has your aggregation for TotalSale etc.
You are showing us expressions with quantity, saleprice and discount... but no values.
I would also assume you have InvoiceNumber somewhere in your query/report.

[June7]

Try:

=Sum(IIf([staff]="A" And [int/ext]="Int", [quantity]*[saleprice]-[discount], 0))