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
|
|||
|
|||
Summarizing yes and no
TIA:
I have a table with yes/no fld. I'm trying to generate a report to simply show "#_of_yes" OF "#_yes_and_no") and calculate the percentage eg (7 of 10 70%). I don't want to see the "no" data in the report. Any ideas on approach? I have tried report based on summay query but the "no" data is there. Thanks, Joel |
#2
|
|||
|
|||
Summarizing yes and no
Joel
Yes & No is stored as -1 (or 1) and 0. You could "add" the responses and use the absolute value function to make sure it is a positive number -- that gives you the number of yes'es. Or you could create a totals query and count WHERE YourField = "True" (and once more, with "False"). Regards Jeff Boyce Microsoft Office/Access MVP "Joel" wrote in message ... TIA: I have a table with yes/no fld. I'm trying to generate a report to simply show "#_of_yes" OF "#_yes_and_no") and calculate the percentage eg (7 of 10 70%). I don't want to see the "no" data in the report. Any ideas on approach? I have tried report based on summay query but the "no" data is there. Thanks, Joel |
#3
|
|||
|
|||
Summarizing yes and no
Thanks Jeff:
I misspoke..it is a text field with yes, no, and other, so the math approach is moot. I have tried a summary query with criteria for "yes or no" and counts of each but the "no" data is now part of the report. I'm tring to only show the yes and total data. How would i use a summary query in a report and not show one of the groups? Or do I need two different queries and then how to make one report with 2 queries (one for yes and one for total yes and no)? Joel "Jeff Boyce" wrote: Joel Yes & No is stored as -1 (or 1) and 0. You could "add" the responses and use the absolute value function to make sure it is a positive number -- that gives you the number of yes'es. Or you could create a totals query and count WHERE YourField = "True" (and once more, with "False"). Regards Jeff Boyce Microsoft Office/Access MVP "Joel" wrote in message ... TIA: I have a table with yes/no fld. I'm trying to generate a report to simply show "#_of_yes" OF "#_yes_and_no") and calculate the percentage eg (7 of 10 70%). I don't want to see the "no" data in the report. Any ideas on approach? I have tried report based on summay query but the "no" data is there. Thanks, Joel |
#4
|
|||
|
|||
Summarizing yes and no
Long way around but it works.
Summary queries for yes and no to get counts. Then create report for each. On main report, add subreports and create text box to add 2 together and textbox to calculate % and hide subreport for "no" data. Any eaiser way? TIA, Joel "Jeff Boyce" wrote: Joel Yes & No is stored as -1 (or 1) and 0. You could "add" the responses and use the absolute value function to make sure it is a positive number -- that gives you the number of yes'es. Or you could create a totals query and count WHERE YourField = "True" (and once more, with "False"). Regards Jeff Boyce Microsoft Office/Access MVP "Joel" wrote in message ... TIA: I have a table with yes/no fld. I'm trying to generate a report to simply show "#_of_yes" OF "#_yes_and_no") and calculate the percentage eg (7 of 10 70%). I don't want to see the "no" data in the report. Any ideas on approach? I have tried report based on summay query but the "no" data is there. Thanks, Joel |
#5
|
|||
|
|||
Summarizing yes and no
To count the number of records where [UnknownField] contains "Yes" use a
control source of: =Sum( Abs([UnknownField]="Yes")) If you don't want to display records in your report with [UnknownField] = "No" but you need them available for counting, you can use code to cancel the printing of a record where the [UnknownField] ="No" In the On Format event of the report section: Cancel = ([UnknownField] = "no") -- Duane Hookom Microsoft Access MVP "Joel" wrote: Thanks Jeff: I misspoke..it is a text field with yes, no, and other, so the math approach is moot. I have tried a summary query with criteria for "yes or no" and counts of each but the "no" data is now part of the report. I'm tring to only show the yes and total data. How would i use a summary query in a report and not show one of the groups? Or do I need two different queries and then how to make one report with 2 queries (one for yes and one for total yes and no)? Joel "Jeff Boyce" wrote: Joel Yes & No is stored as -1 (or 1) and 0. You could "add" the responses and use the absolute value function to make sure it is a positive number -- that gives you the number of yes'es. Or you could create a totals query and count WHERE YourField = "True" (and once more, with "False"). Regards Jeff Boyce Microsoft Office/Access MVP "Joel" wrote in message ... TIA: I have a table with yes/no fld. I'm trying to generate a report to simply show "#_of_yes" OF "#_yes_and_no") and calculate the percentage eg (7 of 10 70%). I don't want to see the "no" data in the report. Any ideas on approach? I have tried report based on summay query but the "no" data is there. Thanks, Joel |
#6
|
|||
|
|||
Summarizing yes and no
Simple query
SELECT Abs(Sum([SomeField] = "yes")) CountYes , Count([SomeField]) as CountResponses , Abs(Sum([SomeField] = "yes")) / Count([SomeField]) as ThePercent FROM [Your Table] In the query grid -- Add Somefield to the query twice -- Change first field to Abs([SomeField= "yes") -- Select View: Totals from the menu -- Change first group by to Sum -- Change second group by to Count '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Joel wrote: Long way around but it works. Summary queries for yes and no to get counts. Then create report for each. On main report, add subreports and create text box to add 2 together and textbox to calculate % and hide subreport for "no" data. Any eaiser way? TIA, Joel "Jeff Boyce" wrote: Joel Yes & No is stored as -1 (or 1) and 0. You could "add" the responses and use the absolute value function to make sure it is a positive number -- that gives you the number of yes'es. Or you could create a totals query and count WHERE YourField = "True" (and once more, with "False"). Regards Jeff Boyce Microsoft Office/Access MVP "Joel" wrote in message ... TIA: I have a table with yes/no fld. I'm trying to generate a report to simply show "#_of_yes" OF "#_yes_and_no") and calculate the percentage eg (7 of 10 70%). I don't want to see the "no" data in the report. Any ideas on approach? I have tried report based on summay query but the "no" data is there. Thanks, Joel |
Thread Tools | |
Display Modes | |
|
|