Using Count() in SQL

[rtipton12]

I am having trouble finding out how to do something in Access SQL.

I have a query in which (among other things), I would like to count for a specific field is not 0.


Example:

Facility # Error type 1
Mars 2
Mars 3
Mars 0
Mars 2

In the query I will be grouping by facility and would like to get a count of each populated <# Error type 1>.

I would like to
count for # error type 1 is not 0 as err_1_count (should be 3)


Sorry if this does not make sense. I am new to Access and am trying to maintain an application that has been transitioned to me.

Thanks in advance.

Rhonda

 

[BNPMike]

What is your example an example of? It has repeated facilties ,repeated error values and a zero value which it looks as though you don't want.

Are you looking for this:

Facility # Error type 1 Count
Mars 1 7
Mars 3 1
Mars 8 2
Neptune 2 4
etc
etc

 

[lespaul]

try:

Select Facility, count(iif [# Error type 1] <> 0, 1, 0) as FacilityCount From Tablename
Group By Facility

Leslie

 

[rtipton12]

Sorry, I did have the error type field wrong (it is a logical or Yes/No field)


Facility # Error type 1
Mars Y
Mars Y
Mars Y
Mars N
Neptune Y
Neptune N
Jupiter Y
Jupiter Y
Jupiter N
Jupiter Y
Jupiter Y



Query result I am looking for:
Facility Error Count
Mars 3
Neptune 1
Jupiter 4

I only want the 'Y' records in the count.

Thanks.

Rhonda

 

[Golom]

Try

Select Facility, Count(*) As [ErrorCount]
From tbl
Where [Error Type 1] = TRUE
Group By Facility

 

[rtipton12]

The only problem is that the example that I used is part of a bigger query in which I cannot put a limit in the where clause.

Thanks.
Rhonda

 

[Golom]

OK. Then try

Select Facility, Sum(IIf([Error Type 1]=TRUE,1,0)) As [ErrorCount]
From tbl
Group By Facility

 

[JonFer]

A simple way to do this (because a True value is stored as -1) is to use one of these:

-Sum([Error Type 1]) as ErrorCount
Abs(Sum([Error Type 1])) as ErrorCount

 

[rtipton12]

Thank you all for your replies. I will try your recommendations.

 

[rtipton12]

Golom,

I was able to incorporate your idea into my query successfully. Thank you very much for your help.

Thanks to all of you for your replies.

Rt