Solved

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

Posted on 2012-03-12
5
247 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

757 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

18 Experts available now in Live!

Get 1:1 Help Now