Problem with Totals / Sum / Count ??

[z.ghulam]

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

 

[Arvin Meyer [MVP]]

You can't include the date range in the query except as a where clause or
expression:

SELECT [Order Type], Sum([Total Field]) AS NewTotal
FROM Table1
WHERE ((([DateField]) Between [Enter start date] And [Enter end date]))
GROUP BY [Order Type];

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[z.ghulam]

Thanks for that.

The problem is that the total field isnt a field, but based on a Count
Expression within the query - is there a way to sum them.
The current SQL I have is:

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]));

 

[z.ghulam]

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]));

 

[Wayne Gillespie]

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

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

 

[z.ghulam]

Cheers Wayne,

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