• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

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

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
Jarred Meyer
Asked:
Jarred Meyer
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Not sure I understand the problem; just leave the sum column and nothing else.

Jim.
0
 
mbizupCommented:
Try removing the grouping on that field and use a wher clause for criteria instead of having.
0
 
Rey Obrero (Capricorn1)Commented:
change Group by with Expression in the Seq field
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Capricorn, I tried Expression.. That gave me an error.

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

Thanks a bunch!
0
 
Jarred MeyerProduction ManagerAuthor 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".
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now