Report sum totals of customes sales between dates

[Bicester]

In a Cost Analysis Report (Report Footer) I'm trying to create totals based on between dates and customer groups. ie all "customer 1" sales for the month of April. I've manage to get totals for all the customers and costs of sales for any particular month but when I try to do this for a particular customer it just adds up ALL entries for a particular customer on the query and ignores the "between" dates. So I will get the totals for "customer 1" for March, April, MAy etc and not just April.

In the query I have:-
Between [Startdate] And [Finishdate]

In the report control source I have:-
=DSum("[Sales Value1]","cost analysis","[Group] = 'Customer 1'")

where Sales Value1 is the column, cost analysis is the query and Customer 1 is the customer.

When I open the query it asks me for a between date which works. I also have total colums which do work on the report. ie =Sum([Sales Value1]) and adds up the total sales for all customers in the column between the dates entered.

I'm just trying to brake down the sales values into customers for any date period.

 

[dhookom]

It sounds like you would need text boxes for each customer with the customer name or ID 'hard-coded' into the expression. This can be done with an expession like:
=Sum([Sales Value1] * Abs([Group] = "Customer 1"))

I would use a subreport based on a totals query that totals Sales Value1 by Group. You should abandon parameter prompt queries in favor of referencing a value in a control on a form.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]