Calculed Fields by Group

[Bonnie]

I have a database to keep survey responses. I'm trying
to get a report that gives me the average response per
question and the distribution of responses for each
question, all by group (a field name).

Getting the average is fine -- a calculated field at the
report group level works. When I put a calcuated field
in at the report group level to count the number of "1"
responses, "2" responses, etc. I get the count for all
groups not for each group. I used a Dcount function. My
report properties do call for grouping at the group
(fieldname) level.

Please help.

 

[Dennis Schmidt]

Hi Bonnie,

My name is Dennis Schmidt. Thank you for using the Microsoft Newsgroups.

There are lots of different ways to do this. Given that you seem to be
trying the DSUM() approach, you can condition the DSum to sum only the
records for the current group in the report. The following illustration is
using the Order Details table in the Northwind sample database:

=DSum("[Quantity]","[Order Details]","[Quantity] > 1 and [OrderID] = " &
[Reports]![Report1]![OrderId])

Also check out this Knowledge Base article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;208786

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Need quick answers to questions like these? The Microsoft Knowledge Base
provides a wealth of information that you can use to troubleshoot a problem
or answer a question! It's located at
http://support.microsoft.com/support/c.asp?M=F>.

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

Regards,
Dennis Schmidt
Microsoft Support

 

[Duane Hookom]

I wouldn't use DSum() in a report since it is re-opening a recordset that is
already available in the report. It's kinda like counting the white marbles
in a bag. Using DSum() makes count through the bag once for each DSum() in
addition to once for supplying results for the remainder of the report.

If you have a field like [Question] and want to count the number of
responses in a group footer where the value is 1 then use:
=Abs(Sum([Question]=1))
If you want to count the number where [Gender]="F" and [Question]=1 then
use:
=Abs(Sum([Question]=1 And [Gender] = "F"))
These expressions take advantage of the recordset created by the report. In
addition, if you limited your report to responses from females then the
DSum() would incorrectly reference both Genders rather than just the
females.

To view a fairly normalized sample survey application, download "At Your
Survey" from
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'