Count distinct addresses

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

I need to make a query as the record source of a report
that wil count distinct housholds. A household is
everyone living at the same address. I then need to
include the number of the households that have a phone
number. Both counts have to be grouped by precinct.
So the column headings in the report or query should look
like:

Region County Precinct NumberofHouseholds
HouseholdswithPhone

I am using this SQL statement, but it is not returning
individual households:

SELECT DISTINCT [Key Activists Statewide Again].Region,
[Key Activists Statewide Again].County, [Key Activists
Statewide Again].Precinct, Count([Key Activists Statewide
Again].Address) AS CountOfAddress1, Count([Key Activists
Statewide Again].Phone) AS CountOfPhone
FROM [Key Activists Statewide Again]
GROUP BY [Key Activists Statewide Again].Region, [Key
Activists Statewide Again].County, [Key Activists
Statewide Again].Precinct;

Any suggetions on how to return unique addresses in this
way?
I will be forever grateful.
 
Does this work?
SELECT [Key Activists Statewide Again].Region, [Key Activists Statewide
Again].County, [Key Activists Statewide Again].Precinct, Count([Key
Activists Statewide Again].Address) AS CountOfAddress,
Sum(Abs(IsNull([Phone]))) AS NoneNullPhone
FROM [Key Activists Statewide Again]
GROUP BY [Key Activists Statewide Again].Region, [Key Activists Statewide
Again].County, [Key Activists Statewide Again].Precinct;
 
Back
Top