Count funtion in report-I'm too rusty!

[KMM]

I know this is simple, but I'm rusty and haven't worked with 2007 yet, so
it's looking a bit foreign, too.

I am trying to do a VERY simple report that shows by state how many people
reponded to what questions. The data I have comes from a survey where people
had to select one of four answers to a question (let's say "yes", "maybe",
"no", or "N/a"). So I have those four fields in each record, but only one of
them is "checked". I did a query to pull only those fields I need for the
report: State, ID#(which is my problem somehow..) and the four fields above.
When I build the report and group by State and then Count for each of the
four above fields (basically building a crosstab), I get an error because ID#
is part of the aggregate function, but isn't in the report. But when I pull
the ID# out of the query (because I really don't need it in the report), it
only returns 1/3 of the records. I'm obviously missing something simple, but,
I can't figure it out. Can you help? By the way, my query only has one table,
there are no joins.

Thanks.

 

[KARL DEWEY]

I have those four fields in each record,
Wrong structure. You are trying to use a spreadsheet. You need this --
State, ID#, Response

An Options Group would work for your data input for Response.

You can use a union query to reformat the data correctly.
SELECT State, ID#, "Yes" AS Response
FROM YourTable
WHERE [Yes] = -1
UNION ALL SELECT State, ID#, "No" AS Response
FROM YourTable
WHERE [No] = -1
UNION ALL SELECT State, ID#, "Maybe" AS Response
FROM YourTable
WHERE [Maybe] = -1
UNION ALL SELECT State, ID#, "N/A" AS Response
FROM YourTable
WHERE [N/A] = -1;

Then you can run a crosstab.

BTW what is your ID# - what purpose?