How can I count unique values in a query in the report footer

[Lindy]

I have a report that gives me the count of the status of individuals . This
works fine as long is there is only one record in the query (in my query
there is one record per month). When I query 12 months (individuals may
appear in various months) it counts each record of an individual. For
Example in a query considering 12 months for a widow Jones it may count her
12 times and for a survivor named Smith may count her 8 times:

Widows 12
Survivors 8

I would like to add a count in the report footer that will tell me how many
unique individuals I have in the report (Example widows: Total 3). No
mattet what I try I end up counting each indvidual the number of times they
appear in the query over a 12 month period; rather than just once.

Any suggestions?

Thanks!!!

 

[Marshall Barton]

I have a report that gives me the count of the status of individuals . This
works fine as long is there is only one record in the query (in my query
there is one record per month). When I query 12 months (individuals may
appear in various months) it counts each record of an individual. For
Example in a query considering 12 months for a widow Jones it may count her
12 times and for a survivor named Smith may count her 8 times:

Widows 12
Survivors 8

I would like to add a count in the report footer that will tell me how many
unique individuals I have in the report (Example widows: Total 3). No
mattet what I try I end up counting each indvidual the number of times they
appear in the query over a 12 month period; rather than just once.

That kind of summary should be done using a subreport.

First create a Totals type query that calculates the count.
It might be somethig=ng like:

SELECT status, Count(*) As CountOfStatus
FROM people
GROUP BY status

But, if your tables are not properly normalized with a
people table containing a status field, you may need to
first create a people query that can be used as the basis
for the above query.

Once you get the query working correctly, the subreport
should be trivial.