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;
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?
Here is a screenshot of the 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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
change Group by with Expression in the Seq field
ASKER
Capricorn, I tried Expression.. That gave me an error.
There 'Where' clause that mbizup mentioned did the trick.
Thanks a bunch!
There 'Where' clause that mbizup mentioned did the trick.
Thanks a bunch!
ASKER
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".
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".
Jim.