Enter Parameter Value Error for Alias field

[harshadborde]

Hello Friends,

I facing problem while calculating a new field.

I want to calculate a new field M4 which is --> 19* [SUM OF M1]

This 'SUM OF M1' is summary of M1 field. Though its already present access is again asking me by throwing 'ENTER PARAMETER VALUE: SUM OF M1'

Help is much appreciated.

 

[Galaxiom]

Aggregate calculations are only available at the end of the query.

You might be able to use:

19*Sum([M1])

 

[harshadborde]

But this code is throwing error that aggregate function not allowed in the end.

 

[Galaxiom]

The problem might be that you have spaces in the derived field name. Either get rid of them or put square brackets around the name.

Otherwise you need to post the full detail of the queries.

 

[CJ_London]

if [SUM OF M1] has be derived from a group by query and in the same query you want to multiply this sum by 19 - it can't be done because you cannot reference the alias at that level. Instead you need sum(M1*19). Alternatively save your group by query and do the multiplication in a new query, referencing your saved query

 

[Galaxiom]

if [SUM OF M1] has be derived from a group by query and in the same query you want to multiply this sum by 19 - it can't be done because you cannot reference the alias at that level. Instead you need sum(M1*19). Alternatively save your group by query and do the multiplication in a new query, referencing your saved query

Although the alias cannot be referenced in the same query, the expression in my first post (19*Sum([M1]) does work. Arithmetic operations can be performed on aggregates by changing the "Total" setting for the field in the query designer to Expression.

Of course all the usual limitations of aggregate queries still apply.

 

[CJ_London]

by changing the "Total" setting for the field in the query designer to Expression

New one for me, have never tried that

 

[Galaxiom]

New one for me, have never tried that

That is what I like about coming here. You just never know what pops up.