sum function in query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that consists of 10 other queries in design view, I'd like to
add one field from each query to give me a total in one field.

How do I enter the expression?
 
i tried on sql server with products table

select DISTINCT (
(select UnitPrice from products where productid=1)
+
(select UnitPrice from products where productid=2)
+
(select UnitPrice from products where productid=3)
) AS my_sum_column

from products
where supplierid=1

hope this will help.

arif.
 
In a query, in design mode, you could add a new query output field something
like:

OutputTotal: [qry1]![field1] + [qry2]![field2] + ...

A couple notes:

First, if there's a chance ANY of the values will be Null, use the Nz()
function, like:

Nz([qry1]![field1],0) + Nz([qry2]![field2],0) + ...

Second, if the names of the fields are unique among the multiple queries you
are using, you can get away with something like:

OutputTotal: Nz([field1],0) + Nz([field2],0) + ...
 
Back
Top