count function

[Guest]

Hello,
I have a table with the following columns: country, topic, company and name
(of people). I need to count the number of topics per company. For example,
if there are 4 people who worked on one topic from company X, then this
should count as 1 (and not four and the fucntion Count does).
Am i making sense? can anyone help?
Thanks
M

 

[Guest]

You need a Totals Query. To get it to count by company, include only the
company and topic fields. Group by company and Count topic.

 

[Guest]

Thanks Klatuu,

Actually my life is a bit more complicated unfortunately. I have tried what
you told me, but may be I did not clearly tell m y problem, so please bear
with me. I have 3 people fform the same company who worked on the same
topic. But in the TOpic column, it is repeated three times, since there are
3 people , but from the same company.
So how can a total query count these 3 people as only 1, since they all
worked on only one topic?
My goal is to be able to show to my boss, how many topics each company from
different countries answered, without the duplicates.
Complicated? Thanks for your hlep

M

 

[Guest]

In your query, try using either Unique records or Unique values in your query
properties to filter out duplicate topics. Not knowing the fields in your
table, I cant' give an exact answer on which would work or how to structure
it.

 

[John Spencer]

Try the following two step process
First query (saved as Q1)
SELECT DISTINCT Company, Topics
FROM Yourtable

Second query
SELECT Company, Count(Topics) as CountOfTopics
FROM Q1
GROUP BY Company

OR All in one query
SELECT Company, Count(Topics) as CountOfTopics
FROM (
SELECT DISTINCT Company, Topics
FROM Yourtable
) as UniqueTopics
GROUP BY Company

If you don't know how to construct a query in the SQL window, post back and
I'll try to document the steps to do the two-step query solution.

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

 

[Guest]

Thank you so much, the two-step queries worked like a charm. I tried the
one-in-all, but must have missed something, it does not matter. Thanks and
now I can keep working.
Marly