Problem with Totals / Sum / Count ??

Hi,

I've been trying to work this little problem out for a while, but to no
avail. I'll start with an example as I've asked before, but not sure if
it was very clear what I'm trying to do.
I have a query which produces the following

Order Type Total Date
Eth 2 09/01
LL 39 09/01
LL 8 10/01
LL 15 11/01
DSL 1 10/01

The Total is based on a COUNT expression which counts the number of
order types in a day.
The date range is pre-selected by the user.

What I would like is for the query to add the totals of the same order
within the date range so I get:

Order Type Total
Eth 2
LL 62
DSL 1

I have a couple of reports which need this tweaking - I'd appreciate
any feedback . help anyone has to offer.
Cheers

re: Problem with Totals / Sum / Count ??

This is the SQL I currently have to produce the example above:

SELECT [Order Type (For Combo)].[Order Type], Count(*) AS Total, [Order
Link].[Allocation Date]
FROM [Order Type (For Combo)] INNER JOIN [Order Link] ON [Order Type
(For Combo)].[Order Type ID] = [Order Link].[Order Type ID]
GROUP BY [Order Type (For Combo)].[Order Type], [Order
Link].[Allocation Date], [Order Link].[Order Type ID]
HAVING ((([Order Link].[Allocation Date]) Between [Forms]![Calender
Test]![BeginningDate] And [Forms]![Calender Test]![EndingDate]));

re: Problem with Totals / Sum / Count ??

On 24 Jan 2006 03:07:07 -0800, z.ghulam@gmail.com wrote:
[color=blue]
>Hi,
>
>I've been trying to work this little problem out for a while, but to no
>avail. I'll start with an example as I've asked before, but not sure if
>it was very clear what I'm trying to do.
>I have a query which produces the following
>
>Order Type Total Date
>Eth 2 09/01
>LL 39 09/01
>LL 8 10/01
>LL 15 11/01
>DSL 1 10/01
>
>The Total is based on a COUNT expression which counts the number of
>order types in a day.
>The date range is pre-selected by the user.
>
>What I would like is for the query to add the totals of the same order
>within the date range so I get:
>
>Order Type Total
>Eth 2
>LL 62
>DSL 1
>
>I have a couple of reports which need this tweaking - I'd appreciate
>any feedback . help anyone has to offer.
>Cheers[/color]

If the Date field is only being used to set the date range, in the query design change the Date column from Group By to
Where.


Wayne Gillespie
Gosford NSW Australia

re: Problem with Totals / Sum / Count ??

Cheers Wayne,

Thats sorted the problem on all my reports out very nicely.
Many Thanks