If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
(SELECT
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] |
#3
|
|||
|
|||
Sum a count total
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 |
#4
|
|||
|
|||
Sum a count total
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. |
#5
|
|||
|
|||
(SELECT
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] |
Thread Tools | |
Display Modes | |
|
|