i am trying to capture the count of

[dulla]

i am trying to capture the count of client ids that have provided quarterly data. client table is related to a year table (1 to many). year table is related to quarter table (1 to many). in my query i have summed up all of the quarterly data (per quarter) and want to know the total number of clients that report per quarter. when i use the count expression in the query it works but does not distinguish between duplicate client ids because each client can have many years and many quarters. is there a way to tell the number of clients that report for each quarter for a particular year with no duplicate ids?

ameen

 

[fredk]

I hope I follow you here.... I am not sure if you want the query to show all years but I will assume you do... There may be a better way but this works:

I would create one query that has the following fields
"year" "Quarter" "clientid" (I would add another field that is contained in each record - as an example I would use "city&quot

Then I would create a second query and use the following fields:
"year" "quarter" "clientid" and the count the field that you added - in my example, it would be city.

Fred

In the second query, you would eliminate duplicate idnumbers because you have a count of city for each specific idnumber

You can then use this query for your counts.

Hope that makes sense... that is what I do.

 

[Celeste025]

If that didnt work, you might try adding the word "DISTINCT" to your query sql statement. That will only bring each client ID one time. Like this:

SELECT DISTINCT ....

 

[fredk]

Thank you Celeste025! That helps me out!

Fred