Query summing on one line

S

Stephanie

Hi. I am struggling with a query. I want to see absolute value of loss by
quarter.
In my query, each Abs value loss lists the quarter and year it occurred.

What I'm getting is:
Abs QRT LoggedYear
20000 1 2007
6130 1 2007
2000 2 2007
6345 3 2007
1234 3 2007

What I want is

Abs QRT LoggedYear
26130 1 2007
2000 2 2007
7579 3 2007

so that I can graph by the Abs by quarter loss (I hope!)

SELECT qu_tbFinalIncident_NoLevel4.AuthorDept, Sum(Abs([GainLoss])) AS Abs,
([LoggedMonth]+2)\3 AS QRT, qu_tbFinalIncident_NoLevel4.LoggedYear
FROM qu_tbFinalIncident_NoLevel4
GROUP BY qu_tbFinalIncident_NoLevel4.AuthorDept, ([LoggedMonth]+2)\3,
qu_tbFinalIncident_NoLevel4.LoggedYear, qu_tbFinalIncident_NoLevel4.LoggedDate
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorDept)=[Forms]![frQuick]![Combo82]) AND
((Sum(Abs([GainLoss])))>0))
ORDER BY qu_tbFinalIncident_NoLevel4.AuthorDept,
qu_tbFinalIncident_NoLevel4.LoggedDate;

Thanks,
Stephanie
 
K

KARL DEWEY

Try this --
SELECT qu_tbFinalIncident_NoLevel4.AuthorDept, Sum(Abs([GainLoss])) AS Abs,
([LoggedMonth]+2)\3 AS QRT, qu_tbFinalIncident_NoLevel4.LoggedYear
FROM qu_tbFinalIncident_NoLevel4
GROUP BY qu_tbFinalIncident_NoLevel4.AuthorDept, ([LoggedMonth]+2)\3,
qu_tbFinalIncident_NoLevel4.LoggedYear
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorDept)=[Forms]![frQuick]![Combo82]) AND
((Sum(Abs([GainLoss])))>0))
ORDER BY qu_tbFinalIncident_NoLevel4.AuthorDept;

Removed qu_tbFinalIncident_NoLevel4.LoggedDate from query.
 
S

Stephanie

Karl, Thanks for taking a look. Removing [LoggedDate] did not do the trick.
The query is behaving the same, where I have

Abs QRT LoggedYear
20000 1 2007
6130 1 2007

I really struggle with the "summary" queries, where I want things combined
Try this --
SELECT qu_tbFinalIncident_NoLevel4.AuthorDept, Sum(Abs([GainLoss])) AS Abs,
([LoggedMonth]+2)\3 AS QRT, qu_tbFinalIncident_NoLevel4.LoggedYear
FROM qu_tbFinalIncident_NoLevel4
GROUP BY qu_tbFinalIncident_NoLevel4.AuthorDept, ([LoggedMonth]+2)\3,
qu_tbFinalIncident_NoLevel4.LoggedYear
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorDept)=[Forms]![frQuick]![Combo82]) AND
((Sum(Abs([GainLoss])))>0))
ORDER BY qu_tbFinalIncident_NoLevel4.AuthorDept;

Removed qu_tbFinalIncident_NoLevel4.LoggedDate from query.

Stephanie said:
Hi. I am struggling with a query. I want to see absolute value of loss by
quarter.
In my query, each Abs value loss lists the quarter and year it occurred.

What I'm getting is:
Abs QRT LoggedYear
20000 1 2007
6130 1 2007
2000 2 2007
6345 3 2007
1234 3 2007

What I want is

Abs QRT LoggedYear
26130 1 2007
2000 2 2007
7579 3 2007

so that I can graph by the Abs by quarter loss (I hope!)

SELECT qu_tbFinalIncident_NoLevel4.AuthorDept, Sum(Abs([GainLoss])) AS Abs,
([LoggedMonth]+2)\3 AS QRT, qu_tbFinalIncident_NoLevel4.LoggedYear
FROM qu_tbFinalIncident_NoLevel4
GROUP BY qu_tbFinalIncident_NoLevel4.AuthorDept, ([LoggedMonth]+2)\3,
qu_tbFinalIncident_NoLevel4.LoggedYear, qu_tbFinalIncident_NoLevel4.LoggedDate
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorDept)=[Forms]![frQuick]![Combo82]) AND
((Sum(Abs([GainLoss])))>0))
ORDER BY qu_tbFinalIncident_NoLevel4.AuthorDept,
qu_tbFinalIncident_NoLevel4.LoggedDate;

Thanks,
Stephanie
 
K

KARL DEWEY

Try it this way --

SELECT Sum(Abs([GainLoss])) AS Abs, ([LoggedMonth]+2)\3 AS QRT,
qu_tbFinalIncident_NoLevel4.LoggedYear
FROM qu_tbFinalIncident_NoLevel4
GROUP BY ([LoggedMonth]+2)\3, qu_tbFinalIncident_NoLevel4.LoggedYear
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorDept)=[Forms]![frQuick]![Combo82]) AND
((Sum(Abs([GainLoss])))>0));

Stephanie said:
Karl, Thanks for taking a look. Removing [LoggedDate] did not do the trick.
The query is behaving the same, where I have

Abs QRT LoggedYear
20000 1 2007
6130 1 2007

