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.

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

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

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

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

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

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

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

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

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