I've written a report  that aggregates (avg, sum, count) a 10 or 15 measures 
($, Days to Book, # rejects, %within SLA, etc.)  I need to do a COUNT 
DISTINCT on one control (D&B number) to determine how many distinct companies 
I am dealing with in a particular country or region.

I've written a SQL subquery that does what I want it to do, but I can't 
figure out how to imbed that statement into a control expression in a Report. 
 I prefer to work with the report engine because I need to take a number of 
cuts on different groups and levels of detail and just find it easier to do 
using Reports.  It's working great except for this one control.  Any thoughts?

Thanks, Jim

You can't use a SQL statement or query name in the control source of a text 
box. You might be able to use a domain aggregate function such as DLookup() 
or DMax() or DSum().

It would help if we knew more about your tables and/or query.

-- 
Duane Hookom
Microsoft Access MVP


"Jim K" wrote:

> I've written a report  that aggregates (avg, sum, count) a 10 or 15 measures 
> ($, Days to Book, # rejects, %within SLA, etc.)  I need to do a COUNT 
> DISTINCT on one control (D&B number) to determine how many distinct companies 
> I am dealing with in a particular country or region.
> 
> I've written a SQL subquery that does what I want it to do, but I can't 
> figure out how to imbed that statement into a control expression in a Report. 
>  I prefer to work with the report engine because I need to take a number of 
> cuts on different groups and levels of detail and just find it easier to do 
> using Reports.  It's working great except for this one control.  Any thoughts?
> 
> Thanks, Jim