Sum a count total

Hi,

I have a query that shows the following information

It looks at a specific table (table 1) then groups by sales agent, then
groups the tariffs that have been sold by that sales agent and then the query
has a count on tariffs. I have then built a report to display the results
which would look like this :-

Dave Brown
Standard Tariff 1
EDP Tariff 2

What I want is a nother function in the query that will then sum the count
coloumn so the example above the sum coloumn would be 3. i then need the
report to sort by the sum.

Thanks

Glenn wrote:
I have a query that shows the following information

It looks at a specific table (table 1) then groups by sales agent, then
groups the tariffs that have been sold by that sales agent and then the query
has a count on tariffs. I have then built a report to display the results
which would look like this :-

Dave Brown
Standard Tariff 1
EDP Tariff 2

What I want is a nother function in the query that will then sum the count
coloumn so the example above the sum coloumn would be 3. i then need the
report to sort by the sum.


You can use a subquery to do that. Add a calculated field
to your query that looks something like:

(SELECT COUNT(*) FROM )Table1 As X WHERE X.agent =
Table1.agent) AS AgentTariffs

Set this field's Group By cell to Expression

--
Marsh
MVP [MS Access]

On Wed, 28 Apr 2010 05:39:01 -0700, Glenn
wrote:

I would create that sum total in the report footer, not in the query.
Create a field in the footer and set the ControlSource to:
=sum(myCountOfTariff)
(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP


Hi,

I have a query that shows the following information

It looks at a specific table (table 1) then groups by sales agent, then
groups the tariffs that have been sold by that sales agent and then the query
has a count on tariffs. I have then built a report to display the results
which would look like this :-

Dave Brown
Standard Tariff 1
EDP Tariff 2

What I want is a nother function in the query that will then sum the count
coloumn so the example above the sum coloumn would be 3. i then need the
report to sort by the sum.

Thanks

Tom, I suspect that you missed the last part of Glen's
objective?
--
Marsh
MVP [MS Access]


Tom van Stiphout wrote:
I would create that sum total in the report footer, not in
the query.
[snip]

Glenn wrote:
[snip]
i then need the report to sort by the sum.

IIRC, there are situations where a report will not sort on a value derived
from a subquery. If this happens, you may need to create a totals query and
join it to your report's recordsource query.

--
Duane Hookom
MS Access MVP


"Marshall Barton" wrote in message
...
Glenn wrote:
I have a query that shows the following information

It looks at a specific table (table 1) then groups by sales agent, then
groups the tariffs that have been sold by that sales agent and then the
query
has a count on tariffs. I have then built a report to display the results
which would look like this :-

Dave Brown
Standard Tariff 1
EDP Tariff 2

What I want is a nother function in the query that will then sum the count
coloumn so the example above the sum coloumn would be 3. i then need the
report to sort by the sum.


You can use a subquery to do that. Add a calculated field
to your query that looks something like:

(SELECT COUNT(*) FROM )Table1 As X WHERE X.agent =
Table1.agent) AS AgentTariffs

Set this field's Group By cell to Expression

--
Marsh
MVP [MS Access]