Prev: Create Categories
Next: Printing a report 2-up
From: KMM on 2 Jun 2010 16:03
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.
From: KARL DEWEY on 2 Jun 2010 16:27
>>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?

--
Build a little, test a little.


"KMM" wrote:

> 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.
 | 
Pages: 1
Prev: Create Categories
Next: Printing a report 2-up