PC Review


Reply
Thread Tools Rate Thread

counting distinct from a query

 
 
campbell_canuck
Guest
Posts: n/a
 
      13th Oct 2006
Hi everyone,

I understand how to get a count(distinct *) using multiple queries.
The problem I have is that I have been given a rather complicated query
to use as the record source of a form. I also need to display the
distinct values from one of the fields in the query.

I have Original_Query as the recordsource of Form1

I also have Distinct_Query which is:

select distinct (NumberField) number_count
from Orig_Query

When I try to add a text field with a Control Source set to be
DCOUNT(number_count, [Distinct_Query]), the value displayed is #Name?

When I set the Control Source to be
Count([Distinct_Query]![number_count]) I get Error! as the value.

When I add the sub-query "(select count(number_count) from
Distinct_Query) as distinct_count" to Form1.RecordSource I get a
circular reference error.

Without re-writing the original query or going back to the original
tables and recreating the FROM and WHERE clauses in the original query,
is there any way of displaying the distinct number of values for a
field from that query on the same form. Writing code behind the scenes
is not a preferred option either.

TIA

 
Reply With Quote
 
 
 
 
John Vinson
Guest
Posts: n/a
 
      13th Oct 2006
On 13 Oct 2006 11:57:49 -0700, "campbell_canuck"
<(E-Mail Removed)> wrote:

>When I try to add a text field with a Control Source set to be
>DCOUNT(number_count, [Distinct_Query]), the value displayed is #Name?


The syntax is wrong: the arguments to DCount must be text strings. Try

=DCount("*", "[Distinct_Query]")

John W. Vinson[MVP]
 
Reply With Quote
 
campbell_canuck
Guest
Posts: n/a
 
      13th Oct 2006

John Vinson wrote:
> On 13 Oct 2006 11:57:49 -0700, "campbell_canuck"
> <(E-Mail Removed)> wrote:
>
> >When I try to add a text field with a Control Source set to be
> >DCOUNT(number_count, [Distinct_Query]), the value displayed is #Name?

>
> The syntax is wrong: the arguments to DCount must be text strings. Try
>
> =DCount("*", "[Distinct_Query]")
>
> John W. Vinson[MVP


That always catches me... TYVM

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting distinct? Mark Microsoft Access Queries 2 29th Jan 2009 07:05 PM
Counting Distinct Values giantwolf Microsoft Excel Misc 4 29th Dec 2005 03:03 PM
Counting Distinct Records =?Utf-8?B?U3VzYW4=?= Microsoft Access 2 1st Dec 2005 01:38 AM
Counting distinct Rita Palazzi Microsoft Excel Misc 2 1st Nov 2004 04:00 PM
Distinct query not producing distinct results KarenM Microsoft Access 1 26th May 2004 07:41 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:09 PM.