Calculations in a Query

[Peter]

I have a table in Access 2003 that contains several numeric fields, lets call
them aa, bb, cc, and dd. Is it possible to create a select query, based on
this table, which contains a calculated field that sums the content of *only*
those fields aa, bb, cc, dd that I pull into the design view grid of the
query? I know I can “manually†alter the calculated field to add only those
fields that I require but I would really like to be able to sum only those in
the query and for the expressing to look only to those fields I have dragged
into the query. To belabour the point, if I drag aa and cc into the query
the calculated field gives me the sum of aa and cc. But if I now also drag
dd into the grid the calculated field automatically gives me the sum of aa,
cc and dd.

I hope I have explained myself clearly. Many thanks in advance, your advice
has got me out of many sticky places in the past. Peter.

 

[KARL DEWEY]

If you are talking of adding the individual fields (vertically) then a Totals
query will do it. Or maybe you are wanting to add then fields horizontally
then you need to edit an output field to include the field names that you
want to add.
Sum(NZ([aa],0)+NZ([bb],0)+NZ([dd],0))

 

[Peter]

Karl - thank you. I do want to sum horizontally and the expression that you
give in your reply is what I am doing. I was hoping that it could be
modified so that it automatically summed only the fields that were included
in the query design grid so that I did not have to adjust it manually every
time I change the query. From your reply I doubt that this is possible.

Thanks again, Peter

If you are talking of adding the individual fields (vertically) then a Totals
query will do it. Or maybe you are wanting to add then fields horizontally
then you need to edit an output field to include the field names that you
want to add.
Sum(NZ([aa],0)+NZ([bb],0)+NZ([dd],0))
--
KARL DEWEY
Build a little - Test a little

I have a table in Access 2003 that contains several numeric fields, lets call
them aa, bb, cc, and dd. Is it possible to create a select query, based on
this table, which contains a calculated field that sums the content of *only*
those fields aa, bb, cc, dd that I pull into the design view grid of the
query? I know I can “manually†alter the calculated field to add only those
fields that I require but I would really like to be able to sum only those in
the query and for the expressing to look only to those fields I have dragged
into the query. To belabour the point, if I drag aa and cc into the query
the calculated field gives me the sum of aa and cc. But if I now also drag
dd into the grid the calculated field automatically gives me the sum of aa,
cc and dd.

I hope I have explained myself clearly. Many thanks in advance, your advice
has got me out of many sticky places in the past. Peter.

 

[KARL DEWEY]

it automatically summed only the fields that were included in the query
design grid so that I did not have to adjust it manually every time I change
the query.
I do not understand what you want to do and not have to do each time.

You say "every time I change the query" and in the same sentence say "so
that I did not have to adjust it manually" which is the same thing to me.

What am I missing?
--
KARL DEWEY
Build a little - Test a little

Karl - thank you. I do want to sum horizontally and the expression that you
give in your reply is what I am doing. I was hoping that it could be
modified so that it automatically summed only the fields that were included
in the query design grid so that I did not have to adjust it manually every
time I change the query. From your reply I doubt that this is possible.

Thanks again, Peter

If you are talking of adding the individual fields (vertically) then a Totals
query will do it. Or maybe you are wanting to add then fields horizontally
then you need to edit an output field to include the field names that you
want to add.
Sum(NZ([aa],0)+NZ([bb],0)+NZ([dd],0))
--
KARL DEWEY
Build a little - Test a little

I have a table in Access 2003 that contains several numeric fields, lets call
them aa, bb, cc, and dd. Is it possible to create a select query, based on
this table, which contains a calculated field that sums the content of *only*
those fields aa, bb, cc, dd that I pull into the design view grid of the
query? I know I can “manually†alter the calculated field to add only those
fields that I require but I would really like to be able to sum only those in
the query and for the expressing to look only to those fields I have dragged
into the query. To belabour the point, if I drag aa and cc into the query
the calculated field gives me the sum of aa and cc. But if I now also drag
dd into the grid the calculated field automatically gives me the sum of aa,
cc and dd.

I hope I have explained myself clearly. Many thanks in advance, your advice
has got me out of many sticky places in the past. Peter.

 

[Peter]

Karl - sorry. I am caught between trying to be brief and giving a proper
explanation. Yes I will be adjusting the *query* manually by including or
excluding fields from the table into it. But I want the *calculated field*
containing the sum() function to adjust automatically to include in that sum,
only the field(s) that I have draged into the query. So, on one occasion I
might include fields aa and bb in the query design and the calculated field
would sum aa and bb. But on another occasion I might include aa, bb, and dd
into the query. Now the calculated field would automatically sum aa, bb, and
dd without me having to manually adjust the formula. With many field this can
be slow and error prone.

Maybe I should add that I am using Access to analyse,examine and investigate
large amounts of data in an interactive manner. I am not creating a typical
database application. I am using it more like Excel. The data is just too
much for Excel. In fact once I have summarised it sufficently I do transfer
it into a spreadsheet.

I really do appreciate your time and I am sorry for an confusion. Peter

design grid so that I did not have to adjust it manually every time I change
the query.
I do not understand what you want to do and not have to do each time.

You say "every time I change the query" and in the same sentence say "so
that I did not have to adjust it manually" which is the same thing to me.

What am I missing?
--
KARL DEWEY
Build a little - Test a little

Karl - thank you. I do want to sum horizontally and the expression that you
give in your reply is what I am doing. I was hoping that it could be
modified so that it automatically summed only the fields that were included
in the query design grid so that I did not have to adjust it manually every
time I change the query. From your reply I doubt that this is possible.

Thanks again, Peter

If you are talking of adding the individual fields (vertically) then a Totals
query will do it. Or maybe you are wanting to add then fields horizontally
then you need to edit an output field to include the field names that you
want to add.
Sum(NZ([aa],0)+NZ([bb],0)+NZ([dd],0))
--
KARL DEWEY
Build a little - Test a little


:

I have a table in Access 2003 that contains several numeric fields, lets call
them aa, bb, cc, and dd. Is it possible to create a select query, based on
this table, which contains a calculated field that sums the content of *only*
those fields aa, bb, cc, dd that I pull into the design view grid of the
query? I know I can “manually†alter the calculated field to add only those
fields that I require but I would really like to be able to sum only those in
the query and for the expressing to look only to those fields I have dragged
into the query. To belabour the point, if I drag aa and cc into the query
the calculated field gives me the sum of aa and cc. But if I now also drag
dd into the grid the calculated field automatically gives me the sum of aa,
cc and dd.

I hope I have explained myself clearly. Many thanks in advance, your advice
has got me out of many sticky places in the past. Peter.