Count Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TommoUK - 27 May 2008 04:37 GMT

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.

Reply to this Message

John Spencer - 27 May 2008 12:44 GMT

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

> Hi all.
>
[quoted text clipped - 11 lines]
>
> Many thanks in advance.

Reply to this Message

TommoUK - 28 May 2008 00:41 GMT

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.

> It sounds as if you need to use either DCount or a subquery.
>
[quoted text clipped - 32 lines]
> >
> >

Reply to this Message

TommoUK - 29 May 2008 00:06 GMT

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

> Thanks for the reply John.
>
[quoted text clipped - 64 lines]
> > >
> > >

Reply to this Message

Douglas J. Steele - 29 May 2008 02:17 GMT

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

Signature

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

> Probably a better example!
>
[quoted text clipped - 96 lines]
>> > >
>> > > Many thanks in advance.

Reply to this Message