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

  • Thread starter Thread starter Lindy
  • Start date Start date
L

Lindy

Am using Access 2003

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 2). No
matter 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.

Have tried suggestion submitted by Marshall Bartoon, which I appreciate, but
cannot get it to work.

Any suggestions?

Thanks!!!
 
Lindy said:
Am using Access 2003

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 2). No
matter 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.

Have tried suggestion submitted by Marshall Bartoon, which I appreciate, but
cannot get it to work.


I might be able to help get it working if I knew what your
tables looked like. Could you post the name and a
description of each significant field in the relevant
tables?
 
Marshall Barton said:
I might be able to help get it working if I knew what your
tables looked like. Could you post the name and a
description of each significant field in the relevant
tables?
My table is a query with ther fields - "name", office, date, hour1, hour2,
hour3, hours (a total of the first three hours) and "status".

The significant fields are name and status. I want the report to show the
count of the various status but only once per name.

Thanks again!!
 
Lindy said:
My table is a query with ther fields - "name", office, date, hour1, hour2,
hour3, hours (a total of the first three hours) and "status".

The significant fields are name and status. I want the report to show the
count of the various status but only once per name.


Does this query do what you want?

SELECT [name], status, Count(*) As CountOfStatus
FROM yourquery
GROUP BY [name], status
 
Marsh. I must be to much of a novice. Where do I put the Totals Type Query,
in the report, my query or a separate element. If in a separate element
where and how do I connect it to my report to give me a count of the number
of "widows", 'survivors", etc.; without counting duplicates.

I am confused!

Incidently mu query is named "Volunteer Tracking Spreadsheet"

Marshall Barton said:
Lindy said:
My table is a query with ther fields - "name", office, date, hour1, hour2,
hour3, hours (a total of the first three hours) and "status".

The significant fields are name and status. I want the report to show the
count of the various status but only once per name.


Does this query do what you want?

SELECT [name], status, Count(*) As CountOfStatus
FROM yourquery
GROUP BY [name], status
 
You should create a new query, add your base **table** that
has the name and status fields. You can not use the main
report's record source query because it has too much
information, which causes your original problem.

Click on the View - Totals menu item. Then drag the name
and status fields down to the field list (the Totals row in
the grid should have Group By for these fields). Drag the
status field down to the third column in the grid and set
the Totals row for this column to Count.

Switch the query to datasheet view to see if it's doing what
you want and report back if it is not.

If it does have the right values, then create a new report
based on the new query. Add text boxes for the query's
three fields. Run the new report to make sure it looks
right, When it does, close and save the new report, then
drag it from the database window and drop it in your main
report's report footer section.
--
Marsh
MVP [MS Access]

Marsh. I must be to much of a novice. Where do I put the Totals Type Query,
in the report, my query or a separate element. If in a separate element
where and how do I connect it to my report to give me a count of the number
of "widows", 'survivors", etc.; without counting duplicates.

I am confused!

Incidently mu query is named "Volunteer Tracking Spreadsheet"

Marshall Barton said:
Lindy said:
:
Lindy wrote:

Am using Access 2003

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 2). No
matter 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.

Have tried suggestion submitted by Marshall Bartoon, which I appreciate, but
cannot get it to work.


I might be able to help get it working if I knew what your
tables looked like. Could you post the name and a
description of each significant field in the relevant
tables?


My table is a query with ther fields - "name", office, date, hour1, hour2,
hour3, hours (a total of the first three hours) and "status".

The significant fields are name and status. I want the report to show the
count of the various status but only once per name.


Does this query do what you want?

SELECT [name], status, Count(*) As CountOfStatus
FROM yourquery
GROUP BY [name], status
 
Thanks Marsh!

Marshall Barton said:
You should create a new query, add your base **table** that
has the name and status fields. You can not use the main
report's record source query because it has too much
information, which causes your original problem.

Click on the View - Totals menu item. Then drag the name
and status fields down to the field list (the Totals row in
the grid should have Group By for these fields). Drag the
status field down to the third column in the grid and set
the Totals row for this column to Count.

Switch the query to datasheet view to see if it's doing what
you want and report back if it is not.

If it does have the right values, then create a new report
based on the new query. Add text boxes for the query's
three fields. Run the new report to make sure it looks
right, When it does, close and save the new report, then
drag it from the database window and drop it in your main
report's report footer section.
--
Marsh
MVP [MS Access]

Marsh. I must be to much of a novice. Where do I put the Totals Type Query,
in the report, my query or a separate element. If in a separate element
where and how do I connect it to my report to give me a count of the number
of "widows", 'survivors", etc.; without counting duplicates.

I am confused!

Incidently mu query is named "Volunteer Tracking Spreadsheet"

Marshall Barton said:
Lindy wrote:
:
Lindy wrote:

Am using Access 2003

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 2). No
matter 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.

Have tried suggestion submitted by Marshall Bartoon, which I appreciate, but
cannot get it to work.


I might be able to help get it working if I knew what your
tables looked like. Could you post the name and a
description of each significant field in the relevant
tables?


My table is a query with ther fields - "name", office, date, hour1, hour2,
hour3, hours (a total of the first three hours) and "status".

The significant fields are name and status. I want the report to show the
count of the various status but only once per name.


Does this query do what you want?

SELECT [name], status, Count(*) As CountOfStatus
FROM yourquery
GROUP BY [name], status
 
Back
Top