(SELECT
in [Queries]
|
From: Marshall Barton on 28 Apr 2010 10:25 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]
From: Duane Hookom on 28 Apr 2010 21:38 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" <marshbarton(a)wowway.com> wrote in message news:edggt5drgqrfh0agd8elsjn06qrt6k9n7g(a)4ax.com... > 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]
|
Pages: 1 Prev: Exclude data from field, when another field equals a certain numbe Next: vlookup in ms access |