Query summing on one line

  • Thread starter Thread starter Stephanie
  • Start date Start date
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
 
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.
 
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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top