Displaying Sum Only

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

Guest

Using Excel, I have built a query into an Access database and am only
interested in
displaying the sum. Is there a way to only display the sum total and not all
the data, followed by the sum?

Thanks
 
without seeing your code, i can only guess but it would
seem like you have structured your query to return all the
data. re-structure the query to return only the totals.
 
That's the problem I'm having. I can't figure out how to restructure the
query to just return totals. I used the Cycle Through Total Button (the
Greek Sigma) to cycle through to the sum calaulation.
 
hi again,
Post your code.
-----Original Message-----
That's the problem I'm having. I can't figure out how to restructure the
query to just return totals. I used the Cycle Through Total Button (the
Greek Sigma) to cycle through to the sum calaulation.


.
Click to expand...
 
Here it is -

SELECT Sum(`Data Entry`.`Committment Amount`) AS 'Sum of Committment Amount'
FROM `G:\data\O\OA\Stew\Master Budget`.`Data Entry` `Data Entry`
WHERE (`Data Entry`.`IT43 Class`='1.A') OR (`Data Entry`.`IT43
Class`='1.A.1') OR (`Data Entry`.`IT43 Class`='1.A.2') OR (`Data Entry`.`IT43
Class`='1.B') OR (`Data Entry`.`IT43 Class`='1.B.1') OR (`Data Entry`.`IT43
Class`='1.B.2') OR (`Data Entry`.`IT43 Class`='1.B.3') OR (`Data Entry`.`IT43
Class`='1.B.3.a') OR (`Data Entry`.`IT43 Class`='1.B.3.b') OR (`Data
Entry`.`IT43 Class`='1.C') OR (`Data Entry`.`IT43 Class`='1.C.1') OR (`Data
Entry`.`IT43 Class`='1.C.2') OR (`Data Entry`.`IT43 Class`='1.C.2.a') OR
(`Data Entry`.`IT43 Class`='1.C.2.b') OR (`Data Entry`.`IT43 Class`='1.C.3')
OR (`Data Entry`.`IT43 Class`='1.C.3.a') OR (`Data Entry`.`IT43
Class`='1.C.3.b') OR (`Data Entry`.`IT43 Class`='1.C.4') OR (`Data
Entry`.`IT43 Class`='1.C.4.a') OR (`Data Entry`.`IT43 Class`='1.C.4.b') OR
(`Data Entry`.`IT43 Class`='1.C.5') OR (`Data Entry`.`IT43 Class`='1.D') OR
(`Data Entry`.`IT43 Class`='1.D.1') OR (`Data Entry`.`IT43 Class`='1.D.2') OR
(`Data Entry`.`IT43 Class`='1.D.3') OR (`Data Entry`.`IT43 Class`='1.D.4') OR
(`Data Entry`.`IT43 Class`='1.D.5') OR (`Data Entry`.`IT43 Class`='1.D.5.a')
OR (`Data Entry`.`IT43 Class`='1.D.5.b') OR (`Data Entry`.`IT43
Class`='1.D.6') OR (`Data Entry`.`IT43 Class`='1.D.6.a') OR (`Data
Entry`.`IT43 Class`='1.D.6.b') OR (`Data Entry`.`IT43 Class`='1.D.6.c')
GROUP BY `Data Entry`.`Committment Date`
HAVING (`Data Entry`.`Committment Date`>={ts '2004-10-01 00:00:00'} And
`Data Entry`.`Committment Date`<={ts '2004-10-31 00:00:00'})

I really appreciate your assistance.

SCW
 
when using the sum function, you can't have a group by.
I think that is what is bringing in all the data.
trash the group by clause and see what you get.
-----Original Message-----
Here it is -

SELECT Sum(`Data Entry`.`Committment Amount`) AS 'Sum of Committment Amount'
FROM `G:\data\O\OA\Stew\Master Budget`.`Data Entry` `Data Entry`
WHERE (`Data Entry`.`IT43 Class`='1.A') OR (`Data Entry`.`IT43
Class`='1.A.1') OR (`Data Entry`.`IT43 Class`='1.A.2') OR (`Data Entry`.`IT43
Class`='1.B') OR (`Data Entry`.`IT43 Class`='1.B.1') OR (`Data Entry`.`IT43
Class`='1.B.2') OR (`Data Entry`.`IT43 Class`='1.B.3') OR (`Data Entry`.`IT43
Class`='1.B.3.a') OR (`Data Entry`.`IT43 Class`='1.B.3.b') OR (`Data
Entry`.`IT43 Class`='1.C') OR (`Data Entry`.`IT43 Class`='1.C.1') OR (`Data
Entry`.`IT43 Class`='1.C.2') OR (`Data Entry`.`IT43 Class`='1.C.2.a') OR
(`Data Entry`.`IT43 Class`='1.C.2.b') OR (`Data Entry`.`IT43 Class`='1.C.3')
OR (`Data Entry`.`IT43 Class`='1.C.3.a') OR (`Data Entry`.`IT43
Class`='1.C.3.b') OR (`Data Entry`.`IT43 Class`='1.C.4') OR (`Data
Entry`.`IT43 Class`='1.C.4.a') OR (`Data Entry`.`IT43 Class`='1.C.4.b') OR
(`Data Entry`.`IT43 Class`='1.C.5') OR (`Data Entry`.`IT43 Class`='1.D') OR
(`Data Entry`.`IT43 Class`='1.D.1') OR (`Data
Click to expand...
Entry`.`IT43 Class`='1.D.2') OR
(`Data Entry`.`IT43 Class`='1.D.3') OR (`Data
Click to expand...
Entry`.`IT43 Class`='1.D.4') OR
 
Hi Anonymous -

Since I'm using the HAVING operator I must use the GROUP operator. I've
tried replacing HAVING with WHERE, but SQL doesn't recognize the syntax. Do
you know of another operator I can use to replace HAVING?

Thanks
 
hi again
I wondered about that. i wish i could duplicate your query
on my machine but.....
try moving the having clause into the where clause.
right now i'm not sure if the ANDs should come before the
ORs or the other way around. try them both and see.
good luck.
 
Can you use a WHERE Clause instead of the having Clause and dropping the GROUP
BY Clause? ALSO, you might look at using an IN operator in the Where Clause to
shorten the code and make it more readable.

SELECT Sum(`Data Entry`.`Committment Amount`) AS 'Sum of Committment Amount'
FROM `G:\data\O\OA\Stew\Master Budget`.`Data Entry` `Data Entry`
WHERE (`Data Entry`.`IT43 Class` IN
('1.A',1.A.1','1.A.2','1.B','1.B.1','1.B.2','1.B.3','1.B.3.a',
'1.B.3.b','1.C','1.C.1','1.C.2','1.C.2.a','1.C.2.b','1.C.3',
'1.C.3.a',1.C.3.b','1.C.4','1.C.4.a','1.C.4.b','1.C.5','1.D',
'1.D.1','1.D.2',1.D.3','1.D.4','1.D.5','1.D.5.a','1.D.5.b',
'1.D.6','1.D.6.a','1.D.6.b','1.D.6.c')
AND `Data Entry`.`Committment Date`>={ts '2004-10-01 00:00:00'} AND
`Data Entry`.`Committment Date`<={ts '2004-10-31 00:00:00'})

ALL THE ABOVE is UNTESTED.
 
Back
Top