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
|
|||
|
|||
Calculation based on results of 2 expressions
I have a field called Reoffense Counter and a field called Counter, where
Counter is the total count of all the records, and Reoffense Counter is the total of only those records where a new offense has taken place. Each of these fields contains a "1" where appropriate, so they can be counted. I want to find out what percentage of the records contain a Reoffense, so I think I would want to divide Reoffense Counter by Counter. I haven't been able to come up with the correct expression to use in my report. Can you help? |
#2
|
|||
|
|||
Calculation based on results of 2 expressions
In this query use your table and field names --
SELECT Sum([Reoffense Counter]) / Sum([Counter]) * 100 AS [Re-offend Percent] FROM YourTable; -- Build a little, test a little. "MarthaNH" wrote: I have a field called Reoffense Counter and a field called Counter, where Counter is the total count of all the records, and Reoffense Counter is the total of only those records where a new offense has taken place. Each of these fields contains a "1" where appropriate, so they can be counted. I want to find out what percentage of the records contain a Reoffense, so I think I would want to divide Reoffense Counter by Counter. I haven't been able to come up with the correct expression to use in my report. Can you help? |
#3
|
|||
|
|||
Calculation based on results of 2 expressions
Thank you, Karl. I tried entering this as a query and I also tried inserting
it into a text box as an expression in the report, but I keep getting a Syntax Error. Perhaps there is something missing that my inexperience doesn't make clear to me? "KARL DEWEY" wrote: In this query use your table and field names -- SELECT Sum([Reoffense Counter]) / Sum([Counter]) * 100 AS [Re-offend Percent] FROM YourTable; -- Build a little, test a little. "MarthaNH" wrote: I have a field called Reoffense Counter and a field called Counter, where Counter is the total count of all the records, and Reoffense Counter is the total of only those records where a new offense has taken place. Each of these fields contains a "1" where appropriate, so they can be counted. I want to find out what percentage of the records contain a Reoffense, so I think I would want to divide Reoffense Counter by Counter. I haven't been able to come up with the correct expression to use in my report. Can you help? |
#4
|
|||
|
|||
Calculation based on results of 2 expressions
In query design view use this as a calculated field --
Re-offend Percent: Sum([Reoffense Counter]) / Sum([Counter]) * 100 -- Build a little, test a little. "MarthaNH" wrote: Thank you, Karl. I tried entering this as a query and I also tried inserting it into a text box as an expression in the report, but I keep getting a Syntax Error. Perhaps there is something missing that my inexperience doesn't make clear to me? "KARL DEWEY" wrote: In this query use your table and field names -- SELECT Sum([Reoffense Counter]) / Sum([Counter]) * 100 AS [Re-offend Percent] FROM YourTable; -- Build a little, test a little. "MarthaNH" wrote: I have a field called Reoffense Counter and a field called Counter, where Counter is the total count of all the records, and Reoffense Counter is the total of only those records where a new offense has taken place. Each of these fields contains a "1" where appropriate, so they can be counted. I want to find out what percentage of the records contain a Reoffense, so I think I would want to divide Reoffense Counter by Counter. I haven't been able to come up with the correct expression to use in my report. Can you help? |
#5
|
|||
|
|||
Calculation based on results of 2 expressions
For some reason, I keep getting error messages. I'm probably trying to enter
the expression in the wrong place. In query design view, is this expression placed in the "Field" row or in the "Criteria" row? Thank you for your patience. "KARL DEWEY" wrote: In query design view use this as a calculated field -- Re-offend Percent: Sum([Reoffense Counter]) / Sum([Counter]) * 100 -- Build a little, test a little. "MarthaNH" wrote: Thank you, Karl. I tried entering this as a query and I also tried inserting it into a text box as an expression in the report, but I keep getting a Syntax Error. Perhaps there is something missing that my inexperience doesn't make clear to me? "KARL DEWEY" wrote: In this query use your table and field names -- SELECT Sum([Reoffense Counter]) / Sum([Counter]) * 100 AS [Re-offend Percent] FROM YourTable; -- Build a little, test a little. "MarthaNH" wrote: I have a field called Reoffense Counter and a field called Counter, where Counter is the total count of all the records, and Reoffense Counter is the total of only those records where a new offense has taken place. Each of these fields contains a "1" where appropriate, so they can be counted. I want to find out what percentage of the records contain a Reoffense, so I think I would want to divide Reoffense Counter by Counter. I haven't been able to come up with the correct expression to use in my report. Can you help? |
#6
|
|||
|
|||
Calculation based on results of 2 expressions
Karl - Please disregard my previous message. I'm not sure what I was doing
wrong yesterday, but I went back and created the calculated field in the report design view, and it works now. Thanks so much for all your help! "MarthaNH" wrote: For some reason, I keep getting error messages. I'm probably trying to enter the expression in the wrong place. In query design view, is this expression placed in the "Field" row or in the "Criteria" row? Thank you for your patience. "KARL DEWEY" wrote: In query design view use this as a calculated field -- Re-offend Percent: Sum([Reoffense Counter]) / Sum([Counter]) * 100 -- Build a little, test a little. "MarthaNH" wrote: Thank you, Karl. I tried entering this as a query and I also tried inserting it into a text box as an expression in the report, but I keep getting a Syntax Error. Perhaps there is something missing that my inexperience doesn't make clear to me? "KARL DEWEY" wrote: In this query use your table and field names -- SELECT Sum([Reoffense Counter]) / Sum([Counter]) * 100 AS [Re-offend Percent] FROM YourTable; -- Build a little, test a little. "MarthaNH" wrote: I have a field called Reoffense Counter and a field called Counter, where Counter is the total count of all the records, and Reoffense Counter is the total of only those records where a new offense has taken place. Each of these fields contains a "1" where appropriate, so they can be counted. I want to find out what percentage of the records contain a Reoffense, so I think I would want to divide Reoffense Counter by Counter. I haven't been able to come up with the correct expression to use in my report. Can you help? |
Thread Tools | |
Display Modes | |
|
|