Count Problem

  • Thread starter Thread starter TommoUK
  • Start date Start date
T

TommoUK

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.
 
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
 
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 said:
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.

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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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.

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.
Click to expand...
Click to expand...
Click to expand...
 
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

(no private e-mails, please)


TommoUK said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top