We help IT Professionals succeed at work.

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

Jarred Meyer
Jarred Meyer asked
on
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?
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Not sure I understand the problem; just leave the sum column and nothing else.

Jim.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Try removing the grouping on that field and use a wher clause for criteria instead of having.
CERTIFIED EXPERT
Top Expert 2016

Commented:
change Group by with Expression in the Seq field
Jarred MeyerProduction Manager

Author

Commented:
Capricorn, I tried Expression.. That gave me an error.

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

Thanks a bunch!
Jarred MeyerProduction Manager

Author

Commented:
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".

Explore More ContentExplore courses, solutions, and other research materials related to this topic.