Count distinct in a linked subform (1 Viewer)

Hi,

I've got a subform I made for calculating KPIs. Essentially, the parent has the quarter and year input, and links to the child. The query is fairly straightforward:

H/T: http://www.techrepublic.com/article/obtain-quarterly-records-in-access-with-the-datepart-function/

Admittedly I let access build the query, so it has some excess brackets, etc. But nevertheless, it works very well.

The KPIs are various functions in the form footer, summing and dividing and such.

What I would like to do is simply count the number of clients (i.e. unique Client_ID) in this recordset.


This is just to go into a textbox, so if it's easier to use DCount, I can do that - I just haven't been able to figure out how to write the criteria. Considered VBA, but it seems that would just be forcing an inelegant solution by sticking to more familiar territory.

As always, your help will be greatly appreciated.

make a query, that uses YOUR query to get the unique values.
qsQUnique = select distinct [client] from qsMyQuery

then count that...
msgbox Dcount("*","qsQUnique")

What do tou need, count per project + qtr+ year+closes OR per proj +closes only?

Ranman256 said:

make a query, that uses YOUR query to get the unique values.
qsQUnique = select distinct [client] from qsMyQuery

then count that...
msgbox Dcount("*","qsQUnique")

Click to expand...

Hmmm... Apologies for not clarifying this earlier, but I use this subform multiple times on a single parent form (so you can see data over the last 8 quarters). Which means that each subform appearance has its own query. So I can't filter the DISTINCT query to be the correct quarter and year, as I need 8 of them, and they'll change depending on the current quarter. Your method will get me the total number of unique clients with closed projects, but not for the quarter.

BUT! If the Distinct query grabs one instance of each Client_ID from each quarter, then I should be able to use the DCount's Criteria. So now I need to figure out how to do that query...

Then I can use something like:
=DCount("Client_ID",[qryUniqueClients],"Qtr = " & [Qtr] & " AND Yr = " & [Yr])

EDIT: OK, so SELECT DISTINCT really is just that simple, huh? (I just added Qtr and Yr to the SELECT list)

Thanks, you have solved it!

arnelgp said:

What do tou need, count per project + qtr+ year+closes OR per proj +closes only?

Click to expand...

I need the total unique clients from the closed projects in the specified quarter & year, based on the date they were closed.

Correct me, you mean a client can appear more than once in a project?

This is solved now, thanks to Ranman256. I've edited my previous post to clarify this.

arnelgp: No, only one client per project. Its the other way round: I'm finding the number of clients we've done projects for (to see e.g. we're doing 10 projects, but only for 2 clients).

Thanks for the responses.

just make another Query (qryClientCountPerProjectPerQuarterPerYear),
and use DCount() against this query:

qryClientCountPerProjectPerQuarterPerYear:

SELECT PROJECTS.Client_ID, PROJECTS.Project_ID, DatePart("q",[Completed_Date]) AS Qtr, Year([Completed_Date]) AS Yr
FROM PROJECTS
GROUP BY PROJECTS.Client_ID, PROJECTS.Project_ID, DatePart("q",[Completed_Date]), Year([Completed_Date]), PROJECTS.Closed
HAVING (((PROJECTS.Closed)=True));



=DCount("*","qryClientCountPerProjectPerQuarterPerYear", "Qtr=" & [Qtr] & " And [Yr] = " & [Yr])

'****
note Replace Project_ID with correct fieldname in the Query