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

Hi Stuart and Max

Thanks to both of you for your efforts. 

Max, the stuff you suggested (below) is almost exactly what I ended up doing.
It's one if those 'quick and dirty' (Q&D) things that is only in use on a
personal dB that I use here in the office to manage and keep an eye on client
requests - hence the need to know pending item numbers - I just wanted to write
a one line DLOOKUP or DCOUNT - Too Lazy to set up a recordset object <sigh> - in
the end it was (as it always is with a Q&D) a false economy

Many many thanks for all the replies and suggestions - gotta love this list :-)

DD

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Thursday, 1 May 2008 10:00 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] A2003: Distinct or Group by in a DLookup

Darren.
The only way I could get this to work (without a query to work directly on)
is this:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT ClientID FROM tblPendingActions
GROUP BY tblPendingActions.ClientID;")
intRecordCount = rst.RecordCount

Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren D
Sent: Thursday, May 01, 2008 5:11 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] A2003: Distinct or Group by in a DLookup

Hi Max

No it didn't - it still returned the number of records in the table It
should work though - not sure why it doesn't Love the concept I ended up
setting up a DAO recordset object to do this I am going to play with your
suggestion a while more though Darren
-----------------
T: 1300 301 731

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Thursday, 1 May 2008 3:42 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] A2003: Distinct or Group by in a DLookup

Darren:
Did this work?
Max
 

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

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

--
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

--
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