Multiple Counts 1

[ba4crm]

I have a table that has the following structure
TP RecordId Status
A 1 X
A 2 X
A 3 Y
A 4

Record 4 does not have a status. I would like to count how many records belong to TP A and also how many records for TP A have a status <> Null (or how many records have a Status = Null)

Output desired:
TP Total Count Status Null
A 4 1

Thanks

 

[dhookom]

This should satisfy your specifications:

SELECT TP, Count(RecordID) As TotalCount, Sum(Abs(IsNull(Status))) as StatusNull
FROM [a table]
GROUP BY TP;

Duane
Hook'D on Access
MS Access MVP

 

[ba4crm]

This worked - thanks!

 

[ba4crm]

I have a slightly different variation on this. For the TP, I cannot consider that TP "closed" unless each record has a status (<> Null). This works so far.
I have to run a report that lists out all the TPs that have a status <> Null. However, if all the records have a status of "Cancel", I do not want that TP to show up in this list.

 

[dhookom]

Provide some sample records and desired output.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

lists out all the TPs that have a status <> Null. However, if all the records have a status of "Cancel", I do not want that

SELECT TP, Count(*) As TotalCount
FROM [a table]
GROUP BY TP
HAVING Count(RecordID)=Count(*)
AND Sum(IIf(Status & ''='Cancel',1,0))<Count(*)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886