Using Sum in a Totals Query | Newbie | | Join Date: Aug 2007
Posts: 4
| | |
I know this is a stupid question but...
I'm trying to total some columns in a query using sum. I'm not getting any results, what am I not seeing?
And my apologies, I'm new to access.
|  | Moderator | | Join Date: Jun 2007 Location: Niagara Falls, Ontario
Posts: 557
| | | re: Using Sum in a Totals Query
Could you post the SQL for your query. It would be help in figuring out where the error is.
Also perhaps explain briefly the data you are working with and what result you wish to produce from the query so not only can we spot the error but ensure you get the proper results.
| | Newbie | | Join Date: Aug 2007
Posts: 4
| | | re: Using Sum in a Totals Query
Quote:
Originally Posted by JKing Could you post the SQL for your query. It would be help in figuring out where the error is.
Also perhaps explain briefly the data you are working with and what result you wish to produce from the query so not only can we spot the error but ensure you get the proper results. I'm just tracking donations by Name and category(numeric) for a nonprofit org..Here's the sql: -
SELECT MEF2008.[Last Name], MEF2008.[First Name], Sum(MEF2008.[MEF 2008]) AS [SumOfMEF 2008], Sum(MEF2008.[PTC 2008]) AS [SumOfPTC 2008], Sum(MEF2008.[Booster 2008]) AS [SumOfBooster 2008], Sum(MEF2008.[B-Lights 2008]) AS [SumOfB-Lights 2008], Sum(MEF2008.[B-Cards 2008]) AS [SumOfB-Cards 2008], Sum(MEF2008.[Proj Grad 2008]) AS [SumOfProj Grad 2008], Sum(MEF2008.[Snr Bkfst 2008]) AS [SumOfSnr Bkfst 2008], Sum(MEF2008.[WBB 2008]) AS [SumOfWBB 2008], Sum(MEF2008.[IB 2008]) AS [SumOfIB 2008], Sum(MEF2008.[TWL 2008]) AS [SumOfTWL 2008], Sum(MEF2008.[TA 2008]) AS [SumOfTA 2008], Sum(MEF2008.[CB 2008]) AS [SumOfCB 2008], Sum(MEF2008.[SFA 2008]) AS [SumOfSFA 2008], Sum(MEF2008.[ST 2008]) AS [SumOfST 2008], Sum(MEF2008.[MB 2008]) AS [SumOfMB 2008], Sum(MEF2008.[Drama 2008]) AS [SumOfDrama 2008], Sum(MEF2008.[A-A-B 2008]) AS [SumOfA-A-B 2008], Sum(MEF2008.[HR 2008]) AS [SumOfHR 2008], Sum(MEF2008.[Art 2008]) AS [SumOfArt 2008], Sum(MEF2008.[MEM 2008]) AS [SumOfMEM 2008], Sum(MEF2008.[PF 2008]) AS [SumOfPF 2008]
-
FROM MEF2008
-
GROUP BY MEF2008.[Last Name], MEF2008.[First Name]
-
OR (((Sum(MEF2008.[PTC 2008]))>(0))) OR (((Sum(MEF2008.[Booster 2008]))>(0))) OR (((Sum(MEF2008.[B-Lights 2008]))>(0))) OR (((Sum(MEF2008.[B-Cards 2008]))>(0))) OR (((Sum(MEF2008.[Proj Grad 2008]))>(0))) OR (((Sum(MEF2008.[Snr Bkfst 2008]))>(0))) OR (((Sum(MEF2008.[WBB 2008]))>(0))) OR (((Sum(MEF2008.[IB 2008]))>(0))) OR (((Sum(MEF2008.[TWL 2008]))>(0))) OR (((Sum(MEF2008.[TA 2008]))>(0))) OR (((Sum(MEF2008.[CB 2008]))>(0))) OR (((Sum(MEF2008.[SFA 2008]))>(0))) OR (((Sum(MEF2008.[ST 2008]))>(0))) OR (((Sum(MEF2008.[MB 2008]))>(0))) OR (((Sum(MEF2008.[Drama 2008]))>(0))) OR (((Sum(MEF2008.[A-A-B 2008]))>(0))) OR (((Sum(MEF2008.[HR 2008]))>(0))) OR (((Sum(MEF2008.[Art 2008]))>(0))) OR (((Sum(MEF2008.[MEM 2008]))>(0))) OR (((Sum(MEF2008.[PF 2008]))>(0)));
-
| | Moderator | | Join Date: Jun 2007 Location: Niagara Falls, Ontario
Posts: 557
| | | re: Using Sum in a Totals Query
I think the problem is in your GROUP BY clause. I really don't understand the use of ORs in there. What is you're trying to accomplish there?
| | Newbie | | Join Date: Aug 2007
Posts: 4
| | | re: Using Sum in a Totals Query
I copied the query from one showing individual donors, not wanting to show zero dollar contributions. It shouldn't make any difference though? What do you mean the problem is in my group by settings?
| | Moderator | | Join Date: Jun 2007 Location: Niagara Falls, Ontario
Posts: 557
| | | re: Using Sum in a Totals Query
I think you want something closer to this: -
SELECT MEF2008.[Last Name], MEF2008.[First Name], Sum(MEF2008.[MEF 2008]) AS [SumOfMEF 2008], Sum(MEF2008.[PTC 2008]) AS [SumOfPTC 2008], Sum(MEF2008.[Booster 2008]) AS [SumOfBooster 2008], Sum(MEF2008.[B-Lights 2008]) AS [SumOfB-Lights 2008], Sum(MEF2008.[B-Cards 2008]) AS [SumOfB-Cards 2008], Sum(MEF2008.[Proj Grad 2008]) AS [SumOfProj Grad 2008], Sum(MEF2008.[Snr Bkfst 2008]) AS [SumOfSnr Bkfst 2008], Sum(MEF2008.[WBB 2008]) AS [SumOfWBB 2008], Sum(MEF2008.[IB 2008]) AS [SumOfIB 2008], Sum(MEF2008.[TWL 2008]) AS [SumOfTWL 2008], Sum(MEF2008.[TA 2008]) AS [SumOfTA 2008], Sum(MEF2008.[CB 2008]) AS [SumOfCB 2008], Sum(MEF2008.[SFA 2008]) AS [SumOfSFA 2008], Sum(MEF2008.[ST 2008]) AS [SumOfST 2008], Sum(MEF2008.[MB 2008]) AS [SumOfMB 2008], Sum(MEF2008.[Drama 2008]) AS [SumOfDrama 2008], Sum(MEF2008.[A-A-B 2008]) AS [SumOfA-A-B 2008], Sum(MEF2008.[HR 2008]) AS [SumOfHR 2008], Sum(MEF2008.[Art 2008]) AS [SumOfArt 2008], Sum(MEF2008.[MEM 2008]) AS [SumOfMEM 2008], Sum(MEF2008.[PF 2008]) AS [SumOfPF 2008]
-
FROM MEF2008
-
WHERE (((Sum(MEF2008.[PTC 2008]))>(0))) OR (((Sum(MEF2008.[Booster 2008]))>(0))) OR (((Sum(MEF2008.[B-Lights 2008]))>(0))) OR (((Sum(MEF2008.[B-Cards 2008]))>(0))) OR (((Sum(MEF2008.[Proj Grad 2008]))>(0))) OR (((Sum(MEF2008.[Snr Bkfst 2008]))>(0))) OR (((Sum(MEF2008.[WBB 2008]))>(0))) OR (((Sum(MEF2008.[IB 2008]))>(0))) OR (((Sum(MEF2008.[TWL 2008]))>(0))) OR (((Sum(MEF2008.[TA 2008]))>(0))) OR (((Sum(MEF2008.[CB 2008]))>(0))) OR (((Sum(MEF2008.[SFA 2008]))>(0))) OR (((Sum(MEF2008.[ST 2008]))>(0))) OR (((Sum(MEF2008.[MB 2008]))>(0))) OR (((Sum(MEF2008.[Drama 2008]))>(0))) OR (((Sum(MEF2008.[A-A-B 2008]))>(0))) OR (((Sum(MEF2008.[HR 2008]))>(0))) OR (((Sum(MEF2008.[Art 2008]))>(0))) OR (((Sum(MEF2008.[MEM 2008]))>(0))) OR (((Sum(MEF2008.[PF 2008]))>(0)))
-
GROUP BY MEF2008.[Last Name], MEF2008.[First Name]
-
;
-
| | Newbie | | Join Date: Aug 2007
Posts: 4
| | | re: Using Sum in a Totals Query
Thanks, I removed the group by fields and that got me close. Any idea whats the best way to total columns on detailed records as you would in excel though?
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
