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
|
|||
|
|||
Use result of expression in another expression??
Can I go to the first empty column in a design query grid and use an
expression to add a new field row and then use the result of that expression in another new field? If so, how is it done? I want to get an average of each individual group and then add up these individual results to get a grand total. Thanks in advance for your help. |
#2
|
|||
|
|||
Use result of expression in another expression??
Serendipity wrote:
Can I go to the first empty column in a design query grid and use an expression to add a new field row and then use the result of that expression in another new field? If so, how is it done? I want to get an average of each individual group and then add up these individual results to get a grand total. Thanks in advance for your help. You have to repeat the expression. Instead of... Expr1: Field1 + Field2 | Expr2: Expr1 * Field3 ....you have to use... Expr1: Field1 + Field2 | Expr2: (Field1 + Field2) * Field3 -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
Use result of expression in another expression??
Thanks, but I guess I am too new at this.
My first new field which worked was AvgOfValue: Avg([fldGrade])*([fldPointValue]/100) This gives me the average grade of each of five different categories (each category has a different weight). Now I want to sum the individual results to get a final grade. What expression would I use in the next new field (FinalGrade? Was the | in your example the character above the \ on my keyboard. On all the expressions I tried, I got an "invalid use of |" error message. Thanks! "Rick Brandt" wrote: Serendipity wrote: Can I go to the first empty column in a design query grid and use an expression to add a new field row and then use the result of that expression in another new field? If so, how is it done? I want to get an average of each individual group and then add up these individual results to get a grand total. Thanks in advance for your help. You have to repeat the expression. Instead of... Expr1: Field1 + Field2 | Expr2: Expr1 * Field3 ....you have to use... Expr1: Field1 + Field2 | Expr2: (Field1 + Field2) * Field3 -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#4
|
|||
|
|||
Use result of expression in another expression??
Serendipity wrote:
Thanks, but I guess I am too new at this. My first new field which worked was AvgOfValue: Avg([fldGrade])*([fldPointValue]/100) This gives me the average grade of each of five different categories (each category has a different weight). Now I want to sum the individual results to get a final grade. What expression would I use in the next new field (FinalGrade? Was the | in your example the character above the \ on my keyboard. On all the expressions I tried, I got an "invalid use of |" error message. The | was just my way of trying to visually indicate a separator between twp fields on the query. I think what you want is... FinalGrade: Sum(Avg([fldGrade])*([fldPointValue]/100)) -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#5
|
|||
|
|||
Use result of expression in another expression??
I put in the following expression--it's cut and pasted here from my query grid
FinalGrade: Sum(Avg([fldGrade])*([fldPointValue]/100)) And I get the error message Cannot have aggregate function in expression (Sum(Avg([fldGrade])*([fldPointValue]/100))). I notice that I only put two ))s at the end but the error message put three. I have "expression" in the total row but have also tried "by group" and "sum". "Rick Brandt" wrote: Serendipity wrote: Thanks, but I guess I am too new at this. My first new field which worked was AvgOfValue: Avg([fldGrade])*([fldPointValue]/100) This gives me the average grade of each of five different categories (each category has a different weight). Now I want to sum the individual results to get a final grade. What expression would I use in the next new field (FinalGrade? Was the | in your example the character above the \ on my keyboard. On all the expressions I tried, I got an "invalid use of |" error message. The | was just my way of trying to visually indicate a separator between twp fields on the query. I think what you want is... FinalGrade: Sum(Avg([fldGrade])*([fldPointValue]/100)) -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
Use result of expression in another expression??
Serendipity wrote:
I put in the following expression--it's cut and pasted here from my query grid FinalGrade: Sum(Avg([fldGrade])*([fldPointValue]/100)) And I get the error message Cannot have aggregate function in expression (Sum(Avg([fldGrade])*([fldPointValue]/100))). I notice that I only put two ))s at the end but the error message put three. I have "expression" in the total row but have also tried "by group" and "sum". I believe if you base a report on your query you could then get the report to sum the result of your first expression. The second one might not be possible to do in the same query. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#7
|
|||
|
|||
Use result of expression in another expression??
I do have that first expression working in the report but can't figure out
how to sum on it. Could you please tell me how to sum on my group totals which were obtained by my working formula? (I can also get the group totals by putting in the calculated fied from the query.) I have read that the control needs to be duplicated in the report but I can't get that to work--or I am not doing it correctly. How do you duplicate an unbound control with an expression in it? Again, thanks! I have worked on this for days. "Rick Brandt" wrote: Serendipity wrote: I put in the following expression--it's cut and pasted here from my query grid FinalGrade: Sum(Avg([fldGrade])*([fldPointValue]/100)) And I get the error message Cannot have aggregate function in expression (Sum(Avg([fldGrade])*([fldPointValue]/100))). I notice that I only put two ))s at the end but the error message put three. I have "expression" in the total row but have also tried "by group" and "sum". I believe if you base a report on your query you could then get the report to sum the result of your first expression. The second one might not be possible to do in the same query. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#8
|
|||
|
|||
Use result of expression in another expression??
Serendipity wrote:
I do have that first expression working in the report but can't figure out how to sum on it. Could you please tell me how to sum on my group totals which were obtained by my working formula? (I can also get the group totals by putting in the calculated fied from the query.) I have read that the control needs to be duplicated in the report but I can't get that to work--or I am not doing it correctly. How do you duplicate an unbound control with an expression in it? Again, thanks! I have worked on this for days. If you have any field in the Report's RecordSource with a numeric value in it then you can use a ControlSource of =Sum(FieldName) in a TextBox that is placed in the appropriate Group Header/Footer or in the Report Header/Footer. However; it is usually the case that summing a value that is based on the average of something else will not produce a result that is meaningful. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#9
|
|||
|
|||
Use result of expression in another expression??
Thanks for your patience. I am trying to design a grading database for the
teachers at the school where I work. My approach was to let each teacher choose the percentages that they want each type of assignment to be worth, the total adding up to 100. The input form has the teacher select the weight of each graded assignment. I then averaged the grades within each weight and then was trying to add the result for each weight to get the final grade. Is there a better approach? Can you direct me to something to read on creating grading databases? I have committed to this, and I am in over my head. Thanks. "Rick Brandt" wrote: Serendipity wrote: I do have that first expression working in the report but can't figure out how to sum on it. Could you please tell me how to sum on my group totals which were obtained by my working formula? (I can also get the group totals by putting in the calculated fied from the query.) I have read that the control needs to be duplicated in the report but I can't get that to work--or I am not doing it correctly. How do you duplicate an unbound control with an expression in it? Again, thanks! I have worked on this for days. If you have any field in the Report's RecordSource with a numeric value in it then you can use a ControlSource of =Sum(FieldName) in a TextBox that is placed in the appropriate Group Header/Footer or in the Report Header/Footer. However; it is usually the case that summing a value that is based on the average of something else will not produce a result that is meaningful. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#10
|
|||
|
|||
Use result of expression in another expression??
Serendipity wrote:
Thanks, but I guess I am too new at this. My first new field which worked was AvgOfValue: Avg([fldGrade])*([fldPointValue]/100) This gives me the average grade of each of five different categories (each category has a different weight). Now I want to sum the individual results to get a final grade. What expression would I use in the next new field (FinalGrade? Was the | in your example the character above the \ on my keyboard. On all the expressions I tried, I got an "invalid use of |" error message. Thanks! Hi Serendipity, I gave a more thorough answer to this today in an earlier thread. -- Smartin |
Thread Tools | |
Display Modes | |
|
|