complex report with distinct

[Andy]

need a report that looks like this:

Region County Precinct #ofActivists #ofHouseholds
HouseholdswithPhones

This is tricky because the NumberofActivists column needs
to return the total number of activists grouped by
precinct while the last two columns need to return the
number of households (ie, where the address is the same)
also grouped by precinct.

I using the query below as the data source. The phone
column in this query gives me the toal number of people
with phones, not the number of households with phones.

SELECT [Key Activists Statewide Again].Region, [Key
Activists Statewide Again].County, [Key Activists
Statewide Again].Precinct, Count([Key Activists Statewide
Again].County) AS [Number of Key Activists], SUM(([Key
Activists Statewide Again].Phone Is Not Null)*-1) AS
HasPhone
FROM [Key Activists Statewide Again]
GROUP BY [Key Activists Statewide Again].Region, [Key
Activists Statewide Again].County, [Key Activists
Statewide Again].Precinct;

so basically, how do I add a DISTINCT statement for the
address field that works for only the last two columns of
the report?

 

[Jeff Boyce]

Take a look at Access HELP on the DCount() function. The gist is that you'd
create a textbox control on the report, and set its control source to
something like (actual syntax may vary):

= DCount(blah, blah, blah)

Good luck

Jeff Boyce
<Access MVP>