Re: Count Problem

Try saving this query as, say, qryIntermediary:

SELECT Industry, Sum(Costs) As SumOfCosts, Customer
FROM MyTable
GROUP BY Industry, Customer

then another query based on that query:

SELECT Industry, Sum(SumOfCosts) As Costs, Count(*) As NoOfCustomers
FROM qryIntermediary
GROUP BY Industry

It should actually be possible to do that in a single query:

SELECT Industry, Sum(SumOfCosts) As Costs, Count(*) As NoOfCustomers
FROM
(SELECT Industry, Sum(Costs) As SumOfCosts, Customer
FROM MyTable
GROUP BY Industry, Customer) AS Subquery
GROUP BY Industry

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"TommoUK" <TommoUK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:208D60ED-759A-4851-B692-F157BE96AD04@xxxxxxxxxxxxxxxx
Probably a better example!

Example of tbMRAInput:
Industry Costs Customer
A 100 XYZ
A 50 EFG
A 25 XYZ
B 150 EFG
B 50 OPQ
C 20 XYZ

My SELECT query picks out the top 20 Industries but for this example we'll
say the top 2 (by costs). Therefore, the output would look like:

Industry Costs
A 175
B 200

The field that I need to add to this output would show the number of
customers in those industries e.g.

Industry Costs No of Customers
A 175 2
B 200 2


"TommoUK" wrote:

Thanks for the reply John.

Unfortunately, this doesn't do what I need it to. I'll try and give you a
better example now.

Example of tbMRAInput:
Industry Costs Customer
A 100 XYZ
A 50 EFG
A 25 ABC
B 150 EFG
B 50 OPQ
C 20 XYZ

My SELECT query picks out the top 20 Industries but for this example
we'll
say the top 2 (by costs). Therefore, the output would look like:

Industry Costs
A 175
B 200

The field that I need to add to this output would show the number of
customers in those industries e.g.

Industry Costs No of Customers
A 175 3
B 200 2

I hope this makes the problem a bit clearer.

Thanks again.

"John Spencer" wrote:

It sounds as if you need to use either DCount or a subquery.

SELECT ...
, DCount("*","tbMRAInput","Industry=""" & [Industry] & """) as
CompanyCount
FROM tbMRAInput INNER JOIN ...

You can use the DCount expression above as a calculated field in your
query
DCount("*","tbMRAInput","Industry=""" & [Industry] & """)

Or you can use a subquery as the calculated field
(SELECT Count(*) FROM tbMRAInput as Tmp WHERE Tmp.Industry =
tbMRAInput.Industry)

In query design view use either DCOUNT or the subquery as a field.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

TommoUK wrote:
Hi all.

I have an issue with a SELECT query I have built. The query generates
25
fields from 3 of different tables/queries. The problem I have is with
the 1
extra field I want to add to the end!

The majority of the query is sourced from 1 table (tbMRAInput). This
table
has a number of fields in it, one of them [Industry] is the main
"driver" for
my query i.e. the query picks out the top 20 industries. The field I
want to
add to the query would tell me how many customers each of the top 20
industries have ( [Customer Name] is another field in tbMRAInput).

Does anybody know of a way to do this within the SELECT query?

Many thanks in advance.





.

Relevant Pages

  • RE: Query returning many duplicate records
    ... If you wanted to calculate the number of establishments in a particular ... Then, in the query you would add the Industry column, and the ... "Roberto" wrote: ...
    (microsoft.public.access.queries)
  • Re: Query Improvement
    ... INDUSTRY: Index on IND_ID column. ... query you do not use a NVL function for the join. ... Even in older versions it should help to write it as an equijoin without IN or EXISTS and let the CBO decide about the driving table. ...
    (comp.databases.oracle.server)
  • Re: How not to display similar records in a subform?
    ... of Position Types or any combination of either, so are a necessary criteria. ... > If Industry and Position Type are not germane, ... > include them in the query? ...
    (microsoft.public.access.forms)
  • Re: How to remove duplicates from linked table query?
    ... Position as parameters into the query and return all Candidates who have at ... I would need to enter the industry as a parameter into the query. ... >> 'tblCandidateIndustries' and 'tblCandidatePositions' each of which may ... >> Penny. ...
    (microsoft.public.access.queries)
  • Re: Anime Rants
    ... What is happening now is, in NO WAY, customer loyalty. ... You're asserting that there ought to be a demand for these products and that simply because they bought the license, everyone ought to throw their money at them for products they don't want in the first place and that's about as idiotic as you can get. ... The entire computer-software industry depends on that fact ... they have no right to sell it. ...
    (rec.arts.anime.misc)
Quantcast