complex report with distinct

[Onlyeyes]

I am working on a report in access 2002 that needs to look 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 am 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?

 

[Rich]

Just Sum the fields on the Report

 

[Onlyeyes]

I am summing the fields in the report to get totals on the bottom, but I need the columns to display the counts of people grouped by precinct, which is different than total.
The problem is that I only need the last two columns to come from distinct households....so how do I make only those columns distinct and not the rest of the report?

 

[Rich]

Not sure I'm reading this correctly but why not just add a grouping level?

 

[Onlyeyes]

That is why this report is so complicated. I already have grouping levels:

Region County Precinct #ofactivists #ofhouseholds #w/phone
1
Suffolk
Suffolk1 37 32 30
Suffolk 2 40 39 36


The problem is that only the last two columns need to come from distinct addresses.

 

[Rich]

Would a subreport do it?

 

[Onlyeyes]

No, I think the only way to get it done is to make three Totals queries to get the Sums for different fields, using different
grouping --one for activists per precinct and one for households per precinct -- and then link the totals queries to the main
table or query, by ID. Then the resulting query can be used as the report record source.

i'm not sure how to do this though.

 

[jon98548]

I tried doing something like that with totals, but each time you have different criteria, you need a different query. I also tried combining queries but had tons of trouble with it. I wound up doing what Rich suggested and made subreports for each query and put them in the main. Played around with the sizing and formatting to make it all blend together and look pretty. That's the best I could do.

 

[Onlyeyes]

Yeah I can try to do it with subreports, but the formatting problems are just crazy. I figured it would be easier to do it through queries, but I guess not.

 

[Rich]

Have you tried a Union Query?

 

[Onlyeyes]

I will try a union query as soon as I get my underlying queries straightened out.

So far I have:

Query1:

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]
FROM [Key Activists Statewide Again]
GROUP BY [Key Activists Statewide Again].Region, [Key Activists
Statewide Again].County, [Key Activists Statewide Again].Precinct;

Query2:

SELECT [Key Activists Statewide Again].Address, [Key Activists Statewide
Again].Precinct, iif(nz([phone],"0")<>"0",1,0) AS [Has Phone]
FROM [Key Activists Statewide Again]
GROUP BY [Key Activists Statewide Again].Address, [Key Activists
Statewide Again].Precinct, iif(nz([phone],"0")<>"0",1,0);

-the second one returns the number of unique addresses….I would have to
query on this to get the number of households and the number of
households with phones, right?