Count

  • Thread starter Thread starter Stephanie
  • Start date Start date
S

Stephanie

Hello. I have a query that is almost working. I am trying to return the # and
$ of incidents in business units.
I've FINALLY gotten the $ to sum correctly on one line. However, when I try
to perform a count of IncidentID, I don't get the correct # (54 instead of 11
for one business unit). I can't figure out how to get the correct count.

Also, I have 5 business units but only 3 have incidents. I would like to
return info for all 5 business units (so that I can have their name returned
from the query so I can graph them), even if there is nothing to return. I'd
appreciate your suggestions.

Thanks,
Stephanie
Cheers,
Stephanie
 
Knew I forgot something ;-)

SELECT tbDepartments.[Business Unit], Sum(Abs([GainLoss])) AS [Absolute
Direct Gain/Loss], Count(tbIncidents_Final.incidentid) AS CountOfincidentid
FROM tbIncidents_Final INNER JOIN tbDepartments ON
tbIncidents_Final.[Department Name] = tbDepartments.[Department Name]
WHERE (((tbIncidents_Final.Level) In (1,2)) AND
((tbIncidents_Final.Status)="Completed") AND ((tbIncidents_Final.Operations)
Is Not Null) AND ((tbIncidents_Final.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
GROUP BY tbDepartments.[Business Unit]
HAVING (((Sum(Abs([GainLoss])))>0));
 
Try this FROM statement --
FROM tbDepartments LEFT JOIN tbIncidents_Final ON tbDepartments.[Department
Name] = tbIncidents_Final.[Department Name]


Stephanie said:
Knew I forgot something ;-)

SELECT tbDepartments.[Business Unit], Sum(Abs([GainLoss])) AS [Absolute
Direct Gain/Loss], Count(tbIncidents_Final.incidentid) AS CountOfincidentid
FROM tbIncidents_Final INNER JOIN tbDepartments ON
tbIncidents_Final.[Department Name] = tbDepartments.[Department Name]
WHERE (((tbIncidents_Final.Level) In (1,2)) AND
((tbIncidents_Final.Status)="Completed") AND ((tbIncidents_Final.Operations)
Is Not Null) AND ((tbIncidents_Final.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
GROUP BY tbDepartments.[Business Unit]
HAVING (((Sum(Abs([GainLoss])))>0));


Stephanie said:
Hello. I have a query that is almost working. I am trying to return the # and
$ of incidents in business units.
I've FINALLY gotten the $ to sum correctly on one line. However, when I try
to perform a count of IncidentID, I don't get the correct # (54 instead of 11
for one business unit). I can't figure out how to get the correct count.

Also, I have 5 business units but only 3 have incidents. I would like to
return info for all 5 business units (so that I can have their name returned
from the query so I can graph them), even if there is nothing to return. I'd
appreciate your suggestions.

Thanks,
Stephanie
Cheers,
Stephanie
Click to expand...
Click to expand...
 
Thanks Karl. That didn't quite do it- it didn't change the count at all. If I
show IncidentID, I see all of the incidents involved but then I lose the
summed $. Arg!

KARL DEWEY said:
Try this FROM statement --
FROM tbDepartments LEFT JOIN tbIncidents_Final ON tbDepartments.[Department
Name] = tbIncidents_Final.[Department Name]


Stephanie said:
Knew I forgot something ;-)

SELECT tbDepartments.[Business Unit], Sum(Abs([GainLoss])) AS [Absolute
Direct Gain/Loss], Count(tbIncidents_Final.incidentid) AS CountOfincidentid
FROM tbIncidents_Final INNER JOIN tbDepartments ON
tbIncidents_Final.[Department Name] = tbDepartments.[Department Name]
WHERE (((tbIncidents_Final.Level) In (1,2)) AND
((tbIncidents_Final.Status)="Completed") AND ((tbIncidents_Final.Operations)
Is Not Null) AND ((tbIncidents_Final.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
GROUP BY tbDepartments.[Business Unit]
HAVING (((Sum(Abs([GainLoss])))>0));


Stephanie said:
Hello. I have a query that is almost working. I am trying to return the # and
$ of incidents in business units.
I've FINALLY gotten the $ to sum correctly on one line. However, when I try
to perform a count of IncidentID, I don't get the correct # (54 instead of 11
for one business unit). I can't figure out how to get the correct count.

Also, I have 5 business units but only 3 have incidents. I would like to
return info for all 5 business units (so that I can have their name returned
from the query so I can graph them), even if there is nothing to return. I'd
appreciate your suggestions.

Thanks,
Stephanie
Cheers,
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
I don't get the correct # (54 instead of 11 for one business unit).
Which is correct 54 or 11?
If it is supposed to be 54 then try dropping some criteria.
First the ---
HAVING (((Sum(Abs([GainLoss])))>0))
then the try LEVEL, STATUS, and Operations separately until you get the
number.


Stephanie said:
Thanks Karl. That didn't quite do it- it didn't change the count at all. If I
show IncidentID, I see all of the incidents involved but then I lose the
summed $. Arg!

KARL DEWEY said:
Try this FROM statement --
FROM tbDepartments LEFT JOIN tbIncidents_Final ON tbDepartments.[Department
Name] = tbIncidents_Final.[Department Name]


Stephanie said:
Knew I forgot something ;-)

SELECT tbDepartments.[Business Unit], Sum(Abs([GainLoss])) AS [Absolute
Direct Gain/Loss], Count(tbIncidents_Final.incidentid) AS CountOfincidentid
FROM tbIncidents_Final INNER JOIN tbDepartments ON
tbIncidents_Final.[Department Name] = tbDepartments.[Department Name]
WHERE (((tbIncidents_Final.Level) In (1,2)) AND
((tbIncidents_Final.Status)="Completed") AND ((tbIncidents_Final.Operations)
Is Not Null) AND ((tbIncidents_Final.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
GROUP BY tbDepartments.[Business Unit]
HAVING (((Sum(Abs([GainLoss])))>0));


:

Hello. I have a query that is almost working. I am trying to return the # and
$ of incidents in business units.
I've FINALLY gotten the $ to sum correctly on one line. However, when I try
to perform a count of IncidentID, I don't get the correct # (54 instead of 11
for one business unit). I can't figure out how to get the correct count.

Also, I have 5 business units but only 3 have incidents. I would like to
return info for all 5 business units (so that I can have their name returned
from the query so I can graph them), even if there is nothing to return. I'd
appreciate your suggestions.

Thanks,
Stephanie
Cheers,
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
Thanks Karl. The count is supposed to be 11.

KARL DEWEY said:
Which is correct 54 or 11?
If it is supposed to be 54 then try dropping some criteria.
First the ---
HAVING (((Sum(Abs([GainLoss])))>0))
then the try LEVEL, STATUS, and Operations separately until you get the
number.


Stephanie said:
Thanks Karl. That didn't quite do it- it didn't change the count at all. If I
show IncidentID, I see all of the incidents involved but then I lose the
summed $. Arg!

KARL DEWEY said:
Try this FROM statement --
FROM tbDepartments LEFT JOIN tbIncidents_Final ON tbDepartments.[Department
Name] = tbIncidents_Final.[Department Name]


:

Knew I forgot something ;-)

SELECT tbDepartments.[Business Unit], Sum(Abs([GainLoss])) AS [Absolute
Direct Gain/Loss], Count(tbIncidents_Final.incidentid) AS CountOfincidentid
FROM tbIncidents_Final INNER JOIN tbDepartments ON
tbIncidents_Final.[Department Name] = tbDepartments.[Department Name]
WHERE (((tbIncidents_Final.Level) In (1,2)) AND
((tbIncidents_Final.Status)="Completed") AND ((tbIncidents_Final.Operations)
Is Not Null) AND ((tbIncidents_Final.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
GROUP BY tbDepartments.[Business Unit]
HAVING (((Sum(Abs([GainLoss])))>0));


:

Hello. I have a query that is almost working. I am trying to return the # and
$ of incidents in business units.
I've FINALLY gotten the $ to sum correctly on one line. However, when I try
to perform a count of IncidentID, I don't get the correct # (54 instead of 11
for one business unit). I can't figure out how to get the correct count.

Also, I have 5 business units but only 3 have incidents. I would like to
return info for all 5 business units (so that I can have their name returned
from the query so I can graph them), even if there is nothing to return. I'd
appreciate your suggestions.

Thanks,
Stephanie
Cheers,
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
Use this to see if you have multiple entries with the same incidentid and
status as Completed --
SELECT tbDepartments.[Business Unit], tbIncidents_Final.incidentid
FROM tbDepartments LEFT JOIN tbIncidents_Final ON tbDepartments.[Department
Name] = tbIncidents_Final.[Department Name]
WHERE (((tbIncidents_Final.Level) In (1,2)) AND
((tbIncidents_Final.Status)="Completed") AND ((tbIncidents_Final.Operations)
Is Not Null) AND((tbIncidents_Final.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
ORDER BY tbDepartments.[Business Unit], tbIncidents_Final.incidentid
HAVING ((((Abs([GainLoss])))>0));


Stephanie said:
Knew I forgot something ;-)

SELECT tbDepartments.[Business Unit], Sum(Abs([GainLoss])) AS [Absolute
Direct Gain/Loss], Count(tbIncidents_Final.incidentid) AS CountOfincidentid
FROM tbIncidents_Final INNER JOIN tbDepartments ON
tbIncidents_Final.[Department Name] = tbDepartments.[Department Name]
WHERE (((tbIncidents_Final.Level) In (1,2)) AND
((tbIncidents_Final.Status)="Completed") AND ((tbIncidents_Final.Operations)
Is Not Null) AND ((tbIncidents_Final.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
GROUP BY tbDepartments.[Business Unit]
HAVING (((Sum(Abs([GainLoss])))>0));


Stephanie said:
Hello. I have a query that is almost working. I am trying to return the # and
$ of incidents in business units.
I've FINALLY gotten the $ to sum correctly on one line. However, when I try
to perform a count of IncidentID, I don't get the correct # (54 instead of 11
for one business unit). I can't figure out how to get the correct count.

Also, I have 5 business units but only 3 have incidents. I would like to
return info for all 5 business units (so that I can have their name returned
from the query so I can graph them), even if there is nothing to return. I'd
appreciate your suggestions.

Thanks,
Stephanie
Cheers,
Stephanie
Click to expand...
Click to expand...
 
Use this to see if you have multiple entries with the same incidentid and
status as Completed --
SELECT tbDepartments.[Business Unit], tbIncidents_Final.incidentid
FROM tbDepartments LEFT JOIN tbIncidents_Final ON tbDepartments.[Department
Name] = tbIncidents_Final.[Department Name]
WHERE (((tbIncidents_Final.Level) In (1,2)) AND
((tbIncidents_Final.Status)="Completed") AND ((tbIncidents_Final.Operations)
Is Not Null) AND((tbIncidents_Final.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
ORDER BY tbDepartments.[Business Unit], tbIncidents_Final.incidentid
HAVING ((((Abs([GainLoss])))>0));


Stephanie said:
Thanks Karl. The count is supposed to be 11.

KARL DEWEY said:
I don't get the correct # (54 instead of 11 for one business unit).
Click to expand...
Which is correct 54 or 11?
If it is supposed to be 54 then try dropping some criteria.
First the ---
HAVING (((Sum(Abs([GainLoss])))>0))
then the try LEVEL, STATUS, and Operations separately until you get the
number.


Stephanie said:
Thanks Karl. That didn't quite do it- it didn't change the count at all. If I
show IncidentID, I see all of the incidents involved but then I lose the
summed $. Arg!

:

Try this FROM statement --
FROM tbDepartments LEFT JOIN tbIncidents_Final ON tbDepartments.[Department
Name] = tbIncidents_Final.[Department Name]


:

Knew I forgot something ;-)

SELECT tbDepartments.[Business Unit], Sum(Abs([GainLoss])) AS [Absolute
Direct Gain/Loss], Count(tbIncidents_Final.incidentid) AS CountOfincidentid
FROM tbIncidents_Final INNER JOIN tbDepartments ON
tbIncidents_Final.[Department Name] = tbDepartments.[Department Name]
WHERE (((tbIncidents_Final.Level) In (1,2)) AND
((tbIncidents_Final.Status)="Completed") AND ((tbIncidents_Final.Operations)
Is Not Null) AND ((tbIncidents_Final.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])))
GROUP BY tbDepartments.[Business Unit]
HAVING (((Sum(Abs([GainLoss])))>0));


:

Hello. I have a query that is almost working. I am trying to return the # and
$ of incidents in business units.
I've FINALLY gotten the $ to sum correctly on one line. However, when I try
to perform a count of IncidentID, I don't get the correct # (54 instead of 11
for one business unit). I can't figure out how to get the correct count.

Also, I have 5 business units but only 3 have incidents. I would like to
return info for all 5 business units (so that I can have their name returned
from the query so I can graph them), even if there is nothing to return. I'd
appreciate your suggestions.

Thanks,
Stephanie
Cheers,
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top