Add Calculations to existing aggregate functions

[Bee]

Hi,

Is it possible to add a calcualted field to a query that already uses 'sum'?
I want to take the value returned by sum and add the value of another field to it.

Any help will be very much appreciated.
B

 

[Brianwarnock]

Hi,

Is it possible to add a calcualted field to a query that already uses 'sum'?
I want to take the value returned by sum and add the value of another field to it.

Any help will be very much appreciated.
B

The aggregate functions work across the recordset, how can you add another field to it all the records will have been read. Not sure I understand what you are trying to do, if you are trying to say sum field1 for the selected records, then add field2 to produce field3 for each record then do it as a second query.

Brian

 

[Bee]

The aggregate functions work across the recordset, how can you add another field to it all the records will have been read. Not sure I understand what you are trying to do, if you are trying to say sum field1 for the selected records, then add field2 to produce field3 for each record then do it as a second query.

Brian

Yes, what you mentioned is what I wanted to do. I will try second query.
Thank you.
B

 

[The_Doc_Man]

There are a couple of possible ways to interpret this.

A query can certainly contain a box that says Sum([X]) + Sum([Y]) where X and Y are two fields in the underlying table.

A query can certainly contain a box that says Sum([X]) + 6.

However, mathematically, if X and Y are two fields in two different tables that participate in an inner join, you get something a little more complex. There is a cardinality constant associated with such cases, showing how many times each JOIN occurred for the base table. This is in line with basic algebra rules for summations. Heavens forfend that the JOIN ever becomes an OUTER JOIN, because then you would NEVER know what you really have.

 

[Brianwarnock]

As Bee had used the words "Aggregate functions" in the title of the thread I had assumed that he was selecting sum in the Total line.

Brian

 

[Bee]

There are a couple of possible ways to interpret this.

A query can certainly contain a box that says Sum([X]) + Sum([Y]) where X and Y are two fields in the underlying table.

A query can certainly contain a box that says Sum([X]) + 6.

However, mathematically, if X and Y are two fields in two different tables that participate in an inner join, you get something a little more complex. There is a cardinality constant associated with such cases, showing how many times each JOIN occurred for the base table. This is in line with basic algebra rules for summations. Heavens forfend that the JOIN ever becomes an OUTER JOIN, because then you would NEVER know what you really have.

I have a sum([X]) in a query where X is from one table and I want to add the value of a field from another table to the sum. The value I wanted to add though is not a summed value. So in theory, it will be pretty much what you said: Sum([X]) + 6 where 6 is the value of the field from the other table.

The problem is that, it does not accept this forumula:

Sum([X]) + [FieldName]

Am i doing any thing wrong?

 

[Brianwarnock]

I'm sorry but Sum([X]) + [FieldName] is not at all the same as Sum([X]) + 6, as I said earlier you need 2 queries, the second using the result of the aggregate query.

Brian

 

[Bee]

I'm sorry but Sum([X]) + [FieldName] is not at all the same as Sum([X]) + 6, as I said earlier you need 2 queries, the second using the result of the aggregate query.

Brian

Oh sorry, i thought it was the same. So, will go for 2 queries.

Instead of using 2queries, how about adding that last value [FieldName] using VB? Or is it a bad practice?

 

[Brianwarnock]

Oh sorry, i thought it was the same. So, will go for 2 queries.

Instead of using 2queries, how about adding that last value [FieldName] using VB? Or is it a bad practice?

I can't see the point it just involves a lot of work, and probably less effecient code.
You seem to be against multi queries but they are quite common, even normal, you only run the last the system does the rest.

Brian

 

[The_Doc_Man]

Where you do this depends on where you need it and how you built it.

If you are saying SUM([X]) + [arbitrary-non-aggregate-field] then you have one case.

If you are saying SUM([X]) WHERE some-filter-critera... + [non-aggregate-field related to same thing that selected X ] then you are saying something else.

In the first case, you could just do a DLookup of the loosely-related field. In the second case, that should only be done via multi-layered query because of implied synchronization between the SUM query and the selection of the other field.

So I'm not trying to be vague, it is just that your answer is multi-valued.

 

[Brianwarnock]

Hi Doc

Bee agreed to this statement

Not sure I understand what you are trying to do, if you are trying to say sum field1 for the selected records, then add field2 to produce field3 for each record

which is why I kept telling him to use a second query, if he was trying to merely add a constant from a field that happened to be in another table to produce 1 record maybe another approach could be used

Brian

 

[Bee]

Hi,

I tried using this formula to sum the agreed price which is a field in the table and the sum of vo price which is a field summed by the sum function. The query adds the values fine, but it asks for the parameter 'SumOfVO_Price' before running.

totalPriceIncVO: [Agreed_Price]+[SumOfVO_Price]

Is there any way to make the query not ask for the parameter?

 

[Brianwarnock]

You should only get a prompt if the field does not exist so that the system treats it as a parameter field. What do you do, just hit enter and it all works? It seems most odd and I haven't a clue.

Brian