query for complex report

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

 

[[MVP] S. Clark]

Often times the goal to be achieved is not done so with just one query. Not
to sound like Confucious or anything, but sometimes you may need to run
several queries to get there. This includes action queries. Many of my
reports are based on tables that only exist for the use of printing the
report. When the report is executed, I populate the table with the desired
data. The code may consist of a delete query, an append query, an update
query, then the opening of the report.

Another thing to consider is that groups can be added to the report. In the
Group Footer bands, totals can be added for data that was/can't be generated
in the query.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-