Count

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
 
S

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));
 
K

KARL DEWEY

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...
 
S

Stephanie

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...
 
K

KARL DEWEY

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...
 
S

Stephanie

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...
 
K

KARL DEWEY

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...
 
K

KARL DEWEY

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...
 

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

Macro Loop 0
Creating a Chart on form from selected data in query. 0
Union query - sort of 4
select count query 1
Making a Queary Updatable 1
counting totals, but not using "like" 6
Rolling Average Calculation 4
'count' query question 2

Top