Need to count the number of records in a field - PLEASE HELP

[MM Smith]

I am in the process of creating a survey database.
For each field (question asked on the survey) I need to return
1. the total number of responses for that field (the fields are text
fields)
2. the number of responses that are "Very Satisfied" (displayed as a
number),
3. the number of responses that are "Very Satisfied" (displayed as a
percentage.
I have tried using the count function in the total row. However, it not
returning the results that I expected. I think once I get that function to
work properly, I will not have any problem getting items 2 and 3 to display.
The basic structure of my database is as follows:
I have 3 tables:
1) a response table that I use to look up the values for the survey
responses. The chosen value is saved in my survey table.
2) The Survey table contains the responses to the questions as well as a
client ID and survey ID (both auto number fields).
3) Client table that contains info about the client (address, contact name,
etc)
The client ID is the primary key in the client table, and the survey ID is
the primary key in the survey table (This is how they are related, one
customer to many surveys).

Data entry is preformed using a form.
Thanks for any help you can provide

 

[John Vinson]

I am in the process of creating a survey database.
For each field (question asked on the survey) I need to return
1. the total number of responses for that field (the fields are text
fields)
2. the number of responses that are "Very Satisfied" (displayed as a
number),
3. the number of responses that are "Very Satisfied" (displayed as a
percentage.
I have tried using the count function in the total row. However, it not
returning the results that I expected.

A Count doesn't count *values* - it counts *records*. If you have
three counts in the totals row they'll all return the same value.

I think once I get that function to
work properly, I will not have any problem getting items 2 and 3 to display.
The basic structure of my database is as follows:
I have 3 tables:
1) a response table that I use to look up the values for the survey
responses. The chosen value is saved in my survey table.

Is this a Lookup Field in the survey table? If so the table does not
contain what you see: if you *see" "Very Satisfied" in the lookup
combo box, the table actually contains a number.

2) The Survey table contains the responses to the questions as well as a
client ID and survey ID (both auto number fields).

Ummm... no. These CANNOT be both autonumber fields in the Survey
table; aren't they long integer foreign key links to the autonumber ID
fields in the Client and Survey tables?

3) Client table that contains info about the client (address, contact name,
etc)
The client ID is the primary key in the client table, and the survey ID is
the primary key in the survey table (This is how they are related, one
customer to many surveys).

Data entry is preformed using a form.
Thanks for any help you can provide

Depending on the datatype of the Response field - I can't tell from
your description whether it's Text, "Very Satisfied", or a Long
Integer foreign key to a lookup field - you'll need to vary this a
bit. But assuming that it's text, put a calculated field in your
Totals query:

IsVerySat: IIF([Response] = "Very Satisfied", 1, 0)

and *sum* this value to get the number of very-satisfied respondants.
You can also put in a calculated field

Sum(IIf([Response] = "Very Satisfied", 1, 0)) / Count(*)

and format it as a percentage to show the percent very satisfied.

John W. Vinson[MVP]

 

[John Vinson]

Sorry for the delay in responding, I have been sick ... see below for responses
My main sticking point is getting the query to count the total number of
records per field

Eh? No responses.

Again: The concept "records per field" is simply incorrect.

If a query returns 238 records, it returns 238 records; if each record
has 18 fields, every single one of those fields will be returned 238
times, one for each record.

My suggestion was to use a calculated field which will return the
number 1 if the value in a given field is one of the desired values;
you can then add up all those 1's to count how many of the 238 records
match a particular desired value.

Hope you're feeling better and that this makes sense!

John W. Vinson[MVP]

 

[MM Smith]

Thank you - I think I understand; and as always it is logical. Now if only I
could make my brain be as logical as the machines; lol.

FYI: my original responses are listed but they kind of blend in with
everything else. Thank you very much for your help.

M Smith