DCOUNT ( DISTINCT

[Ivan Ferrer Villa]

We all know that Dcount doesn't support DISTINCT in VBA and there are
lots of threads talking about it everywhere.

But now I've found the way to do it.
We can use the criteria string to compare results.
iCustomers = Dcount("IdCustomer","Customers","IDCustomer IN (SELECT
IdCustomer FROM Customers WHERE " & stCriteria & ")")
the key is the word IN.


You're welcome.



Ivan Ferrer Villa - Granada - Spain.

 

[Allen Browne]

Hi Ivan

Thanks for posting this suggestion. You are suggesting to use a subquery in
the Criteria of the domain aggregate functions. Good idea!

Using Northwind, you could therefore answer the question, "How many
different customers placed orders during 1997?" like this:
DCount("*", "Customers", "CustomerID IN
(SELECT CustomerID
FROM Orders
WHERE OrderDate Between #1/1/1997# And #12/31/1997#)")

While it may not be the most efficient approach, it would be possible to
extend the idea to handle much more involved queries as well. It could help
circumvent the fact that the domain aggregate functions can't take a SQL
statement for their Domain argument.

Good suggestion.

 

[Ivan Ferrer Villa]

DAMN. Now I see that my system is not working well.
Im sorry.
It still doesnt count only DISTINCT values.

Thanks anyway.

 

[Ivan Ferrer Villa]

Sh*t. Now I see that my system is not working well.
Im sorry.
It still doesnt count only DISTINCT values.

Thanks anyway.

 

[Wolfgang Kais]

Hello Ivan.

Sh*t. Now I see that my system is not working well.
Im sorry.
It still doesnt count only DISTINCT values.

Thanks anyway.

Don't give up. Look at Allen's suggestion about the distinct customers:
The trick is using a table for the domain argument where the things to
count are unique. He did not count the customers in the orders table
but the customers from the customers table that have a matching ID
in the subquery.
But as you might have noticed studying the lots of threads: dcount is
one of the slowest funtions ever known. Write you own function, and
your code will be much faster, even if a recordset has to be opened
and closed every time the function is called.

 

[Ivan Ferrer Villa]

Yes, it seems better to open a Recordset only with Distinct values and
count them there.
But... let me say... This is not full SQL compatibility, I think.
Well, anyway Access is still really amazing for me.
And Im just learning it step by step. Big steps...
Thanks.

 

[Allen Browne]

Ivan, from your post, I take it you are already familiar with the ECount() -
i.e. extended DCount() - here:
http://allenbrowne.com/ser-66.html
I don't think there's anything non-standard in the SQL used there.

But, as Wolfgang said, don't give up on the idea of using a subquery in the
Criteria of the domain aggregate functions. That's a useful suggestion, so
thanks for posting it.

 

[Ivan Ferrer Villa]

Yes, that's full SQL .
Simply, it looks strange that we have to use a own function to count
distinct values.
Dcount could have a boolean argument to indicate that.
But it's ok... May be it would be slower than the function you
exposed.
May be in future...

Thanks again.

 

[Ivan Ferrer Villa]

Hi all.
I think i've got it ( to Dcount Distinct values)
For example, to count how many distinct Customers exist in table
Orders try the following:

=DCount("idCustomer";"Orders";"idOrder IN ( SELECT FIRST(idOrder) FROM
Orders GROUP BY idCustomer)")

The subQuery gives only the first IdOrder for each idCustomer, and
then, Dcount has only to count the idCustomer's related to that unique
idOrder and give the result.
It works!
Excuse my enthusiasm, but I knew it was possible to achieve.
Of course, idOrder could be any non-duplicated field in Orders wich
makes idCustomer to get repeated.
We won't never have an idOrder without an idCustomer nor an idCustomer
without an idOrder, then the count is OK.
Any comments?

 

[Ivan Ferrer Villa]

Excuse me again, Allen.
Now I see that what I propose is almost exactly what you said in your
first reply about Northwind.
The only difference is that I'm using only one table and they two.

Greetings.

 

[Allen Browne]

Great

 

[Ivan Ferrer Villa]

Is a good idea to do it with just one table because we wont have
always a table for unique values for IdCustomer.
Well, if that is an Id, yes. But we could be counting distinct values
in another field.
Northwind is pre-supposing that we have a table with unique Ids for
what we are trying to count.
Thanks again for your attention.

 

[Allen Browne]

It seems most suited to counting something that has a distinct list.

Guess you could create a query for that purpose if no such table exists.

 

[Wolfgang Kais]

Hello Ivan.

Is a good idea to do it with just one table because we wont have
always a table for unique values for IdCustomer.
Well, if that is an Id, yes. But we could be counting distinct values
in another field.
Northwind is pre-supposing that we have a table with unique Ids for
what we are trying to count.
Thanks again for your attention.

If your one orders table has an index on the idCustomer column, say
ixCustomer, you could also use a VBA expression loke this:
CurrentDb!Orders.Indexes!ixCustomer.DistinctCount

 

[Ivan Ferrer Villa]

Good idea.
But i tried it and i see that DistinctCount counts the deleted records
of an autonumeric field too.
If i delete some records in between, it counts them too (I can't
understand why).
And we can't use a WHERE criteria.

But thanks anyway. Is good to know it exists.


To Allen:
Yes, i tried to create the query, use it, and delete it when closing
the report. Works good too.

Regards.