Solved

Trying to Sum in access query with criteria without grouping by critieria.

Posted on 2012-03-12
5
252 Views
Last Modified: 2012-03-12
I have lines of amounts that I differentiate with Sequence numbers (Seq is the field name). I want to Sum Seq 1 through 4 without having the query display them as individual lines.

Here is a screenshot of the query;
query
and here is the sql;
SELECT Sum(qryCSLPL.SumOfAmount) AS Amount, 5 AS Seq
FROM qryCSLPL
GROUP BY 5, qryCSLPL.Seq
HAVING (((qryCSLPL.Seq)>=1 And (qryCSLPL.Seq)<=4));


My results for this give me each seq 1 through 4 on seperate lines. Where I want to see just one line with a total for all four of them.

Any ideas?
0
Comment
Question by:SeyerIT
5 Comments
 
LVL 57
ID: 37711306
Not sure I understand the problem; just leave the sum column and nothing else.

Jim.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37711312
Try removing the grouping on that field and use a wher clause for criteria instead of having.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37711339
change Group by with Expression in the Seq field
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37711358
Capricorn, I tried Expression.. That gave me an error.

There 'Where' clause that mbizup mentioned did the trick.

Thanks a bunch!
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37711370
Sorry Jim.. Somehow I didn't even see your comment at the top. Here is what I was trying to end up with in my result for the query;

Seq                        LineItem                                          Amount
5                          "Total Gross Sales"                            $341,324.00


It needed to sum Seq 1-4, give me that total amount and then the new Seq would be 5 and I would designate the LineItem name as "Total Gross Sales".
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now