If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count funtion in report-I'm too rusty!
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. |
#2
|
|||
|
|||
Count funtion in report-I'm too rusty!
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. |
Thread Tools | |
Display Modes | |
|
|