[AccessD] A2003: Distinct or Group by in a DLookup

Max Wanadoo max.wanadoo at gmail.com
Wed Apr 30 01:45:54 CDT 2008


You could try something like this:

intRecordCount = DCount("[ClientID]", "tblPendingActions","ClientID in
(select ClientID from tblPendingActions group by ClientID)") 

This is Air Code and needs testing, but should work.  It will be slow on
large datasets.

Max

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Wednesday, April 30, 2008 7:02 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2003: Distinct or Group by in a DLookup

Not AFAIK.  DCount() only operates on a Table or Query.  
One way would be to create and save the  query "Select Distinct ClientID
from 
tblPendingActions".   Then use DCount on that saved query.



On 30 Apr 2008 at 15:30, Darren D wrote:

> Hi Team
> 
> I have a simple DLOOKUP working well
> 
> intRecordCount = DCount("[ClientID]", "tblPendingActions")
> 
> But I want to know - Is there a "Distinct" version of the same?
> 
>  
> 
> EG
> 
> intRecordCount May return 10 records (but this may only be across say 
> 2
> accounts)
> 
> So I want to know if there is something where I can group on the 
> ClientID - like
> 
> intRecordCount = DCount(Distinct("[ClientID]", "tblPendingActions"))
> 
>  
> 
> Many thanks in advance
> 
>  
> 
> Darren
> 
>  
> 
>  
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list