Do sums and a complex count

[Frank Dubuc]

Hi,

I have this structure:

Fields: A B C
Record

1 0 1 0
2 2 3 0
3 0 0 0
4 1 0 0
5 0 0 1
6 0 0 0
7 4 1 2

I want a query that sums seperatly the 3 fileds: Sum of A:
7, Sum of B: 5, Sum of C: 3. (I know how to do that). The
other thing that the query must do to satisfy my report is
count how many records with at least one fileds with a
value higher than zero: in this case 5 records because we
don't count record 3 and 6.

How can I get the count and the 3 sums on the same query to
produce my report?

 

[Duane Hookom]

Select Sum(A) As SumA, Sum(B) As SumB, Sum(C) As SumC
FROM tblZ
WHERE [A]++[C]>0;

 

[John Verhagen]

Try this:
SELECT Sum(tblCount.A) AS SumOfA, Sum(tblCount.B) AS SumOfB, Sum(tblCount.C)
AS SumOfC, Sum(-([A]>0 Or >0 Or [C]>0)) AS NonZero
FROM tblCount;

 

[Frank Dubuc]

It worked well. I didn't know that I could use SQL in
Access query expression. Thanks a lot that's what I needed.

Frank

-----Original Message-----
Select Sum(A) As SumA, Sum(B) As SumB, Sum(C) As SumC
FROM tblZ
WHERE [A]++[C]>0;

--
Duane Hookom
MS Access MVP

Hi,

I have this structure:

Fields: A B C
Record

1 0 1 0
2 2 3 0
3 0 0 0
4 1 0 0
5 0 0 1
6 0 0 0
7 4 1 2

I want a query that sums seperatly the 3 fileds: Sum of A:
7, Sum of B: 5, Sum of C: 3. (I know how to do that). The
other thing that the query must do to satisfy my report is
count how many records with at least one fileds with a
value higher than zero: in this case 5 records because we
don't count record 3 and 6.

How can I get the count and the 3 sums on the same query to
produce my report?

.