I really struggle with the "summary" queries, where I want things combined
Try this --
SELECT qu_tbFinalIncident_NoLevel4.AuthorDept, Sum(Abs([GainLoss])) AS Abs,
([LoggedMonth]+2)\3 AS QRT, qu_tbFinalIncident_NoLevel4.LoggedYear
FROM qu_tbFinalIncident_NoLevel4
GROUP BY qu_tbFinalIncident_NoLevel4.AuthorDept, ([LoggedMonth]+2)\3,
qu_tbFinalIncident_NoLevel4.LoggedYear
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorDept)=[Forms]![frQuick]![Combo82]) AND
((Sum(Abs([GainLoss])))>0))
ORDER BY qu_tbFinalIncident_NoLevel4.AuthorDept;

Removed qu_tbFinalIncident_NoLevel4.LoggedDate from query.

Stephanie said:
Hi. I am struggling with a query. I want to see absolute value of loss by
quarter.
In my query, each Abs value loss lists the quarter and year it occurred.

What I'm getting is:
Abs QRT LoggedYear
20000 1 2007
6130 1 2007
2000 2 2007
6345 3 2007
1234 3 2007

What I want is

Abs QRT LoggedYear
26130 1 2007
2000 2 2007
7579 3 2007

so that I can graph by the Abs by quarter loss (I hope!)

SELECT qu_tbFinalIncident_NoLevel4.AuthorDept, Sum(Abs([GainLoss])) AS Abs,
([LoggedMonth]+2)\3 AS QRT, qu_tbFinalIncident_NoLevel4.LoggedYear
FROM qu_tbFinalIncident_NoLevel4
GROUP BY qu_tbFinalIncident_NoLevel4.AuthorDept, ([LoggedMonth]+2)\3,
qu_tbFinalIncident_NoLevel4.LoggedYear, qu_tbFinalIncident_NoLevel4.LoggedDate
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorDept)=[Forms]![frQuick]![Combo82]) AND
((Sum(Abs([GainLoss])))>0))
ORDER BY qu_tbFinalIncident_NoLevel4.AuthorDept,
qu_tbFinalIncident_NoLevel4.LoggedDate;

Thanks,
Stephanie
 
S

Stephanie

How did you do that?!!!! Days of heartache trying to figure it out and you
put it all on one line like its nothing. I'll practice. But I do a couple of
other complicated queries I'd like to streamline. Hope you'll look for future
query summing posts...

Thanks for your help- very appreciated!
Cheers,
Stephanie


KARL DEWEY said:
Try it this way --

SELECT Sum(Abs([GainLoss])) AS Abs, ([LoggedMonth]+2)\3 AS QRT,
qu_tbFinalIncident_NoLevel4.LoggedYear
FROM qu_tbFinalIncident_NoLevel4
GROUP BY ([LoggedMonth]+2)\3, qu_tbFinalIncident_NoLevel4.LoggedYear
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorDept)=[Forms]![frQuick]![Combo82]) AND
((Sum(Abs([GainLoss])))>0));

Stephanie said:
Karl, Thanks for taking a look. Removing [LoggedDate] did not do the trick.
The query is behaving the same, where I have

Abs QRT LoggedYear
20000 1 2007
6130 1 2007

I really struggle with the "summary" queries, where I want things combined
Try this --
SELECT qu_tbFinalIncident_NoLevel4.AuthorDept, Sum(Abs([GainLoss])) AS Abs,
([LoggedMonth]+2)\3 AS QRT, qu_tbFinalIncident_NoLevel4.LoggedYear
FROM qu_tbFinalIncident_NoLevel4
GROUP BY qu_tbFinalIncident_NoLevel4.AuthorDept, ([LoggedMonth]+2)\3,
qu_tbFinalIncident_NoLevel4.LoggedYear
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorDept)=[Forms]![frQuick]![Combo82]) AND
((Sum(Abs([GainLoss])))>0))
ORDER BY qu_tbFinalIncident_NoLevel4.AuthorDept;

Removed qu_tbFinalIncident_NoLevel4.LoggedDate from query.

:

Hi. I am struggling with a query. I want to see absolute value of loss by
quarter.
In my query, each Abs value loss lists the quarter and year it occurred.

What I'm getting is:
Abs QRT LoggedYear
20000 1 2007
6130 1 2007
2000 2 2007
6345 3 2007
1234 3 2007

What I want is

Abs QRT LoggedYear
26130 1 2007
2000 2 2007
7579 3 2007

so that I can graph by the Abs by quarter loss (I hope!)

SELECT qu_tbFinalIncident_NoLevel4.AuthorDept, Sum(Abs([GainLoss])) AS Abs,
([LoggedMonth]+2)\3 AS QRT, qu_tbFinalIncident_NoLevel4.LoggedYear
FROM qu_tbFinalIncident_NoLevel4
GROUP BY qu_tbFinalIncident_NoLevel4.AuthorDept, ([LoggedMonth]+2)\3,
qu_tbFinalIncident_NoLevel4.LoggedYear, qu_tbFinalIncident_NoLevel4.LoggedDate
HAVING
(((qu_tbFinalIncident_NoLevel4.AuthorDept)=[Forms]![frQuick]![Combo82]) AND
((Sum(Abs([GainLoss])))>0))
ORDER BY qu_tbFinalIncident_NoLevel4.AuthorDept,
qu_tbFinalIncident_NoLevel4.LoggedDate;

Thanks,
Stephanie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union query - sort of 4
Query math 1
Crosstab, Quarter Sum isn't working 6
Query with count, but what am I counting?! 4
Query on 2 other queries 1
Query type 2
Problem with query 2
$ Complex Access Query 4

Top